Initial Connection To SQL 2005 Drops After Several Commands
Mar 12, 2007
We are having some unusal problems with an upgrade to SQL Server 2005. I've come across two other people who have had this type of issue but I haven't found a resolution. We are running SQL Server 2000 in production and we have installed SQL Server 2005 on new hardware. We have been working for about a week to verify that all of out existing applications will work properly with SQL 2005.
We have several old VB applications that open a connection to the database and reuse it for days on end. We can test these apps against the production 2000 server and they run fine, when they connect to the 2005 server the connections work fine for 5 - 15 minutes then we get an error trying to execute a command and are told that the connection doesn't exist. (One interesting note, there is only one line of code in the whole program that connects to the database but we have seen 3+ connections from the app when we check in Management Studio for SQL 2005.)
Just to get though the tests we put in some error handling to reconnect if the connection is lost. When we do this the application ends up re-connecting only once, the new connection never has a failure even after hours of testing and we see a 300% performance improvement.
We can reliably cause this problem by stopping the sql server, starting it, waiting a few minutes, then running our app. After one run of the app the problem seems to go away for a while and we can't re-create it nearly as consistantly. Only occasionally will the initial connection fail and every time it does it will be after 5-10 minutes of activity.
Both machines are connected to the same switch and we have ruled out networking issues. We are only seeing this problem with VB6 apps, the VS2005 apps run great. We are looking for the cause of this connection behavior and a fix for it because we don't have the time to fix code in dozens of VB apps because the connections are not stable. Granted the practice of holding a connection open isn't good to begin with but with legacy code you have to work with what you're dealt.
Any thoughts as to why ADO connections from VB6 to SQL 2005 would not be stable?
Hi there, I am in the process or deploying our existing .NET 1.1 application together with its SQL Server 2000 databases to 2 new hardware platforms (one for databases and one for application). I have transferred the databases and installed the application (using our installers). I have tested the database logins and checked the database user rights - everything is fine - or so it appears to be. However, when I try to open the application it receive the following exception [Exception: Data Source=172.16.7.1;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE;Pooling = 'false';Application Name=WEBAPP; System.Data.SqlClient.SqlException: Cannot open database requested in login 'http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE'. Login fails. Login failed for user 'myAccount'. at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin() at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, SqlConnectionString connectionOptions) at System.Data.SqlClient.SqlConnection.Open() at FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt)] FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt) +655 FrameWork.DataServices.DBConnect.GetInternalConnection() +16 FrameWork.Components.DBComponent.get_m_SqlConnection() +59 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Int32 iRetry, Boolean bIgnorecase) +53 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Boolean bIgnorecase) +337 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd) +7 It appears as if my ADO connection string is faulty. If a manually connect using a connection string in the format I expected it to be, everything works. Here are my manual connection string: Data Source=xxx.xx.x.x;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=MYDATABASE;Pooling = 'false' What is inserting the URL that I used to open the page ahead of my Initial Catalog parameter?Any assistance will be more than welcome.CJ
Hi, I use a Remote Sql Server Express instance, and I have a strange behavior.. The first connection is really slow and I don't know how to fix that.I read some posts about this topic but I didn't find the right solution.Is there a way to "keep alive" the connection between my IIS server and the SQL one ?I check the auto-close property and it sets to false. Any help ? Stan
SQL 2012 SSIS package.I have a package connection that has the initialcatalog set in the connection string/properties page. This package connection also has an expression defined to set the initial catalog at runtime according to a passed in parameter. It works fine.
I am trying to create a second package in this same manner, but the connection does not seem to want to keep both the hardcoded initialcatalog and the expression to set it dynamically.
I can hardcode the initcatalog just fine, when when I add the expression to set it dynamically later, it clears out the initialcatalog I added.
What am I missing, why was I able to do this in the other package? I compared as much as I can think of between the two packages, all seems similar.
We find that if we deploy the OLAP database with a different name on the test server, then regardless of how we change the connection string provided to the SSIS package that processes the cube, then the package fails to connect to the database. To clarify:
In development the OLAP database is called MyOlapDB and the source database is called MySqlDB. Both are on the same machine. When the the application is built and released for test, the test team install the databases on a replica of the production environment (i.e. web app on one machine, OLAP DB on another and SQL database on yet another). They also, quite rightly, implement the new test databases so they incorporate the build version number. So, MyOlapDB123 and MySqlDB123 are both from build 123.
This is when the problems start. Regardless of how the connection string is specified in the job that processes the cube, the SSIS integration package fails with the error:
[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the database with the ID of 'MyOlapDB' does not exist in the server with the ID of 'OurTestServer', or the user does not have permissions to access the object.
We have tried config files and job properties, but neither work. Also, simply attempting to run the package using the DTEXECUI does not work either.
Looking inside the XML of the package, we clearly see the ConnectionManager object which has the original connection string, which is
Data Source=localhost;Initial Catalog=MyOlapDB;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;
However, editing the initial catalog here still does not solve the problem. Searching the XML for the string MyOlapDB reveals the OLAP database name in two other places - both within the object data of the two Analysis Services Execute DDL tasks.
Anyone know how to solve this problem without having to hack the XML of the package?
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
Hi there! I just installed SQL 2005 Express and would like to use the command prompt to interact with my database such as creating/deleting etc. How do I start the SQL command terminal from command prompt? A tutorial website would be great! Thanks!
Hi all, I'm having some trouble getting commands to be available. Certain commands are grayed out, and i'm assuming not working.
For example I copied this code from online:
SELECT h.USERID, c.KEY1, CASE srectype WHEN 'A' THEN 'Appointments' WHEN 'C' THEN 'Calls' ELSE 'Nothing' END AS ContactType, COUNT(h.USERID) AS Total_Contacts, COUNT(CASE WHEN resultcode BETWEEN 0 AND 4 THEN 1 ELSE NULL END) AS Contacts_Made, COUNT(CASE WHEN resultcode = 4 THEN 1 ELSE NULL END) AS Sales FROM CONTHIST h INNER JOIN CONTACT1 c ON h.ACCOUNTNO = c.ACCOUNTNO WHERE (h.USERID = @userident) AND (h.ONDATE BETWEEN @startdate AND @enddate) AND (h.SRECTYPE IN ('A', 'C')) GROUP BY h.USERID, c.KEY1, h.SRECTYPE ORDER BY h.USERID, c.KEY1, h.SRECTYPE
From that bit of code the following commands are gray: BETWEEN AND NULL INNER JOIN IN
I can only assume that i'm doing something fundamentally wrong. Is there a setting i'm missing in SQL Server Managment Studio EXpress to make these available? Do I need to 'import' anything?
SQL Server 2005: Microsoft SQL Server Enterprise Edition (64-bit) Microsoft Windows NT 5.2 (3790)
SQL Version 9.00.3175.00
I have 2 identical servers... one for DEV and one for TEST. On the DEV server, I can use xp_cmsdhell to execute "administrative commands" such as NET SHARE and AT. On the identically configured TEST server, I can not. Those command return the "System error 5 has occurred. Access is denied" error. I CAN run other commands via xp_cmdshell (DIR, NET USE, etc.). This is true regardless of the account I connect as.
The SQL Server service accounts in both instances are domain accounts belonging to the local admin group.
I "know" that I ran across this when setting up the DEV server and am "fairly sure" I found a reference to a known problem with 2005's xp_cmdshell and administrative commands. But for the life of me I can not now find any such reference, nor can I find any differences between the DEV and TEST servers in the area of users, groups, policies, or anything else security-related. (99.9% of the internet searches I do end up being about either enabling xp_cmdshell -- done -- or setting up an xp_cmdshell proxy account -- not needed).
We have a database that we are preparing to set up Merge replication on. We often make schema changes via T-SQL, many of these changes are made to tables on which an ALTER TABLE statement will not do (rather the creation of a temporary table, copying of the data, deleting the original table then renaming the temp table).
My question is how this will affect Merge replication. I have not been able to find anything that is very clear on this. From what I gather, if a table needs to be dropped that is participating in a merge replication, I need to go through the manual process (manual, as in calling the necessary system stored procedures) of removing the the article from any publication (and subsequent filtering), make the modifications, then re-add it to the publication and filtering.
Is this correct? If so, a new snapshot needs to be created, correct? If so, I have a follow-up question regarding that snapshot.
If a new snapshot needs to be created, what happens during replication/synchronization? Meaning, since it is a new snapshot, does the client (subscriber) see the whole thing as new or is it smart enough to recognize that only that one table i have changed needs to be synched?
I am quite new to replication, as you can tell, so please forgive the rambling. I ask these questions because I have heard different answers on both questions...so I would like to get the correct answers.
Is there a way to have SSIS monitor a folder for file drops? I have been unable to determine which object/task to use for this. We need the ability to have it monitor for files being dumped by other systems, pick those up and then process them. Thanks for your assistance.
We've implemented mirroring between two identical servers. Sporadically, the mirroring session will drop and the ERRORLOG reflects the errors below at the exact time the mirroring session becomes suspended. We do not manage our back end network since we use a dedicated hosting environment at a remote location. Is this issue solely caused by network connectivity issues, or are there other factors at work?
2007-07-16 04:24:37.24 spid23s Error: 1453, Severity: 16, State: 1. 2007-07-16 04:24:37.24 spid23s 'TCP://192.168.215.92:5022', the remote mirroring partner for database 'evestment', encountered error 1204, status 4, severity 19. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance. 2007-07-16 04:24:48.46 spid23s Error: 1479, Severity: 16, State: 1. 2007-07-16 04:24:48.46 spid23s The mirroring connection to "TCP://192.168.215.92:5022" has timed out for database "evestment" after 10 seconds without a response. Check the service and network connections.
I've written an SSIS package which has two DataFlow tasks running simultaneously, importing the data from two separate ASCII files into two staging/prep tables: one Tasks imports records into an ORDER table while the other Task imports the ITEM-level records into an ITEM table.
Starting several days ago, the ITEM DF Task began importing a partial set of records even though the ASCII file clearly showed more records were available.
For example, in today's processing, here is the message from SSIS: << [ITEM Ascii File [1695]] Information: The total number of data rows processed for file "\webserv01globalscapeusr vr logstandarditem.txt" is 217361. >> yet i only found 10,401 rows -- and they all appeared to be the bottom 10,401 rows of the incoming ASCII file.
I cannot see any other untoward messages about SSIS encountering a problem.
There are no Error Output restrictions on the ASCII file.
The ASCII row preceding the 10,401 rows i DID get looks OK.
Can anyone suggest a possible place to look which might explain this "jumping" over the nearly 207,000 rows?
In SSIS it reads all the date, time, seq etc but I lose that super critical "key" in the outputted data flow. If I look at the XSD in visual studio it shows 2 tables related via a "nested heirarchy"... "hist" and "r" with "key" being an attribute in the hist table and all the rest as attributes in the "r" table. However in SSIS it only shows one table so I can't get at that key :(
Any idea how to remedy? I keep messing around with the XSD file but that hasn't gotten me anywhere thus far...
I ought to know how to do this, but it escapes me at the moment. I need to write an insert statement for a table that will be based on a complex select query. The select query may return rows that are already in the target table. In that case, I don't want duplicates created, but I don't want the query to error, either. I can't remember how to set that up.
My server (SQL 2005 SP2) typically runs with a procedure cache usage of about 92% or higher... lately it seems like at some point in time during the day it just drops to anywhere between 50% and 65%... with this comes horrible server performance and many snowball effects. If I clear the procedure cache it will go up only about 10% for a minute or two. The only way I can get it to recover completely seems to be restarting the SQL service. Then it will be fine till the next incident. The database is a read only (not set to read only but no updates other than replication). and the same SPs are run over and over and over throughout the day. also did notice that the compiles of the SPs goes up drastically at this point also. not sure if this is part of the cause or part of the effect.
CPU is normal. response from anything (even sp_who) is slow.
i do not understand the way procedure cache works completely so I thought I would ask for some direction.
Any ideas where to look or where to start??? Any thing I can do to catch this when it happens would be great.
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success. Anyone have some info on this?
Does anyone have a good strategy or technique for preventing database drops in SQL Server 2000? I know in 2005 DDL triggers rock, but in 2000 what can you do to audit who drops a database why keeping the same permissions intact.
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Please forgive my ignorance, I am by no means a SQL Expert, but have encountered a strange issue.
I have 6 SQL Servers, Primarily SQL 2005 (one older SQL 2000) all loaded on Windows Server 2003 SP1.
We use the servers for a proprietary database that we created which is the backend to a software package we sell.
The issue I have is: We have added a Security account to the servers, and in one case we have granted rights for this account to the TempDB system database. However, whenever we restart this server SQL drops this user account, thus severing connectivity for the app that is relying on that account.
I have set the account as DB-Owner etc, but nothing sems to keep it on re-start.
I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.
However, when I deploy the model to the server it is only deploying 76,500 records for those tables.
Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
We are using BCP in a Process task. The value for the path to BCP's error log requires double quotes around it. We initially put this value in a configuration file, and that worked fine. Yesterday, we eliminated the config file and tried to use the "Set Values" tab of the SSIS SQL Server Agent job to pass in this value.
The package variable was assigned the value of the path -- all except for the leading double quote. The closing quotation mark was included in the value.
We tried adding a second double quote at the beginning of the value, but that caused the job to fail immediately.
For now we will work around this problem by putting the quotes around the value in a Script task inside the package.
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations Copy table partitioning schemes Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
All -- Please help. I have some questions about connection strings.
BACKGROUND...
Note that I am using the "SQL Native Client OLE DB Provider", SQL Sever 2005 Express, ASP.NET, C#.NET, and VS.NET 2005. Note that I do not want to use the "Attach a database file" type of connection string. Note that I am using the site http://www.ConnectionStrings.com as a reference. Note that this "Standard security" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Uid=MyDatabaseUser;Pwd=MyDatabasePassword;" />...does not work and causes the following run-time error...A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) Note that this "Trusted connection" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Trusted_Connection=yes;" />...works but is less-than ideal because it uses a Trusted Connection rather than a Username/Password combination.
QUESTIONS... (A) What exactly does the error in Item 4 mean? (B) Do 4 and 5 imply that one must use a Trusted_Connection for all SQL Server 2005 Express connection strings when the database does not reside in the web application's App_Data folder or use a "Attach a database file" type of connection string? (C) Are there any alternatives? (D) What do you suggest?
Problem: ColA (Source) Rounding error to PARTY_NO (Destination) I have a field of text of in a flat file that the flat file connection manager Source picks up correctly €ś70000893€? However when it gets the OLE DB Connection Destination the data has changed to 70000896. That€™s before its even Written to the database. The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07 Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00 ColA (Source) PARTY_NO (Destination) 71167300 71167296 70329000 70329000 70410000 70410000 Any ideas people? Thanks in advance Dave
I have been using a licensed copy of Visual Studio 2005 and MS SQLServer 2005 for some time but am only now trying out the Reporting Services functionality.
I have attempted to follow the instructions from url:
However despite confirming that the report services are running and also checking the configuration by following the information in the above web page I still get the following problems.
1. When attempting to create a project via the wizard I get the following error: Exception has been thrown by the target of an invocation. If an attempt is made to add a new data source I am unable to choose the data source type (eg: Microsoft SQL Server). I am not given a choice for the type, in fact the relevant drop down is blank.
2. A project can be created without the wizard but if I right click the Shared Data Source folder I do not see the next Pop up to set the data source parameters.
I assume I am missing something quite fundamental - however so far I cannot see what.
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.
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \serverfolder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '---' is not yet available". can you guide me what are causes of this problem and how may i solve it?
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
