ASP.Net 2.0 Application Connection To SQL Server 2005 - Setup Questions
Jun 5, 2007
All:
I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login.
Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login account so that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login.
Here is my situation: I am creating a database driven ASP.Net web application that will be used over the internet. My ASP.Net application connects to my SQL server 2005 database/server by using a SQL server login. I am using the DPAPI API to encrypt my connection stings with a hidden entropy value for extra security. I am using the SQL login for obvious reasons, as my users will not have a windows login.
What I am trying to do: I want to limit this SQL login account to be able to just run/execute stored procedures and NOT access the tables or views directly. In my ASP.Net application I am using the MS applications data block, and I am using stored procedures for every single database access action. There is no inline SQL being executed from my web application.
What I have tried so far:
I created a new schema and made the above SQL login account owner of this schema. I then granted "Execute" permissions to the SQL login and DENY permissions to all other permissions.
I created an database role with "Execute" only permissions and DENY permissions to all other permissions.
What Happened: In BOTH of the above scenarios I tested a direct SQL statement against one of my tables, from my ASP.Net application and I was able retrieve data back, NOT GOOD, exactly what I am trying to STOP.
If someone could give me (Step-by-Step) guide on how to setup the situation I am looking for, I would be very grateful!
Hi all, i have standard edition of sql server, on a server hat doesnt have sql server standard would i be able to connect to it using my connection string. Or does the server has to have standard edition too. Is this same for express edition, and if possible to do this whats the difference between express connection string from standard edition thanks
I have a SQL Server 2005 database (called BDHSE) in a PC which i call PC1. I have a second PC (PC2) and both are within a network (a WLAN).
What i want is to have access to BDHSE from an application in VB6 (APP1) running in PC2. All the INSERT, DELETE, UPDATE records process is done through APP1.
APP1 ia currently running in PC1 and is to be installed on PC2.
I have these questions:
1. What do i need to install in PC2 since all the INSERT, DELETE, and UPDATE is done using APP1? I guess i only have to install the Microsoft SQL Native Client (with all the prerequisites of course) but i am not sure.
2. In the APP1 made in VB6, do i have to change the connectionstring since i am accesing the database which physically is at PC1 and the APP1 will be used in PC2?
3. Any advice you can give me on doing this will be well received.
I am setting up a web server and I'm having some issues with database connections. I am running Windows Server 2k3 and SQL Server 2005. The error I get is when I attempt to connect to the database, it's the following: Cannot open database "dbReseacher" requested by the login. The login failed. Login failed for user 'userRes'. The following is my connection string: <add name="RESEARCHER" connectionString="Data Source={Sql Server}; Server=THRALL2; Database=dbReseacher; Uid=userRes; Pwd=password;"/> In SQL Server Management Studio, the user userRes is listed under Security -> Logins, but it is not listed under Database -> dbResearcher -> Security -> Users. Originally it was listed under both and this didn't solve the problem. Please let me know if you any ideas or questions that I can answer. Thanks a lot. -Richard
I'm new to using SQL server and have a few setup questions. I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.
I've developed an application that connects to a SQL Server 2005 Express database. I created a DSN to connect to the database through ODBC. Currently, I am testing locally and everything works fine.
I would now like to install my application on another workstation and connect remotely to the database located on my development machine.
The client workstation does not have SQL Server 2005 Express installed on it because I would just like my application to connect remotely by creating the DSN and using ODBC. What I'm missing here are the database drivers. The "SQL Natice Client" is not available on this client workstation. How can I deploy the necessary drivers with my installation file so that I may create the required DSN name using the SQL Native Client driver?
I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login.
Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login account so that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login.
Dear members,I'am thinking about the "best" way to connect to my sql-server viaADO.Net (if there is a best way).My application uses the sql-server very intensively. At the momentevery database-operation opens a connection, executes and then closesthe connection.Another way would be to use only a single connection to the database,which is opened when the application starts.What do you think is the better way to get a high performing sqlserver: using one single application connection vs. using oneconnection for every operation?Cheers, Sebastian
If i wanted to set up my SQL Server DB to run in an Application Server environment (i.e. clients connecting to a remote server) would i be required to give every user a WINDOWS SERVER logon as well as a SQL Server logon or would i only need to setup just a SQL Server logon for them? (obviously using SQL Server Security opposed to NT Integrated Security)
We have a small Ethernet LAN running SBS 2003 R2. I have installed SQL Server 2005 that came with that release on the server. I did not specify a named instance.
1) What is the name of the default instance?
2) When I go to a client computer I cannot see the SQL server instance.
For example on my PC I have VS2005. If I go to Server Explorer, all I see is the local copy of SQL Server 2005 Express I have installed on my PC. I see nothing for the server. Does the server need configuring within SQL Server to allow this. Do I need to change permisions on the folders where the SQL Server 2005 files are stored.
In short, what basic steps do I need to follow to get acces to SQL Server on my server from a client.
3) If I want to create a new named instance, do I have to do Run the whole setup again? There appears to be no managemnt tool for instances of SQL server.
I got 2 questions to ask: 1. I choose Windows and SQL authentication during setup. Will this have any impact on my connection string in Dot net? 2. If I want to host my db on my computer, what other protocols do I need to enable? Currently I got shared memory on.
I have a windows application that connects to a SQLExpress database hosted on a shared server. The client machines will run an interface software and interact with the info within SQL The SQL database isn't huge (50 megs) and all of the info is text. The interface application isn't too complex either, it was designed using VB.net05. I have a few setup questions:
1 - Is it best to use Windows or SQL authentiaction? Currently I am using Windows authentication and I have a user group setup on the DNS that is setup as a user for the SQL database. That has worked so far, but I've only had a few users logged in at one time so far. My plan was to add all DNS accounts that will use the software to the DNS user group, thus giving them access to the SQL database.
2 - I know this gets asked a TON, but I am interested in knowing how many users I should be able to support using the current setup. I have the one user acount setup for the DNS user group. The SQL table is not huge and it is all reading and writing text. The server is running Windows Server 03 and is a couple years old (not sure of exact specs).
Thanks for any help, I am still learning my way around SQL and it's great to have such a vast amount of support for the product.
Hey anyone, I got a problem to check a connection whether it is established or not from my C# Application to SQL Server. I am developing my application which can run in 2 modes: 1. Online: There must be a connection to SQL Server to get new data updates. 2. Offline: I dont need a connection to SQL Server. I set a timer to tick every 5 seconds and it will check the connection with SQL Server and my problem here: (this is my code)1 public static bool checkConnection() 2 { 3 SqlConnection conn = new SqlConnection("mydatasource"); 4 try 5 { 6 conn.Open(); 7 return true; 8 } 9 catch (Exception ex) { return false; } 10 }
Yes, it works but my application seems to wait about >3 seconds to get the return "False"; I tried to add "Connect TimeOut=1" in my Connection String, but the problem is still there. I dont know whether there is a faster way to check the connection to SQL server which is established or not. Thank you very much, Joesy
I have application that is connecting to SQL Server and runs bunch of stored procedures, but I don't think that am able to connect to my database server. When it hits the line to execute the stored procedure it comes up with syntax error saying the syntax error is at the stored procedure. I know how to set up the connection string with web app from the web config, but never done it on an app. Here is the connection string pointing to my server which I pass to my SqlConnection object: "Data Source=ServerXXXX;Initial Catalog=DB;Integrated Security=True; User ID=xxxx; Password=xxxx;" And that is the code that is using the above connection string: Public Sub MigrateNFSData(ByVal calcTbl As DataTable, ByVal strDBConnection As String) Dim sqlServerConn As New SqlConnection(strDBConnection) 'Define stored procedures Dim command As New SqlCommand Dim getAccID As New SqlCommand("GetAccountID", sqlServerConn) Dim getActionID As New SqlCommand("GetActionID", sqlServerConn) Dim getExchangeID As New SqlCommand("GetExchangeID", sqlServerConn) 'Dim getParrentAccID As New SqlCommand("GetParentAccID", sqlServerConn) Dim getStatusID As New SqlCommand("GetStatusID", sqlServerConn) Dim getTraderID As New SqlCommand("GetTraderID", sqlServerConn) Dim getGroupID As New SqlCommand("GetGroupID", sqlServerConn) 'Define insert records stored procedures Dim insertAcc As New SqlCommand("InsertAccount", sqlServerConn) Dim insertAction As New SqlCommand("InsertAction", sqlServerConn) Dim insertExchange As New SqlCommand("InsertExchange", sqlServerConn) Dim insertGroup As New SqlCommand("InsertGroup", sqlServerConn) Dim insertStatus As New SqlCommand("InsertStatus", sqlServerConn) Dim insertTrader As New SqlCommand("InsertTrader", sqlServerConn) Try sqlServerConn.Open() Catch ex As Exception 'Exception message goes here End Try 'Set parameters to helper Get Stored Procedures to retreive Id's getAccID.Parameters.Add("@AccName", SqlDbType.NVarChar) getAccID.CommandType = CommandType.StoredProcedure getActionID.Parameters.Add("@ActionName", SqlDbType.NVarChar) getActionID.CommandType = CommandType.StoredProcedure getExchangeID.Parameters.Add("@ExchName", SqlDbType.NVarChar) getExchangeID.CommandType = CommandType.StoredProcedure 'getParrentAccID.Parameters.Add("@ParentName", SqlDbType.NVarChar) 'getParrentAccID.CommandType = CommandType.StoredProcedure getStatusID.Parameters.Add("@StatusName", SqlDbType.NVarChar) getStatusID.CommandType = CommandType.StoredProcedure getTraderID.Parameters.Add("@TraderName", SqlDbType.NVarChar) getTraderID.CommandType = CommandType.StoredProcedure getGroupID.Parameters.Add("@GroupName", SqlDbType.NVarChar) getGroupID.CommandType = CommandType.StoredProcedure command = New SqlCommand("InsertTradeTransaction", sqlServerConn) command.CommandType = CommandType.StoredProcedure 'Set Parameters for Insert stored procedures insertAcc.Parameters.Add("@Account", SqlDbType.Text) insertAction.Parameters.Add("@ActionName", SqlDbType.Text) insertExchange.Parameters.Add("@Exchange", SqlDbType.Text) insertGroup.Parameters.Add("@Group", SqlDbType.Text) insertGroup.Parameters.Add("@ACCID", SqlDbType.Int) insertStatus.Parameters.Add("@StatusName", SqlDbType.Text) insertTrader.Parameters.Add("@Group", SqlDbType.UniqueIdentifier) insertTrader.Parameters.Add("@IP", SqlDbType.Text) insertTrader.Parameters.Add("@TraderName", SqlDbType.Text) 'Adding stored Get Stored Procedure's parameters----------------------- command.Parameters.Add("@OrderNum", SqlDbType.Text) command.Parameters.Add("@ACC_ID", SqlDbType.Int) command.Parameters.Add("@Group_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Trader_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Exch_ID", SqlDbType.Int) command.Parameters.Add("@Date", SqlDbType.DateTime) command.Parameters.Add("@Time", SqlDbType.DateTime) command.Parameters.Add("@ActionID", SqlDbType.Int) command.Parameters.Add("@StatusID", SqlDbType.Int) command.Parameters.Add("@TimeSent", SqlDbType.DateTime) command.Parameters.Add("@Qty", SqlDbType.Int) command.Parameters.Add("@Product", SqlDbType.Text) command.Parameters.Add("@MMYYY", SqlDbType.Text) command.Parameters.Add("@ExchOrderID", SqlDbType.Text) command.Parameters.Add("@TimeTicks", SqlDbType.Int) command.Parameters.Add("@W2G", SqlDbType.Int) command.Parameters.Add("@W2Exch", SqlDbType.Int) command.Parameters.Add("@G2ExchDerived", SqlDbType.Int) command.Parameters.Add("@ExchDate", SqlDbType.DateTime) 'command.Parameters.Add("@ParentID", SqlDbType.Int) 'Paremeters Defenition-------------------------------------- 'Write table with computed NFS data to sql server DB For Each dr As DataRow In calcTbl.Rows command.Parameters("@OrderNum").Value = dr.Item("OrderNo").ToString() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() If getAccID.ExecuteScalar() = 0 Then insertAcc.Parameters("@Account").Value = dr.Item("Acct").ToString() insertAcc.ExecuteNonQuery() //THIS IS WHERE IT FAILS and pops up with the message that there is syntax error at this procedure getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() command.Parameters("@AccName").Value = getAccID.ExecuteScalar() Else command.Parameters("@ACC_ID").Value = getAccID.ExecuteScalar() End If getGroupID.Parameters("@GroupName").Value = dr.Item("GroupID").ToString() Try command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getTraderID.Parameters("@TraderName").Value = dr.Item("TrdID").ToString() Try command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getExchangeID.Parameters("@ExchName").Value = dr.Item("Exch").ToString() Try command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getActionID.Parameters("@ActionName").Value = dr.Item("Action").ToString() Try command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getStatusID.Parameters("@StatusName").Value = dr.Item("Status").ToString() Try command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try 'getParrentAccID.Parameters("@ParentName").Value = "" 'Try 'command.Parameters("@ParentID").Value = getParrentAccID.ExecuteScalar() ' Catch ex As Exception 'End Try command.Parameters("@Date").Value = DateTime.Parse(dr.Item("Exch Date").ToString()) command.Parameters("@Time").Value = DateTime.Parse(dr.Item("Time").ToString()) command.Parameters("@TimeSent").Value = DateTime.Parse(dr.Item("Time Sent").ToString()) If (dr.Item("Qty").Equals(System.DBNull.Value)) Then command.Parameters("@Qty").Value = System.DBNull.Value Else command.Parameters("@Qty").Value = Int32.Parse(dr.Item("Qty").ToString()) End If command.Parameters("@Product").Value = dr.Item("Product").ToString() command.Parameters("@MMYYY").Value = dr.Item("MMYYYY").ToString() command.Parameters("@ExchOrderID").Value = dr.Item("ExchOrderID").ToString() If (dr.Item("TimeTicks").Equals(System.DBNull.Value)) Then command.Parameters("@TimeTicks").Value = System.DBNull.Value Else command.Parameters("@TimeTicks").Value = Int32.Parse(dr.Item("TimeTicks").ToString()) End If command.Parameters("@ExchDate").Value = Date.Parse(dr.Item("Exch Date").ToString()) command.Parameters("@W2G").Value = 2 command.Parameters("@W2Exch").Value = 2 command.Parameters("@G2ExchDerived").Value = 2 command.ExecuteNonQuery() sqlServerConn.Close() Next End Sub Thank you guys as always being helpful.
I have VB6 application on several desktops connected to SERVER A (SQL Server 2005) in TESTING. I know I can change some registry values in VB6 application to connect it to SERVER B(SQL Server 20050 in PRODUCTION. but it is work on each and every desktop.
Is there any way to do something on SQL Server 2005 Engine end (like Alias) so I don't have to make changes for SERVER Redirection in each and every Desktop machine(client), and Just I make change on SERVER Side. any help or idea on that? Thanks,
Can I have an sql server database (mdf file) on a server and allow windows applications at different site manipulate the database.
As far as I am concerned the only thing that needs to be configued is the connection string..is this correct.
[i would rather use windows applications rather than asp pages]
If I can, the best way to manipulate the database is to use stored procedures. How would I do the following to create and execute the following proposed procedure:
Insert data into a table Update another table as a result
Execute the store procedure on a form to execute these two statements
I'moving my asp application to a new hosting server.
So when i tried the setup locally with the live DB & application in my test machine... The DB access is denied when application tries to hit the DB.
DB is in seperate machine with SQL Server 2000 & application(ASP) is in Windows server 2003..... Kindly help me with your suggestions....on what went wrong?
Hi,I have a problem, when I want to access to my SQL Server Expressdatabase from my ASP.NET application.My workstation ID is KITOLAP-HPMy username is user01Now I built the following connection String (VB.NET):Dim workstation As String =System.Environment.GetEnvironmentVariable("computername")connectionString = "workstation id=""" & workstation & """;packetsize=4096;user id=user01;integrated security=SSPI;data source=""" &workstation & "SQLExpress"";persist security info=True;initialcatalog=webshop"connection = New SqlConnection(connectionString)Although I put as user id "user01" the application makes an error whenI try to open the connection, saying that the login with user "aspnet"failed.How can I change it so that as login-user "user01" is taken and not"aspnet" or otherwise, how can I create a new user in the SQL Serverdatabase with which I could access the DB??thanks,kito
We are currently running a corporate client with Windows 2000 and .Net 1.1. We are running a number of SQL Server 2000 applications and are now thinking of upgrading to SQL Server 2005 as part of a data consolidation exercise. I am concerned on a number of points:
Can I connect to SQL Server 2005 using old ADO connectors? We have about 40 Excel VBA solutions, and we dont want to upgrade to SQL Server 2005 if we will be unable to connect to the data source. We cannot upgrade any new versions of MDAC or upgrade the .NET framework so this is a concern.
Do we need .NET 2.0 or Visual Studio 2005 to connect and work with SQL Server 2005? If so, this will be a problem as we cannot upgrade any client beyond .NET 1.1, and only have VS 2003 as a scripted application we can install for any development.
Has anyone have any experience of the KPI capabilities of SQL 2005? We are bordering on committing to a Business Objects BI platform, and having worked with BO Dashboard Manager and Performance Manager for 4 months (it was horrible), I am not relishing the prospect and would like to propose SQL 2005 as an alternative.
Hello, I would like to know if anyone here has any information on how to change the default version of SQL Server 2005 Express which ships with Visual Studio 2005 to the new SQL 2005 Express SERVICE PACK 1?
I see a file under disc:vswcuSSESQLEXPR.exe I am wondering if changing that file with the new one from Microsoft Download Center will do it...
There's another file under that folder named sqlexpr32.exe I am wondering what it's for?
Hello, I would like to know if anyone here has any information on how to change the default version of SQL Server 2005 Express which ships with Visual Studio 2005 to the new SQL 2005 Express SERVICE PACK 1?
I see a file under disc:vswcuSSESQLEXPR.exe I am wondering if changing that file with the new one from Microsoft Download Center will do it...
There's another file under that folder named sqlexpr32.exe I am wondering what it's for?
i'm trying to install MS SQL 2005 Enterprise Evaluation Edition, on my Win XP Pro SP2 (Intel Centrino 1.73Ghz, 1GB RAM)... i have downloaded the self-extractable file (SQLEVALX64.EXE) from MS website... After extracting this file to a folder and clicking on "setup.exe" - resulting in "setup.exe is not a valid Win32 application" error message...
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
Hi pardon my ignorance but I wonder if someone could answer a few questions for me.
I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.
A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.
So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.
How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.
I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.
I suspect the problem is the way my program is going about the checking.
Should I use an SQL insert statement like??
If exists(SELECT username from users where username=@username) BEGIN RAISEERROR("Username already exists") END ELSE BEGIN INSERT etc
If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?
Also some more general questions.
1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.
2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()
As a rule I close all my connections as soon as I'm done with them.
A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AIcollation, but there is no such collation returned fromfn_helpcollations(). Also, if I try to use this collation ina CREATE DATABASE stmt, SQLS yells about it.I see that there is a Latin1_General_CS_AI. What effects are therein using this collation? The SQL_* collations are SQL collations,while non-SQL_* collations are Windows collations, yes? SQLS runsonly on Windows, so am I safe in using Latin1_General_CS_AI? Whatdoes the CP1 in the SQL collation signify? Am I asking for trouble?------------------------------------Assuming that I set Latin1_General_CS_AI (or any other case-sensitivecollation) at the database level, I believe my DDL/DML for that databasealso becomes case-sensitive. How can I specify that I want ONLY my dataaccess to be case-sensitive, and not my DDL/DML? I don't want to haveto remember to type "select * from MyCamelCase" when "mycamelcase"should work.Any help appreciated.A new SQLS DBA..aj
Hello, I would like to know if anyone here has any information on how to change the default version of SQL Server 2005 Express which ships with Visual Studio 2005 to the new SQL 2005 Express SERVICE PACK 1?
I see a file under disc:vswcuSSESQLEXPR.exe I am wondering if changing that file with the new one from Microsoft Download Center will do it...
There's another file under that folder named sqlexpr32.exe I am wondering what it's for?
Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
So I am fairly new to Express and I have installed it on my development machine; much tio my chagrin it is quite difficult to import data into SQLEXPRESS. I have a 'sa' account setup and I have created a new database and table within that database, however, when I try to import data into that table by setting up a link server to excel I am having some major issues!
I ran this code first to create the linked server...
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:Anchor_Hocking blactionlist.xls'
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking".
The file is not open. I have granted full access rights to all users....I am really frustrated!
Also, how can I get SSIS on this machine with SQLEXPRESS?
First of all I would like to politely greet everybody as I'm new on that forum and new to Data Mining in fact.
To introduce myself I can say I'm a student of Computer Science and I'm trying to use Time Series algorithm for weather analysis. I know that forecasting weather is a hopeless task even for the fastest computers in the world but what I'm trying to do is a kind of aposteriori analysis of historical data to notice some dependencies or characteristic weather behavior on a specified region and perhaps make some short time predictions.
I tried Time Series Algorithm although I have some doubts about methodological justification of this choice (if You have any critical comments please share them with me). But my main questions are about the usage of the algorithm itself:
I've read the documentation and a tutorial on this page for historical predictions but I still don't know what exactly are HistoricalModelCount and HistoricalModelGap. I know that my historical predictions are bounded by a €“ HistoricalModelCount*HistoricalModelGap*, but it's a rather operational knowledge... The explanation is always clouded with an €œinternal model€? phrase. Can You point me to a document where I can find some more detailed information? (What is the form of the model? How is it built? etc.)
Periodicity Hint. How should I treat these optional values? Are they other possible periods of data? I have data about weather measurements made every six hours for thirteen years** so is it a good choice to set this parameter to {365*4,4} (The first goes for a year and the second for a day)?
This is a technical question and I'm really ashamed of myself that I bother You with it. On the time chart in a model Viewer I can see date from the last year only. Zooming out/in, clicking insanely on every pixel on the screen, did not give any result (apart of broken mouse buttons). Is is possible to browse that data in mining model viewer chart? Thank You in advance for Your replies!
*This formula suggests how this parameters could work but I would like to know it for sure €“ don't want to make some awful mistakes in my project. :-) **Of course I plan to reduce the amount of data but the period will stay.
The first question is how to of TimeSeries Algorithm?
Using SQL Server 2005 TimeSeries Algorithm ,I build a data mining model.But after three days,it is still training.The data has 2,200,00 rows.
So what can i do to improve the processing speed.
Thanks!
The second question is parameters in Data Mining Query Task.
Data Mining Query Task is used to get data from data mining model.In the mining model form, i choose a mining model . And in the query form,i wrote a dmx ,"select flattened top 100 predicttimeseries([Xssl],1) from [Time Series XSSL]".Last i choose a table that is for the data from mining model.
