InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 12:11:05 GMT20http://www.sqlservercentral.com/Forums/Topic945657-20-1.aspxFirstly, I hope this is posted in the right area. Secondly, I have little to no knowledge of how to do this. The only thing I have as of now is a system DSN that I use to connect Access to Sybase. That currently works. As far as what I have tried: I have a local instance of Sql server 2005 with windows authentication. I have logged into management studio, and choosen:MyServer>Server Objects>Linked Servers> and right click "add new linked server". Now this is where it gets kinda hazy. I've tried to setup these settings on my own knowing nothing about them and failed. There was already a linked server created before I took on this project, but I tried to use it in a SELECT as below, and it fails.[code="sql"]SELECT *FROM MyLinkedServer.DatabaseName..TableName[/code]I got the following messages [code="plain"]OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "Method is not supported by this provider.".OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[Sybase][ODBC Driver]Driver not capable".Msg 7399, Level 16, State 1, Line 1The OLE DB provider "MSDASQL" for the linked server "MyLinkedServer" reported an error. The provider does not support the necessary method.Msg 7311, Level 16, State 2, Line 1Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for the linked server "MyLinkedServer". The provider supports the interface, but returns a failure code when it is used[/code]The Linked server that was created already has the following settings:Linked Server: MyLinkedServerServer Type: set to Other data sourceProvider: Microsoft OLE DB Provider for ODBC DriversProduct Name: SQL AnywhereData source: set to the name of my system DSN which I use to access Sybase from AccessProvider string: blankLocation: blankCatalog: blankI'm guessing location and catalog don't need to be configured because they are set in the system DSN that I am using. Oh, on a side note, the system DSN has a driver "Sybase SQL Anywhere 5.0". When I click to configure it it has a server name of the SQL Anywhere server that I am trying to connect to, and all the correct settings. It uses a file at startup "C:\sqlany50\win32\dbclient.exe" which is the client that fires up when I use linked tables in Access. And the other thing is that I have a file called "dbsvmn50.exe" that fires up the database engine for the Sybase Database that I am trying to connect to and it is running.Any help would be greatly appreciated. I can try and explain more, but as you can probably see from this post, I have no idea of what I am doing.Wed, 30 Jun 2010 11:03:13 GMTloki1049http://www.sqlservercentral.com/Forums/Topic1262410-20-1.aspxGood Day People,I have had the following experiene in the past week:An application is suddenly unable to connect to the QL sever instance using the SERVER NAME or IP ADDRESS of the HOST. I change the instance field on the application to (local) and the apliation connects. This behaviour is same for SSMS. Can connct with (local) but cant connect with the SERVERNAME. The confusiing thing is that this happens just fr a ehile and later on i can connect using the SERVERNAME.My aliases are same as before, the application is on the same host as the database instance. My version is below:Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86)Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)Tue, 06 Mar 2012 11:45:23 GMTkennethigirihttp://www.sqlservercentral.com/Forums/Topic1218506-20-1.aspxI have SQL Server 2008 and SQL Server 2008 R2 Express Edition installed in my machine. I was using SSMS of SQL Server 2008 R2 Express edition to connect to SQL Server 2008 instance in Windows Vista and it was working fine. Last week, I upgraded OS to Windows 7. After that, SQL Server 2008 Management Studio Query Editor is getting disconnected automatically when it is idle for sometime. I have to re-connect again in order to run query. What could be the reason? Is there any option to set Query Editor idle time or something?.. Please help.. Thanks in advance,Rekha K RaviThu, 08 Dec 2011 04:09:49 GMTrekhakravihttp://www.sqlservercentral.com/Forums/Topic1276891-20-1.aspxHi,One of my ETL server is running on SS 2008 R2 (SP1) is giving connectivity problems when SQL Instance is reached to ALLOCATE MAX Memory. Here is the details ...The physical box is running on Windows 2008 R2 and memory is 24 GB and we allocated Max memory: 16 Gb for SQL Server. we left 8 GB for OS. Because lot of ETL (SSIS) jobs are running on the same box. The problem we are getting, when SQL Instance reaches to 16 GB memory, it is NOT ALLOWING users to connect to SQL Instance from "SS management studio". At the same time, when we use the PORT number and try to connect with "SS Management Studio" it is connecting without any issues. We are getting the error (WITHOUT using PORT number) as below ...[b]A network-related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL server is configured to allow remote connections.[/b]In the normal situaltion we are able to connect the SQL. So we conclude that there is no firewall issues, no port blocking issues or "Allow remote connections to this server" issues. Does anyone experience the same problem, please suggest me why SSMS is NOT able to connect WITHOUT PORT when the instance consumed MAX Mem?.Mon, 02 Apr 2012 13:24:12 GMTs007reddyhttp://www.sqlservercentral.com/Forums/Topic1272141-20-1.aspxHi Seniors,I am very new to HP Nonstop servers and started working in Guardian environment. I need to write a client application on Windows XP which should connect to SQL/MP database on HP NonStop Servers and perform few operations.Now, can you please guide me how can I achieve this.I have no idea where to start the connection from and are there any drivers I need to install and so on.Thanks in Advance,KiranFri, 23 Mar 2012 15:32:31 GMTkiranbhatter1803http://www.sqlservercentral.com/Forums/Topic1260953-20-1.aspxHi,We have a new Attunity Connect driver to access this legacy relational database platform. We have an ODBC DSN setup which easily lets us access data via Excel 2007/2010.Within SSIS I can also connect to the Tandem server in the Server Explorer window, simply adding it as an ODBC server. This exposes all the tables in the SQL catalog defined in the ODBC DSN. This lets us Retrieve Data using the right-click context option and the data flows forth. Lovely so far.However, I am now struggling with exposing the same list of tables in a Data Flow / Data Flow Source connection.I have added a data flow task to the control flow, and then added an ADO.NET, data flow source object to the data flow. I have also created a Tandem data source, which is using the System.Data.Odbc Provider with the standard Connection string of "Dsn=TandemCatalogName;Uid=:pwd=*******".Then in the ADO.NET source editor I have created a new connection, using that ODBC DSN. however, when I try to expose the list of tables that are displayed in the Solution Explorer panel, I get an error"Could not retrieve the table information for the connection manager "Tandem Name". Object reference not set to an instance of an object.".I know that we only have the ODBC drivers (Attunity) and not the ADO .NET versions. However, when I use the OLE DB data flow source object, it does not expose the DSN and I don't know how to specify the server name as you would for a SQL Server.I have read in MS Help online that there is meant to be an ODBC Connection Manager, as well as many others that don't display in the Toolbox (eg. SMO, SMTP, WMI, FTP etc).Do I need to use a different Control Flow object, to contextually reveal the ODBC Connection Manager, as all the help suggests is there to use and I can't find any download links for it.Interestingly, an Attunity option does appear within the OLE DB Source Editor, Native OLE DB list. Its called "Attunity OLEDB Provider and Query Processor for Heterogeneous Data Sources". I am assuming because it appears in this list it is deployed on my PC, but at this stage I can't verify either way. It could be that all client drivers deploy but we only have the ODBC Server component.I am an experienced Tandem DBA and a beginner SQL Server SSIS designer, for context on what I currently understand. I have created and run some basic packages that load data from one SQL Server 2008 database to another merging in records from CSV files etc. The above issue is using Visual Studio 2008, Version 9.0.30729 and Tandem NonStop SQL / MP on G06.18.Thanks in anticipation of any help.CheersEricFri, 02 Mar 2012 10:43:32 GMTericjlawsonhttp://www.sqlservercentral.com/Forums/Topic1258899-20-1.aspxI am new here and hope this is in the correct post area.I am having issues connecting to my SQL server in Windows7. I am soing the smae steps I did with XP. I am using Access to connect to the SQL server and run daily functions.I receive the following errors..SQL State '01000'SQL State '08001'SQL Server Error 6Any thoughts would be greatly appreciated.Again, this is working fine with XP Pro.Same server, server name, just giving me the errors.Any other information to assist let em know.Thank youCameron DavisTue, 28 Feb 2012 07:44:07 GMTcameron.davishttp://www.sqlservercentral.com/Forums/Topic1252531-20-1.aspxHello,I'm on SQL Server 2005 Workgroup Edition.Our application is being troubled by deadlocks and I'm trying to find out what is causing it.I've set trace on DCBB TRACEON(1222,-1)and run a test in an unrelated database to cause a deadlock.However nothing seems to be written to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\DBCC TRACESTATUS(1222) confirms it is on.is this because it's Workgroup Edition as it works fine on our Developer Edition?As always, any help is appreciated.GilesWed, 15 Feb 2012 09:10:45 GMTgiles.claphamhttp://www.sqlservercentral.com/Forums/Topic1247827-20-1.aspxfriends,when I connected to sql server 2000 through a login form in my vb 6 application an error occured, "Unclosed quotation mark before the character string....." Because the error raised when I typed the login name, accidentaly I typed [b]admin'[/b] instead of [b]admin[/b] I used variant array for pass parameters.How can I solve this issueMon, 06 Feb 2012 23:59:09 GMTssa2010http://www.sqlservercentral.com/Forums/Topic1243805-20-1.aspxAdmittedly I know very little about this topic so apologies for my naivety.Basically the issue is a lot of time outs on a sql db...We have a legacy ASP web applciation that uses a include file to create a connection to a database, to my knowledge (having looked through the code) these connections are never closed but as this include file exists in many pages presumably it is being constantly re-opened - I know, huge problem!Now the connection uses sql authentication to connect to a SQL 2000 db. When I look in the activity monitor to debug things there is only one process id within SQL. Am I right in thinking that because the connection string is pooled this one connection is being reused time and time again hence just one showing up in SQL? Any advice or pointers would be hugely appreciated.Mon, 30 Jan 2012 11:04:29 GMTdavid.alcockhttp://www.sqlservercentral.com/Forums/Topic1244346-20-1.aspxHello everyonei have an application which actually is an excel workbook that connects to an sql server 2000,reads data and returns the data back to excel or writes data to sql server tables.I have this application (the excel files) in the same machine that the sql server is installed.My problem is that in the production environment the user that will work with the excel files will not have installed in his machine sql server and there has to be a bridge between his pc and sql server which is located on another machine probably on the same intranet.Can someone tell me how this is done?**The excel file uses ado to connect with the sql server on my machine by using VBA.Tue, 31 Jan 2012 07:01:49 GMTapostolis.karayiannishttp://www.sqlservercentral.com/Forums/Topic1238889-20-1.aspxSorry -- Accidentally posted this long before it was ready!We have a front end db in MS Access which connects to clients' SQL Server data.Most of our clients have no problems with this code, but one of our clients does.Dim cnSQL As ADODB.ConnectionDim rstSQL As New ADODB.RecordsetSet cnSQL = New ADODB.ConnectioncnSQL.ConnectionString = strSQLConnString cnSQL.OpenrstSQL.Open "Exec usp_SavePrepFormConfig VariablesHere....", cnSQL, adOpenKeyset, adLockOptimisticrstSQL!FieldName = BlahBlahrstSQL.UpdateIt's on the .Update that the code fails for the client.They get this Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'CorrectServerName\CorrectInstanceName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.An interesting thing is that for this client, strSQLConnString = "driver={SQL Server};SERVER='CorrectServerName\CorrectInstanceName' ,Port;Trusted_Connectio=Yes;DATABASE=mts7"But after I set the cnSQL.ConnectionString = strSQLConnString, and I then ask for what the value of cnSQL.ConnectionString IS, then I get"Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server; SERVER='CorrectServerName\CorrectInstanceName' ,Port;UID=;APP=2007 Microsoft Office system;WSID=UserNameHere;DATABASE=mts7;Trusted_Connection=Yes"(For other clients, if I ask for the cnSQL.ConnectionString after I set it, the ConnectionString is what I told it to be.)Anyone know why the client might be getting this Error?Their data is in SQL Server 2005, but its Compatibility Level is SQL Server 2000, if that matters. (Though others clients have had the same config.) To get more specific configuration data, I'll have to log on with the client again.I've seen this question asked a couple of times, but I haven't found an answer.Thanks!Thu, 19 Jan 2012 10:10:03 GMTaleksrhttp://www.sqlservercentral.com/Forums/Topic1234235-20-1.aspxHi everbody I need some suggestionsI Have a sql server(2005) default instance in Windows Server 2003, I need to connect it to a MySQL server as a linked server. I dwonloaded mysql-connector-odbc-5.1.9-win32.msi and mysql-connector-odbc-5.1.9-winx64.msi. The second one is not supported by my server, so I use the first one. I added a System DSN for MySQL server. Testing the DSN is ok.But when I configure the Linked sever, the windows "New Linked Server" never finishes when click "Ok". In the activity monitor are there some process related with ODBC (54,56,59). Then I try to kill them with "KILL #SPID", but it responses with this: "SPID 56: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." and never finishes.Some details:[code="sql"]kill 54[/code][code="sql"]SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.[/code][code="sql"]select * from sys.sysprocesses where spid in (54, 56, 59)[/code][b]SPID[/b]545659[b]KPID[/b]93801124811880[b]waittime[/b]806700788086521880696796[b]LastWaittype[/b]OLEDB for all[b]cmd[/b] KILLED/ROLLBACK KILLED/ROLLBACK KILLED/ROLLBACK [b]hostprocess[/b]7712 10308 7712 [b]blocked[/b]0 for allSome people suggested me I must restart the service, but I don't want to restart the services because is a production server. Another suggestion??thanks and regards.Wed, 11 Jan 2012 11:27:14 GMTobregonjose3http://www.sqlservercentral.com/Forums/Topic1233710-20-1.aspxHi,How to take a table backup in specific drive in SQLServer?Normally, we take table backup using SELECT * INTO new_table_name FROM old_table_nameif we do so, can we get the table backup with same old table structure (including primary & foreign keys, indexes..) ?And more over, we don't get this backup in specific drive. right ?Please advise.Thanks and Regards,Ravi.Tue, 10 Jan 2012 21:34:09 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1233110-20-1.aspxJust throwing this one out there to see if anyone has any idea a) what might have caused this or b) resources to help try to figure it out...We have an application that uses ODBC to connect to our SQL Server (2008). All at once, we were suddenly unable to use that application. Every other SQL Server connection method was working properly.I reset the ODBC connections on all of the servers and things were fine - but I'm now trying to figure out what can happen that would cause ODBC connections on several servers to suddenly fail all at the same time. The fix was simply to manually run through the ODBC configuration wizard anyplace that was trying to connect to our SQL server. Any and all ideas are welcome- I'm going to try to replicate the issue in a test environment so that we actually know what caused the situation in the first place. While it's an easy fix, I'd really prefer not to see this happen again!Thanks in advance!Tue, 10 Jan 2012 06:09:02 GMTKiarahttp://www.sqlservercentral.com/Forums/Topic1216157-20-1.aspxhi all,while try to connect ms sql server 2008 from remotely system using query analyzer (sql server 2000) it through error like:unable to connect server hostname\msdbserverserver :Msg 17,level 16 ,state 1[microsoft][ODBC sql server driver][BDNETLIB] sql server does not exists or access denied...Mon, 05 Dec 2011 04:01:18 GMTramlalasahhttp://www.sqlservercentral.com/Forums/Topic1226028-20-1.aspxI am using SQL Server 2008 R2 on Windows Server 2003. We have 3 Active Directory groups, DivisionEast, DivisionWest, DivisionSupv. Windows user tuser1 is a member of DivisionEast and DivisionSupv. Windows user tuser2 is a member of DivisionWest. In SQL Server we have defined a Windows authenticated login for each of the Active Directory Groups and a database user for each login. For auditing purposes I would like to know the Windows authenticated login a database user used to connect to SQL Server. The procedure sp_who ans sp_who2 returns tuser1 and tuser2 in the loginame. Every query I've found so far returns tuser1 or tuser2, not DivisionEast, DivisionWest or DivisionSupv. Can this be done? If so can you give me some guidance in what tables or procedures to use?Thanks,NancyThu, 22 Dec 2011 15:23:03 GMTnmccormickhttp://www.sqlservercentral.com/Forums/Topic305523-20-1.aspxI have successfully setup a linked server from my SQL Server to our AS400 that I can use for SELECT's and UPDATE's. However I can not get an INSERT or DELETE to work.First what syntax should I be using. For the insert if I use:INSERT INTO OPENQUERY([R2D2-Test], 'SELECT ABAN8,ABALPH FROM CRPDTA.F0101') (ABAN8,ABALPH) VALUES (999999,'SQL TEST')I get the following:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method.OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.].If I use:INSERT INTO [R2D2-Test]..CRPDTA.F0101 (ABAN8,ABALPH) VALUES (999999,'SQL TEST')I get:Server: Msg 7314, Level 16, State 1, Line 1OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].I'm not sure if this indicates a config. issue on either the ODBC conn. or the linked server or if I have a syntax error.For the DELETE, if I use Delete from [R2D2-Test]..CRPDTA.F0101DAU where aban8 = 100I get:Server: Msg 7314, Level 16, State 1, Line 1OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].If I use:Select * from openquery([R2D2-Test],'Delete from CRPDTA.F0101 where aban8 = 100')I get:Server: Msg 7357, Level 16, State 2, Line 1Could not process object 'Delete from CRPDTA.F0101 where aban8 = 100'. The OLE DB provider 'IBMDA400' indicates that the object has no columns.OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='IBMDA400', Query=Delete from CRPDTA.F0101 where aban8 = 100'].Can someone help figure out the correct configuration and/or syntax.Help!!Thu, 31 Aug 2006 15:32:00 GMTDon Urquharthttp://www.sqlservercentral.com/Forums/Topic1219269-20-1.aspxI get an Error when Setting CLASSPATH JDBC Driver SQL Server.The documentation states to set the CLASSPATH but when I do I get the error listed below.CLASSPATH =.;C:\Temp\sqljdbc_3.0\enu\sqljdbc.jar CLASSPATH is not recognized as an internal or external command, operable program or batchFri, 09 Dec 2011 05:40:03 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1193344-20-1.aspxHi,i am new in developer.i need to conect database connection string. can u help me.Thu, 20 Oct 2011 00:06:50 GMTsthenmozhi14http://www.sqlservercentral.com/Forums/Topic1199834-20-1.aspxHi,If I am using a connection pool, is there a way to log different application name in master..sysprocesses each time an application uses a connection from connection pool.By setting Application Name property, all the pooled connections are showing same program name in master..sysprocesses.Is there a way to pass program name while re-using a connection from connection pool?Thanks.Thu, 03 Nov 2011 06:09:15 GMTpraveen_vejandlahttp://www.sqlservercentral.com/Forums/Topic1191546-20-1.aspxI have an VB application written with VS2008 that has been running against a third party applications DB's on SQL2000 servers successfully for years, but when I updated it to use the new version running on SQL2008 servers it starts running and seems to work fine but then fails with : [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].If I debug the application in VS and when the error occurs hit Continue (sometimes twice) the application continues normally, though it will fail several times before I get all the way through. I have checked the final data once this happens and it is totally complete.The failure occurs on one or the other of two UPDATE statements for the two tables that are being populated with data extracted from other tables that are on different servers (some 2000, some 2008 during conv. period) different instances, and in different DB's (one for each of our stores). The output tables for the application have been moved from 2000 to one of the 2008 servers. I am unable to determine why the connections seem to be dropping as I only open the connection at the beginning of the routine and close once all data has been written. I have checked the logs on my local box, the SQL box (both SQL and system) and can find no indication of the problem.I don't know if this is a problem with the code, the server, or something else. I am attaching the relevant section of the code w/ passwords etc. changed but nothing else. If more of the code is required I would gladly update this. The two updated causing the failure are recordsets are the rsDistOut and rsTransOut in attached code.Can anyone help with what the problem might be or suggest how I can go about determining what it is? Any assistance would be greatly appreciated!Mon, 17 Oct 2011 12:08:29 GMTDon Urquharthttp://www.sqlservercentral.com/Forums/Topic1194731-20-1.aspxI have a sql server set up on one of my computer at home and would like to access the server/db's with other computers. The server name is using the name of the computer plus the sql name I assigned [ComputerName]\[Server1]I can see this server from the computer is is loaded on with other applications (access/excel etc.) but not from another computer. When I try to do a query from excel from a different computer I get "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication." I can share files between computers and use remote desktop but not sql.I think the issue may be the domain that it is assigning (computer name) so when I try to login from a different computer it is reading it as a different domain. Is there anyway to work around this?Thanks for any help.Fri, 21 Oct 2011 17:13:16 GMTrick 93262http://www.sqlservercentral.com/Forums/Topic1174706-20-1.aspxhi,How to find Last used date of an index in SQLServer 2005?Please advice.Thanks and Regards,Ravi.Wed, 14 Sep 2011 02:06:05 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1177298-20-1.aspxI have been trying to create a stored procedure to extract data on a regular basis from MS SQL 2005 connecting to Informix database using ODBC. On my SQL box I have set up a data source System DSN 'CX', then used sp_addlinkedserver, sp_addlinkedsrvlogin, and openquery. But then i get error messages see below.FYI- I have been able to use MS Access 2003 to connect via the same DSN to extract data and link tables.[b]Code to Link Server:[/b]EXEC sp_addlinkedserver@server = 'uccx',@srvproduct= '',@provider = 'MSDASQL',@datasrc= 'CX',@provstr = 'HOST=LIFEBOOK; SERVER=uccx; Protocol=onsoctcp; Database=db_cra;UID=user; PWD=pa;'GOEXEC sp_addlinkedsrvlogin 'uccx', 'False'GO[b]Connection is shown as LINKED.... but when i run the Query..[/b]FROM OPENQUERY (uccx, 'SELECT * FROM Informix_contactcalldetail')[b]I receive these Error messages...[/b]OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Informix][Informix ODBC Driver][Informix]Attempt to connect to database server (uccx) failed.".Msg 7399, Level 16, State 1, Line 2The OLE DB provider "MSDASQL" for linked server "uccx" reported an error. Authentication failed.Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "uccx". I have tried OPENROWSET to directly connect but i receive similar Authentication failed messages. As i mentioned, the puzzle is that MS Access ODBC works fine.Thank you in advance for any help anyone can provide.RayMon, 19 Sep 2011 09:04:12 GMTrayberryhttp://www.sqlservercentral.com/Forums/Topic716534-20-1.aspxHello, I am trying to connect to SQLExpress from an ASP.Net web app.Here is the connecton string:Provider=SQLOLEDB;Data Source=machinename\sqlexpress;Initial Catalog=databasename;User Id=*****;Password=*****;With the above connection string I get this error message:Keyword not supported: 'provider'.So I remove the Provider...Data Source=machinename\sqlexpress;Initial Catalog=databasename;User Id=*****;Password=*****;This connection string gives me the error...An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.So I'm damned if I put in the provider and damned if I don't. What am I missing?Thanks.Wed, 13 May 2009 16:20:13 GMTrdprecurehttp://www.sqlservercentral.com/Forums/Topic1163664-20-1.aspxHi All,I am trying to execute a stored Proc via vbscript. In one of the stored proc, it setup with the output parameter. If i run the query in the query window, it works fine, but not with VBscript. (error - The connection cannot be used to perform this operation. Its either closed or invalid in this context) Here is my code. Any help will be appreciated. on Error Resume Next'Declare VariablesDim strCount,oArgs,oAPIDim DBName, sProc, VarParam1, VarParam2, VarParam3Dim FilesCount, oConObj, oCmdObj'Declare ConstantsConst SERVERNAME = "myServerName"const adCmdStoredProc=4const adInteger=3const adChar=129const adVarChar=200const adDate=7const adParamInput=1Const adParamOutput = 2const adParamReturnValue=4Set oArgs = WScript.Arguments'Collect data from the argumentsDBName = oArgs(0)sProc = oArgs(1)VarParam1 = oArgs(2)VarParam2 = oArgs(3)VarParam3 = oArgs(4)VarParam4 = oArgs(5)VarParam5 = oArgs(6)'Set Connection String to DatabasesConnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=" & DBNAME & ";Data Source=" & SERVERNAME ' Establish connection.Set oConObj = CreateObject( "ADODB.Connection" )Set oCmdObj = CreateObject("ADODB.Command")oConObj.ConnectionString = sConnectoConObj.OpenSet oCmdObj.ActiveConnection = oConObj' Open recordsetoCmdObj.CommandText = sProcoCmdObj.commandtype=adCmdStoredProcoCmdObj.Parameters.Append oCmdObj.CreateParameter("Param1", advarchar, adParamInput, 200)oCmdObj.Parameters("Param1") = VarParam1oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param2", adChar, adParamInput, 200) oCmdObj.Parameters("Param2") = VarParam2oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param3", adChar, adParamInput,200) oCmdObj.Parameters("Param3") = VarParam3oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param4", adChar, adParamInput,200) oCmdObj.Parameters("Param4") = VarParam4oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param5", adChar, adParamInput,200) oCmdObj.Parameters("Param5") = VarParam5oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param7", adVarChar, adParamOutput, 200)oCmdObj.Parameters("Param7") = QueryOutPutoCmdObj.ExecuteFilesCount = oCmdObj.Parameters("Param7").Value wscript.echo "Result :" & FilesCount Set Rs1 = NothingSet oConObj = NothingSet oCmdObj = NothingMon, 22 Aug 2011 16:06:11 GMTfidelis.xavierhttp://www.sqlservercentral.com/Forums/Topic1157335-20-1.aspxHi masters!!! It's my first time here. Please, forgive my poor knowledge.I've got a working linked Server with Oracle, but one problem:When I execute a count query it gives me the right answer, but, when I execute a simple select to retrieve the data, it gives me just a part of my count.[b]SELECT * FROM OPENQUERY(myserver,'SELECT COUNT(*) FROM mytable')[/b] ==> This count returns 349, that is the number of lines in my table.[b]SELECT * FROM OPENQUERY(myserver,'SELECT * FROM mytable')[/b] ==> This select returns only 250 lines, that is a part of my table.Please, can you help me with this scenario?It would have the same result (349 lines instead of 250).Thank you so very much (in advance)...Tue, 09 Aug 2011 16:22:44 GMTneocooliohttp://www.sqlservercentral.com/Forums/Topic1151564-20-1.aspxI have a situation with one of our clients where one of our utility applications is losing it's SQL connection after it has successfully retrieved data. For instance, the customer can log into the application, load a list of members, but on trying to view an individual members details we'll get the following exceptionA transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)The Connection String uses an IP address to get to the server - don't know if this makes behaviour any different - I would doubt it though.Have tried it with pooling=false but no difference. Have elongated the server time out setting to no avail.We have multiple clients connection to the DB server in this fashion but only some of them are experiencing this behaviour.At this point, at a loss as to where to look next - hopefully someone has some fresh ideas about what might be happening - or can ask some questions that might open up those ideasTIASteveSun, 31 Jul 2011 16:51:25 GMTBeerBeerBeerhttp://www.sqlservercentral.com/Forums/Topic1149592-20-1.aspxI am upgrading a legacy application to .NET and it now uses ADO.NET rather than ODBC. However, I have found that when querying CHAR columns ODBC always pads them to their column length whereas ADO.NET treats them properly by giving you their actual data i.e. if the column was created with ANSI_PADDING on then it will be padded and with ANSI_PADDED off it will be trimmed. This is actually causing problems in the new application because the legacy code expected the consistency of ODBC. Does anyone know if there is a simple way to make ADO.NET behave in the same way as ODBC?Wed, 27 Jul 2011 13:06:17 GMTkieron.dyehttp://www.sqlservercentral.com/Forums/Topic1127013-20-1.aspxNot sure if the description fits what I'm trying to do.I have one table where the person's name could appear in many columns. Instead of having the name we have a userid in there. I can't figure out I create a sample of what I'm trying to do. Every time I try I just confuse myself more. So thought I'd see if I could get help here. Trying to show the display name from the user table instead of the number. Thanks for taking your time to help me. Table 1Project Main_sales_Rep, Assoc1_SalesRep, Assoc2_SalesRep, Assoc3_SalesRep, SpendAlpha_1, 100001, 100003, 100025, , $40,000Telcom_1, 100003, 100015, , , $30,000Telcom_2, 100001, 100003, 100015, 100025, $17,000User TableUserID, FirstName, LastName, DisplayName100001, Bugs, Bunny, Bugs Bunny100003, Porky, Pig, Porky Pig100015, Daffy, Duck, Daffy Duck100025, Petunia, Pig, Petunia Pig Output to be (showing the DisplayName):Project, Main_sales_Rep, Assoc1_SalesRep, Assoc2_SalesRep, Assoc3_SalesRep, SpendAlpha1, Bugs Bunny, Porky Pig, Petunia Pig, , $40,000Telcom1, Porky Pig, Daffy Duck, , , $30,000Telcom2, Bugs Bunny, Porky Pig, Daffy Duck, Petunia Pig, $17,000 Thu, 16 Jun 2011 17:00:41 GMTkickersdjhttp://www.sqlservercentral.com/Forums/Topic1124642-20-1.aspxHelloAll,I have refreshing the pivot table using script task inside the SSIS package and it worked great. But in my new server they are not installing Office application and all that will be availiable is drivers. How do i get my pivot table refreshed using script? Script that i am using at the moment with office application. Public Sub Main() Dim objXLS_Application As Object Dim objXLS_Workbook As Object Dim s_outputfilename As String objXLS_Application = CreateObject("Excel.Application") s_outputfilename= "\\C:\Test.xlsm" objXLS_Workbook = objXLS_Application.Workbooks.Open(s_outputfilename) Call objXLS_Application.Run("'" & s_outputfilename & "'" & "!MacroRefreshAll") Call objXLS_Workbook.Save() Call objXLS_Workbook.close() objXLS_Workbook = Nothing Call objXLS_Application.Quit() objXLS_Application = Nothing Dts.TaskResult = ScriptResults.Success End SubMon, 13 Jun 2011 15:48:38 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic995758-20-1.aspxDear All,How to find and set the port number for named instance in SQLServer 2005 through GUI and command prompt?In SQL Server configuration manager ->TCP/IP -> properties ->IP1,IP2,IP3 and IP ALL,What is the difference among these IP's, under which IP, I can set the Port number.What is the limit to set static port numbers in SQLServer 2005 other than default port numbers 1433 & 1434 ?Thanks and Regards,Ravichandra.Thu, 30 Sep 2010 00:53:37 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1120008-20-1.aspxODBC problems connecting to SQL Server 2008Can ping both ways, no firewall issues, NETCAT can spawn a shell using port 1433.ODBC driver version 2.575.1132.00Both machines XP pro , SP 2 and 3I get (in part) Connection failed , SQL state 01000 server error 10061, then SQL state 08001 error 17On the server side I have TCP/IP enabled, and I’ve rebooted the machine a number of times since I did that.Sun, 05 Jun 2011 20:27:01 GMTArt Heymanhttp://www.sqlservercentral.com/Forums/Topic1116729-20-1.aspxit looks like when my page is under heavy load i dont have enough connection..and that is killing my site :( i get this from our error systemMicrosoft SQL Server Native Client 10.0TCP Provider: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full. We use for conetion this conection strig: SQL and IIS is same server!Provider=[b]SQLNCLI10[/b];Server=127.0.0.1,xxxx;Database=xxxxxx;Uid=xxxxxx;Pwd=xxxxxx;im wondering if this maybe the wrong connection string to use??I have also look at SQL server settings, its set to Unlimited connections..Is there something im missing here? See settings i have attached a PDF whit screenshotsThe server is BIG has lots of power and lots of ram.on heavy load it rungs like 33% cpu and 16 gigs ram. and it has 48 gigs ram total.we are at roads end, we have try almost everything in the IIS i can think of, nothing helps.So now im asking for help in this forum, as i may suspect its an SQL issue, and not IISany one have any advise??Thanks so much... :)Sun, 29 May 2011 00:40:35 GMTbrownghosthttp://www.sqlservercentral.com/Forums/Topic1116698-20-1.aspxHi everyone I've searched and tried so many things, and this one has me completely stumped. I have my app on a hosted webserver with Viux. It runs fine, but it will not connect to my database. I get the error: [i]Unable to get records. Unable to connect to database. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A socket operation was attempted to an unreachable network.) [/i] So, I am using Sql Server 2000, it listens on port 1433. I have enabled port forwarding on my Cisco router.What I am stuck on is the connection string. Inside the network, I have no trouble, my connection string looks like this:[i]<add key="Databaserep_rpt1" value="Data Source=SQLSERVER;Database=rep_rpt;Trusted_Connecti on=yes" />[/i] The application itself uses a table in the database for logins and passwords, but sql server is set to integrated windows security (which is why I have no issues from within the network). So what do I specify in the connection string for the web.config file? Do I pass the username and password for the PC's domain login? Or is my issue something else entirely? I'm lost. Here's the final attempt at the connection sting, I've tried so many variations, I don't know which ones anymore: [i]<add key="Databaserep_rpt1" value="Data Source=208.xxx.xxx.xx,1433;Database=rep_rpt;Trusted_Connection=yes" />[/i] Any thing you can offer in the way of help would be much appreciated. Thanks, TammySat, 28 May 2011 17:05:27 GMTTAMMYAUDETTEhttp://www.sqlservercentral.com/Forums/Topic1112875-20-1.aspxDim con_exl As System.Data.OleDb.OleDbConnection con_exl = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _ "data source=C:\test.xls;Extended Properties=Excel 8.0;") Dim testAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter() testAdapter.TableMappings.Add("Table", "test") con_exl.Open() Dim testCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand( _ "SELECT * FROM [test$];", con_exl) testCommand.CommandType = CommandType.Text test.SelectCommand = testCommand Dim testDataSet As DataSet = New DataSet("test") testAdapter.Fill(testDataSet) DataGrid1.SetDataBinding(testDataSet, "test")Sat, 21 May 2011 04:58:56 GMTsetivhttp://www.sqlservercentral.com/Forums/Topic986932-20-1.aspxDear All,Can I schedule full backup of the logshipped/Primary/Source database during logshipping in SQLServer2000?I think, it’s possible only in SQLServer2005 since it won’t break the log chain. Can you confirm what will happen in SQLServer 2000 if we schedule full backup during logshipping?Thanks in advance.Regards,Ravichandra.Thu, 16 Sep 2010 00:13:39 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1108798-20-1.aspxDim rst As New ADODB.Recordset Dim con_sql As New ADODB.Connection Dim fld As ADODB.Field con_sql.Open("File Name=E:\sql.udl;UID="";PWD=""") rst.Open("delete from titleauthor", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic) rst.Open("insert into titleauthor(au_id,title_id,au_ord,royaltyper)values('1-1','po',1,1)", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic) rst.Open("select * from titleauthor", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic) While Not rst.EOF For Each fld In rst.Fields ListBox1.Items.Add(fld.Value & ",") Next rst.MoveNext() End While rst.Close() con_sql.Close()Sat, 14 May 2011 02:14:05 GMTsetivhttp://www.sqlservercentral.com/Forums/Topic1103991-20-1.aspxi execute the following statement succesfully:select * from OPENROWSET('MSDASQL','DSN=Mistral_Gemas', 'SELECT * FROM Clients')but i'm not able to run it succesfully again after 1 minute. everytime i execute it before the minute the following error message is shown :Msg 7399, Level 16, State 1, Line 1The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".i don't think is a permission issue, rather a locked file issue or something...any ideas?...thanks in advanceThu, 05 May 2011 09:44:55 GMTwhitetool2084