Transactions With Remote Connection
Jul 20, 2005
A colleague wants to insert many millions of records where the
values are computed in a C++ program. He connects to the
database with ODBC, and does an INSERT for each row.
This is slow, apparently because each INSERT is a separate
transaction. Is there a way to delay committing the data
until several thousand records have been written? Inside
SQL Server this is simple, but I don't see an equivalent
when using ODBC. Or is there something better than ODBC?
Or might it be faster to write values to a file and then
use bulk insert? I would appreciate any thoughts on this
general problem!
Thanks,
Jim
View 1 Replies
ADVERTISEMENT
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Aug 23, 2006
I'm trying to run an SSIS package. The package runs on an SQL 2005 server on Win2k3 server. The package tries to connect to another win2k3 server with sql 2000 to retrieve some data. However, I recieve the errormessage shown in the topic.
I found info about modifying the MSDTC security settings under "component services" and did so. I made sure everything was allowed. However, the result was the same. Does anyone have any other idéa about what could cause this problem?
PS. The package works fine if I set up both databases on the same physical machine...
Regards Andreas
View 3 Replies
View Related
Oct 11, 2007
HiI'm going insane.I have a club starter kit on my local server which is working fine.I have migrated the database to my remote host and seems to have transfered ok.For a brief period I was able to connect to the database remotely, but now I cannot I get the following, even though SQL Server Surface Area Config is set to accept remote connections.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)This is my connection string;<add name="ClubSiteDB" connectionString="Data Source=xx.xxx.xx.xx,xxxx; Network Library=DBMSSOCN; Initial Catalog=tets; User ID=sqladminbob;Password=sqlbob4398;" providerName="System.Data.SqlClient="/>I hav also tried to use aspnet_regsql.exe to add the remote provider but I get the same message. Thanks
View 3 Replies
View Related
Apr 7, 2008
When running the project in debug mode or non-debug mode, I get the following error from MS Visual Studio:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package [Connection manager "SYBASE_CONNECTION"]: The connection manager failed to defect from the transaction.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Any ideas???
View 8 Replies
View Related
Aug 30, 2006
When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.
How can I allow the server to allow this.
Any help appreciated
regards
View 1 Replies
View Related
Feb 8, 2006
In one of my packages, I set the package-level property called TransactionOption=Required. During run-time I saw an error saying "[Execute SQL Task] Error: Failed to acquire connection "SQL_DW". Connection may not be configured correctly or you may not have the right permissions on this connection. ". When the property is changed to anything other than Required, it works fine (the calling package that calls this package is not involved in a transaction).
The machine running the packages is Windows Server 2003, and so is the database where the data lives. I verified that the machine containing the database does has Enable Network DTC Access checked in Control Panel -> Add/Remove Windows Components -> Application Server.
Is anyone else having this problem?
View 6 Replies
View Related
Jun 15, 2005
Hello - where I can find the tools to view/see open connections, transactions not closed ... etc of a MS Sqlexpress database?
Thanks a lot - FOX
View 3 Replies
View Related
Mar 1, 2006
I'm getting "Access denied" error when I try to connect to SSIS from my desktop (Database Engine is fine). I'm a member of sysadmin within SQL, as well as an administrator on the server. I don't have any problem when I log on to the server directly via Remote Desktop and open SSIS.
I went into the surface area config tool, and I see that Database Engine and Analysis Services both expand and have a sub tabs to enable Services and Remote Connections, but Integration Services does not expand to a Remote Connections option, just Services. We're running MSDN Enterprise edition.
Does it matter if all the SQL services are using "Local System" for the logon ? I use an administrative logon on my SQL2000 boxes, and was fiddling with them on the 2005 box yesterday, but it seemed to cause more problems, so they are all Local System for now.
Am I missing something obvious ?
View 3 Replies
View Related
Nov 24, 1998
Ok sql-masters, I'm stumped and need someone to come to the rescue.
The issue: nested transactions = dblibrary process dead; broken connection or runaway process.
On the first try it usually bombs with the dblibrary... error. If I continue trying to run it, it will run, but actually runaway.
The sp:
/****** Object: Stored Procedure dbo.UP_MR1700 Script Date: 10/03/1998 11:00:08 AM ******/
CREATE PROCEDURE UP_MR1700 AS
DECLARE @TotRecs int
TRUNCATE TABLE MR1700_WorkTable1
EXECUTE MR1700_Insert_WT1_OnHand
EXECUTE MR1700_Insert_WT1_Packed
EXECUTE MR1700_Insert_WT1_Allocated
EXECUTE MR1700_Insert_WT1_Capacity
SELECT @TotRecs = (SELECT COUNT(Store_No) FROM MR1700_WorkTable1)
IF @TotRecs = 0 OR @TotRecs IS Null
RETURN -100
TRUNCATE TABLE MR1700_WorkTable2
EXECUTE MR1700_Insert_WT2_Classes
EXECUTE MR1700_Insert_WT2_Depts
EXECUTE MR1700_Insert_WT2_ClassGroups
EXECUTE MR1700_Insert_WT2_DeptGroups
EXECUTE MR1700_Report_Request
View 1 Replies
View Related
May 19, 2008
Hi. I'm (very) new to SQL Server. I have run into what seems to be a very common problem, judging by the number of Google hits, but it's not actually in the FAQs here, as far as I can see.
Situation
I installed SQL Server Express with Advanced Services 2005 on my Windows XP Pro laptop, using (with one exception) the default install. The exception was to put the data files on a separate partition (D: ) where I have more free space.
SQL Server Management Studio Express connects happily, and I set up a single data import table.
Issue: bcp Fails to connect to default local instance
I initially tried bcp in output mode, prior to working in earnest on importing my data. However the command line-
bcp MyDatabaseName.dbo.UserProfile out UserProfile.txt -T -c
Fails with the error-
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
As I understand it, the local instance of SSE is the default, and should use a local (shared memory?) connection. This seems to work for Management Studion Express, but not bcp.
While investigating this problem, I tried enabling remote connections (all types) through the Surface Area Configuration wizard, and found that remote connections using either of:
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S localhostsqlexpress
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S mycomputernamesqlexpress
both work ok.
Extra Info
When starting Management Studio Express, it says something like "Configuring environment for the first time". However it says this every time it is started, not just the first run after installation, does this indicate some configuration data is not being successfully stored?
I have found threads that look similar to my issue-
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=836670&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3351349&SiteID=1
However these do not relate directly to bcp, and to be honest my knowledge of SSE is slender enough at this stage that i'm unsure if there are really relevant.
Any hints on how to proceed much appreciated.
Regards: Colin E.
View 6 Replies
View Related
Jun 19, 2006
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
View 54 Replies
View Related
Apr 24, 2007
Hello everyone, I am trying to connect to a remote SQL 2005 database from Microsoft SQL Server Management Studio.I enabled the remote connection on the server and creating a user on the server.I am trying to connect to the server from my workstation using sql server authentication with the login name already created on the server.
I am receiving this error: cannot connect to "server name"cannot open user default database. Login failed.Login failed for user "username".(Microsoft SQL Server, Error: 4064)
Can anyone help?
View 1 Replies
View Related
Mar 31, 2008
hello !
I am deploying a websete in visual studio2005 after deploying, when run a website Login Failed: The user is not associated with a trusted sqlserver connection. error occurs plese anybody helpme. urgent
Thanks.
View 3 Replies
View Related
Apr 23, 2006
I am attempting to connect to SQL 2005 server. The server and the VS 2005 are on the same computer. I have configured the db so it uses the security for asp. The log in section connects fine and checks for the user. I have combined the old db with the new ASP security. If the user is not found then I open a connection using the open command and check for the user in the old part of the db. The is where I have trouble. I get the message below that the server connect but then won't allow remote connections. I have check the setting and the allow remote connections is checked. Thank in advance if anyone can help.
Endeavor
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)
View 2 Replies
View Related
Oct 4, 2004
I am now trying to connect with the correct details to an MS SQL machine.
The machine in question is not local to me and the internal ID is //bobsmachine01.
I know this is a dumb question, but I shall ask it anyway...I am using MS Access 2000's "Upsizing wizard" and it asks me for the machines name - what should I put to connect to it??
I know the IP of the machine and the internal ID for the MS SQL Server.
Any help much appreciated,
Chas
View 1 Replies
View Related
May 8, 2006
How would I connect to remote SQL 2005 database using Management Studio?
The only difference from local DB is that I type IP address instead of server name, right?
I'm getting error:
Testing the registered server failed. Verify the server name, login cridentials, and then click Test again.
Additional information:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53)
I did everything from article "How to configure SQL Server 2005 to allow remote connections"(http://support.microsoft.com/?kbid=914277&SD=tech). SQL Browser enabled. Both TCP/IP and Named Pipes are enabled. Exceptions in firewall for sqlsrvr.exe and sqlbrowser.exe added.
Did not help.
What else could I check?
View 3 Replies
View Related
Jun 7, 2007
Hi,
I have SQL Server 2005 running on a machine with "Allow Remote Connections" property turned on. This server has SQL Server Authetication mode turned on.
I can connect to the server locally with sa login and password but when I try connecting to the server remotely it gives me following error:
TITLE: Connect to Server
------------------------------
Cannot connect to ServerName.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233).
Any help is appreciated.
View 4 Replies
View Related
Jul 5, 2007
Hi all,
I have a machine that is running windows 2003 server, and sql server 2005 developer edition(let's call this machine server one). I have another machine running windows xp and sql server developer edition, visual studio 2005 pro(machine 2). I have developed a small windows application on machine 2, but I'm trying to connection to the database that one mcahine one, and I keep getting some the following error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
Any idea why? How do I need to configure that ?thanks
View 5 Replies
View Related
Apr 14, 2008
Hello,
I have microsoft sql server management studio express 2005
but if i want to connect it says that i cant connect because remote connection are not aload but i searched support on microsoft and i enabled it restarted the database enige but still dont works can please someone help me?
Pleasant greats.
Irakli
AND HOW DO I KNOW WHAT MY SERVER NAME IS?
MAYBE ITS BECAUSE MY SQL BROWSER ISINT RUNNING? AND I CAN'T START IT...
View 18 Replies
View Related
Mar 9, 2007
Hi all,
Need to know if its possible to allow remote connections and access a Sql Ce database using TCP/IP.
Thanks for your help
Hach
View 3 Replies
View Related
Apr 12, 2006
Could someone tell me how to remote connect to other server. Everytime when I try to connect to other server, I get an error message. Eventhough I have opened the 1433 port, allow the remote connection using both TCP/IP and named pipes, and open the firewall for connection. The following is the error message
"An error has occurred while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)(Microsoft SQL Sever)"
Thank you
View 4 Replies
View Related
Feb 10, 2007
hi guys,
i'm trying to access my friend's database from my computer but i cant seem to detect his database though i can detect his server.
what i did was i tried adding a connection to his database.i typed in the server name but his database name couldnt be detected.
the server name was: WPLSQLEXPRESS
am i missing something here?
this was the error generated when i tried testing the connection:
An error has occurred while establishing a connection to the server.When connection to SQL Server 2005,this failure might be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces,error: 28 - Server doesn't support requested protocol)
what does it mean?how do i solve this problem?
thanx
View 13 Replies
View Related
Jun 24, 2006
System.Data.SqlClient.SqlException: An error
has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
I have enabled the remote connection in the surface area configuration, and i've also enabled my sql browser.
This error raised when i'm testing my just installed WSRFdotnet using
NUnit testing, however, when I tested my remote connection using SQL
Server Management Studio Express CTP, it worked. btw, I'm using SQL
Express
any solutions?
thx
View 3 Replies
View Related
Sep 18, 2006
hi communitythe error something like this " error 40, could not open connection to sql server" . For community information i try to transfer my membership table from sql express 2005 to sql server 2000. my application something like email where user need to create their account first before they can log in. I use membership default in mssql express . and my datacenter do not support sql express ( like all we know most of them). I already surf some tips from the internet how we can transfer our membership from sql exppress to sql server but i get this error . i already try to change the setting from sql server surface area configuration but it still not working.. i hope someone ( the jedi ) can help me .. ThankMay The Force Be With U
View 1 Replies
View Related
Mar 4, 2007
I have researched over and over on how to solve the error code below. I have tried every suggestion under the moon and still get the same error. Please provide some insight. Here is exactly what I have done regarding the remote connection issue:
Error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
History:
1. Created a test site on my local machine (using MS Visual Web Developer 2005 Express Edition) call WebSite8 for test purposes.
2. Simply created a single page to allow users to create an account by dragging and dropping the CreateUserWizard onto the designer screen.
3. Performed some basic formatting (font, theme, etc) on the web control.
4. Ensure that TCP/IP and allow remote connection defaults were changed to allow remote connections.
5. Verfied that the login control worked on my local machine. Everything worked fine. I was able to create two new users and was sent to the "complete" portion of the control.
6. Opened the aspnet_memberships and aspnet_users databases to ensure that the two test accounts were created.....everything good so far.
7. Copied all files from WebSite8 (1. App_Data Folder - includes ASPNETDB.MDF file created by Developer when I dragged and dropped the CreateUserWizard onto the designed sreen, 2. the main asp web page I called createaccount.aspx, and 3. web,config file) via ftp to my test web site hosted by 1and1. My test web site is named something different that WebSite8.
8. I created a test.htm page on the 1and1 web site to link to the createaccount.aspx page which is in the same path as the test.htm.
9. The process let me enter the user name, password, etc but when I hit submit, I get the error listed below.
10. Followed some other research suggestions and verified that ApplicationName in the aspnet_Membership was set to "/". It is.
11. I checked the web.config file and it does not seem that any connection strings were created when I dragged the CreateUserWizard onto the screen. Not sure if this is an error on this control or not or if it is ok as is(?)
I believe there is some sort of issue on the hosted web site where the sql server is not linking to the database on the hosted site and is likely trying to link back to my "local" server......just a feeling.
I have tried everything that I can think of after performing relentless research on this error. I am new to database programming, ASPNET 2.0, SQL, etc but I catch onto things very quickly.
View 2 Replies
View Related
Jun 3, 2007
Hi,
I've worked with SQL Server plenty but never in this specific manner. Hoping someone can shed some light.I have a webform on web server A that needs to talk to a SQL server database that web server B accesses via its web application. I do not haveany privelidged access to server B or the sql server it uses (no FTP to webserver, no remote access via Enterprise Manager to the SQL server). The Admin of Server B told me that I can use a DSN connection to talk to the SQL server database that the web app on server B uses. He emailed me the names of the SQL database tables AND a copy of the web.config from server B which shows the DSN implemented like this:<appSettings> <add key="dsn" value="data source=sql3.domainname.net;database=dbname;User Id=dbuserid;password=dbpw;" /> <add key="default_language" value="en" /> <add key="error_email_to" value="someone@domainname.com" /> <add key="baseHref" value="http://www.mysite.com/" /> <add key="website_name" value="$$$$$$$$$$$$$$$$$" /></appSettings>
I was planning to run my webforms on Server A where I do have FTP, Control Panel, etc.So, I wrote this code into my VB.NET codebehind file.
Dim oODBCConnection As OdbcConnectionDim sConnString As String = _"Driver={SQL Server};" & _"Server=sql3.domainname.net;" & _"Database=dbname;" & _"Uid=dbuserid;" & _"Pwd=dbpw"oODBCConnection = New Odbc.OdbcConnection(sConnString)oODBCConnection.Open()... commands to access data and load something into a control on a webformoODBCConnection.Close()
When I attempt to run the webform in my browser from Server A. I'm getting this error:ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).The error is EXACTLY the same whether I place the appSettings section in server A's web.config or leave them out.I have obvious questions about the error like...Might sql3.domainname.net simply be refusing communication requests that don't come from the Server B domain?Might the sql3.domainname.net be setup so that I need to use another protocol (not tcp/ip)?Might my code need to be changed to support this type of connection (i.e. I didn't code it right)?I spent some time reading http://support.microsoft.com/kb/328306/en-us but I'm at a loss as to what the best course is to take for debugging this.Thanks in advance for any insight you can offer.
Stewart
View 1 Replies
View Related
Aug 17, 2007
I had made an application which is using SQL Expresss.When I deploy that application on a server where only .Net Framework 2.0 is installed then it gives an error of remote connection.Does that need some prerequisite for SqlExpress or the reason of error is something else.
View 3 Replies
View Related
Oct 6, 2007
hello allmy web page occur this errorAn error has
occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) what is this, my site does not contain the remote connection i m using sql server 7.0so please help me Ashwani Kumar
View 1 Replies
View Related
Dec 3, 2007
when connecting to database it give me the error like this
Message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" string
can you have any idea to solve it..
View 2 Replies
View Related
Dec 25, 2007
Hi,i installed sql server express 2005. Everything worked good until yesterday..Indeed, since yesterday and for an unknown reason, i get this error when attempting to login via Management Studio:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact thatunder the default settings SQL Server does not allow remote connections.(provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)Now, i know how to solve this: putting the option 'Remote connections'.But i want to know why i suddenly has to change that option in order to get in Management Studio.Here are the settings:- i login with authentification mode.- The sql browsing service is started- following sql client native protocols are enabled: shared memory, tcp/ip, named pipes- following sqlexpress protocol is enabled: shared memoryRemark: i still can login in with sqlcmd (i mean with remote connections off).Thanks for helpT.
View 4 Replies
View Related
Jun 21, 2008
i am building an asp.net 2.0 website. i was trying to use the Sql Server 2000 database of the hosting i bought. the reason behind this is that several people are working simultaneously and it is hard to sync the database changes by different people. now the problem is when i run the site ( IN MY HOME ), from IIS or VS 2008, i get a connection error:An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: Named Pipes Provider, error: 40 -
Could not open a connection to SQL Server)But when i use the same project in my office ( with same connection string ), i have no problem to connect. and my friend can also connect from his home and office. so what can be wrong in my home XP (SP2) pc? i have turned off windows firewall and i have uninstall the norton 2007 anti virus that i had to resolve the problem. what else i have to do. and why in the error it is trying to connect to Sql Server 2005 where i am using 2000 in the remote server. i have an Sql Server 2005 Express edition install with the VS2008, but i have it in my office also and my friend also installed it. here is the connection string:<add name="SqlServer2000" connectionString="Data Source=mssql.fashionzoom.com;Initial Catalog=fashionzoomer;User Id=*******;Password=******;"/> i will be so thankful if someone help me to solve this problem. thanks in advance.
View 4 Replies
View Related
Jan 20, 2005
I would appreciate any help anyone can offer
I am developing a web application that needs to access SQL Server. I have implemented forms authentication. My Sql server is on another machine and I am unable to connect because ASPNET is a local account. Is there a way around this, I really need to keep forms authentication and I am forbidden to place Sql Server on the same machine
View 6 Replies
View Related