Considering blank spaces

I need to make a function for coding string in which i make difference
between strings that are identical except from final blank spaces. In SQL 20
0
exists an option (collate set or something else) that can make SQL
distinguish betwene two string like these:
'MYSTRING1'
'MTSTRING1 '
ThanksOne method...
if(cast('as ' as varbinary) = cast('as' as varbinary))
print 'match'
else
print 'does not match'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE FUNCTION MyStringCompare(@.str1 nvarchar(MAX), @.str2 nvarchar(MAX))
RETURNS nvarchar(15)
AS
BEGIN
IF ((@.str1 + N'#') = (@.str2 + N'#'))
RETURN N'match'
RETURN N'don''t match'
END
GO
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1') -- -> match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1 ') -- -> don't
match
SELECT tempdb.dbo.MyStringCompare(null, N'MYSTRING1 ') -- -> don't match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', null) -- -> don't match
SELECT tempdb.dbo.MyStringCompare(null, null) -- -> don't match
Greetings,
Urs
"checcouno" wrote:

> I need to make a function for coding string in which i make difference
> between strings that are identical except from final blank spaces. In SQL
200
> exists an option (collate set or something else) that can make SQL
> distinguish betwene two string like these:
> 'MYSTRING1'
> 'MTSTRING1 '
> Thanks|||How about this:
declare @.a varchar(30)
declare @.b varchar(30)
set @.a = 'MYSTRING1'
set @.b = 'MTSTRING1 '
IF (@.a = @.b AND DATALENGTH(@.a) = DATALENGTH(@.b))
PRINT 'Matched'
ELSE
PRINT 'Different'
Roy Harvey
Beacon Falls, CT
On Fri, 30 Jun 2006 00:26:01 -0700, checcouno
<checcouno@.discussions.microsoft.com> wrote:

>I need to make a function for coding string in which i make difference
>between strings that are identical except from final blank spaces. In SQL 2
00
>exists an option (collate set or something else) that can make SQL
>distinguish betwene two string like these:
>'MYSTRING1'
>'MTSTRING1 '
>Thanks|||>> I need to make a function for coding string in which make difference betw
een strings that are identical except from final blank spaces. <<
Be very careful about this. SQL pads shorter strings with blanks for
comparing them, so your function could destroy expected behavior and
you would wind up with your own private language. Otherwise, use the
DATALENGTH() function; it is standard and portable.

Connection Strings/Data Sources

Hey all,

I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.

Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.

Thanks in advance!
LarryIf you are dropping connection objects, drag and drop functionality wasn't meant for that completely. You could try in your code to switch it to the connection in the web.config in one of the page events before it is used.

In the next version of Visual Studio, and most likely Visual Web Developer, you can use web.config entries in the connection objects.

Brian|||Hmmm...

So what can I do now? Should I define some kind of global string or an application variable with my database connection string and then search and replace all references to the connection string with that?

My application needs to be able to easily switch to a backup data source if the main source goes down.

Thanks in advance!
Larry|||I like the idea better of an n-tier architecture, where you have a data access layer that defines the connection information. However, if that's not for you, then I don't know what to tell you other than define the connection object in your web page. But then that throws off your datadapter or command objects dropped into the form.

I personally always created the database objects in the code because of that. Or look at changing the connection string in one of the page events. I think I got that concept to work in a windows form app, I think it may also work in a page event.

Brian

Connection strings.

Hi all,
Okay, a few questions. What is the difference between the following, and
which should I use?
1) Server=DBSERVER;Database=DATABASE;uid=UI
D;pwd=PWD
and ...
2) Provider=SQLOLEDB; Data Source=DBSERVER;Initial Catalog=DATABASE; User
Id=UID; Password='PWD'
Turns out that our ASP.NET pages were using #1, and ASP #2. However,
someone just changed our ASP pages to use #1, and we've been noticing some
strange issues (one of our clustered database servers went offline yesterday
and didn't fail over).
We're using SQL 2000 on Windows 2000 Server, and some ASP and ASP.NET pages.
Thanks for the help.
WadeCheck out:
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81
Anith|||I've been to both places -- while they have a lot of examples, do they
really discuss the differences between the different connection strings? I
certainly didn't notice any discussion.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eNz5$2N0FHA.3896@.TK2MSFTNGP10.phx.gbl...
> Check out:
> http://www.connectionstrings.com/
> http://www.carlprothman.net/Default.aspx?tabid=81
> --
> Anith
>|||The first one seems to be using an ODBC string while the second one uses a
OLEDB provider connection string.
Since ODBC is almost deprecated and its drivers not being updated these days
by MS, it is recommended that you favor OLEDB for your data access. Do a
search on MSDN or technet and you'll get all the information you need on
these API specs.
Anith|||Wade,
Might try here as well:
http://msdn.microsoft.com/data/Default.aspx
HTH
Jerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eNBwoBO0FHA.908@.tk2msftngp13.phx.gbl...
> The first one seems to be using an ODBC string while the second one uses a
> OLEDB provider connection string.
> Since ODBC is almost deprecated and its drivers not being updated these
> days by MS, it is recommended that you favor OLEDB for your data access.
> Do a search on MSDN or technet and you'll get all the information you need
> on these API specs.
> --
> Anith
>|||Thanks for all the help, guys!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23LuF8DO0FHA.2792@.tk2msftngp13.phx.gbl...
> Wade,
> Might try here as well:
> http://msdn.microsoft.com/data/Default.aspx
> HTH
> Jerry
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eNBwoBO0FHA.908@.tk2msftngp13.phx.gbl...
>|||In ASP.NET, the data access is very likely using ADO.NET SqlClient
namespace, which is specifically designed for SQL Server access. Therefore,
in connectionString you only need to specify database name and server name,
SqlClient.Connection object knows which PROVIDER to use. While #2 is used in
ASP, its data access could be ADO, ODBC... depending on the developer's
choice. Usually, "Provider" is specified in ConnectionString in this case.
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e$DhJzN0FHA.2652@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Okay, a few questions. What is the difference between the following, and
> which should I use?
> 1) Server=DBSERVER;Database=DATABASE;uid=UI
D;pwd=PWD
> and ...
> 2) Provider=SQLOLEDB; Data Source=DBSERVER;Initial Catalog=DATABASE;
> User Id=UID; Password='PWD'
> Turns out that our ASP.NET pages were using #1, and ASP #2. However,
> someone just changed our ASP pages to use #1, and we've been noticing some
> strange issues (one of our clustered database servers went offline
> yesterday and didn't fail over).
> We're using SQL 2000 on Windows 2000 Server, and some ASP and ASP.NET
> pages.
> Thanks for the help.
> Wade
>

connection strings, trusted-connection VS username-password, SQL Server 2005 Express

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 sitehttp://www.ConnectionStrings.com as a reference.Note that this "Standard security" connection string...
<add name="LocalSqlServer" connectionString="Server=MYTEST\OFFICESERVERS;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=MYTEST\OFFICESERVERS;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?

Please advise.

Thank you.

-- Mark Kamoski

Hi Mark,

Here are the answers to your questions:

(A). The error message means a client connecting to SQL Server may encounter this named pipe error when connecting to SQL Server if the server has not enabled named pipe support even though other protocols such as TCP/IP are available.

(B). You are not required to use a Trusted_Connection if you have enabled SQL Authentication on the SQL Server.

(C)& (D) I would suggest you to try the connection again after a while. If that doesn't help and you're always getting this error, please check the following articles for troubleshoot.

http://msdn2.microsoft.com/en-us/library/ms175496.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

connection strings in web.config

What am I doing wrong?

This works:

SQLConn.ConnectionString = "Data Source=McD_DB;Initial Catalog=McDFeelGood;User ID=uuu;Password=ppp"

Now I want to put it in my web.config, but these don't work ("ConnectionString property has not been initialized"):

SQLConn.ConnectionString = ConfigurationSettings.AppSettings("McDFeelGoodConnectionString")(VS IDE says this method is obsolete)
SQLConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("McDFeelGoodConnectionString")

<connectionStrings>
<add name="McDFeelGoodConnectionString" connectionString="Data Source=McD_DB;Initial Catalog=McDFeelGood;Persist Security Info=True;User ID=uuu;Password=ppp" providerName="System.Data.SqlClient" />
</connectionStrings>

OK well this worked:

<appSettings>
<add key="McDFeelGoodConnectionString" value="Data Source=McD_DB;Initial Catalog=McDFeelGood;Persist Security Info=True ;ID=uuu;Password=ppp" />
</appSettings>

connection strings for "create database" queries

I'm using connection strings to SQL Server Express that have the form

"DataSource= .\SQLEXPRESS;AttachDbFilename="(path to my development directory)\CurrentDevelopmentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

I'm assuming this works because the DB is already there, created "manually".

How might I build a connection string for a SQLCommand for a "Create DataBase" query?

I'm confused about what the AttachDbFileName would be, or if there would be another variable in its place, since there's no file yet.

Any guidance on this would be appreciated.

Thanks!

Hi,

You use a connection string like the below sample connection string

Data Source=KODYAZ\SQLEXPRESS;Initial Catalog=ContentManager;Integrated Security=True

Eralper

http://www.kodyaz.com

|||

There are essentially 2 ways of connecting to SQL Server in the case that you are discussing(there are actually way more but bear with me I am trying to simplify this).

1/ Is to use User Instances, this is what you are doing, this allows you to use a DB that exists in the file system, it is temporarily attached to the SQL Server Service as a result of your connection being opened. SQL Server spins up a special user instance of SQL Server to attach the database to, rather than using the exisintg running instance.

2/ SQL Server is running and you connect to master (a system database) or one of the already attached user databases.

To decide which technique to use it depends on your application as the 2 are somewhat exclusive within an application. If you choose 2 then just use the following connecting string and the create database should work;

"DataSource= .\SQLEXPRESS;Initial Catalog=Master;Integrated Security=True;Connect Timeout=30"

To use the database you just created you must either change the initial catalog to it ina new connection, or issue a "use newdb" command in the current connection.

|||

Thanks for the inputs. I resolved this by creating the connection string with the "file path" method for an existing DB. Then I executed the SQL for the DB Create using that connection.

connection strings for "create database" queries

I'm using connection strings to SQL Server Express that have the form

"DataSource= .\SQLEXPRESS;AttachDbFilename="(path to my development directory)\CurrentDevelopmentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

I'm assuming this works because the DB is already there, created "manually".

How might I build a connection string for a SQLCommand for a "Create DataBase" query?

I'm confused about what the AttachDbFileName would be, or if there would be another variable in its place, since there's no file yet.

Any guidance on this would be appreciated.

Thanks!

Hi,

You use a connection string like the below sample connection string

Data Source=KODYAZ\SQLEXPRESS;Initial Catalog=ContentManager;Integrated Security=True

Eralper

http://www.kodyaz.com

|||

There are essentially 2 ways of connecting to SQL Server in the case that you are discussing(there are actually way more but bear with me I am trying to simplify this).

1/ Is to use User Instances, this is what you are doing, this allows you to use a DB that exists in the file system, it is temporarily attached to the SQL Server Service as a result of your connection being opened. SQL Server spins up a special user instance of SQL Server to attach the database to, rather than using the exisintg running instance.

2/ SQL Server is running and you connect to master (a system database) or one of the already attached user databases.

To decide which technique to use it depends on your application as the 2 are somewhat exclusive within an application. If you choose 2 then just use the following connecting string and the create database should work;

"DataSource= .\SQLEXPRESS;Initial Catalog=Master;Integrated Security=True;Connect Timeout=30"

To use the database you just created you must either change the initial catalog to it ina new connection, or issue a "use newdb" command in the current connection.

|||

Thanks for the inputs. I resolved this by creating the connection string with the "file path" method for an existing DB. Then I executed the SQL for the DB Create using that connection.

Connection strings and SQL Express 2005

Hello,

I am working on porting a Windows Forms VB.net VS 2003 app with a SQL 2000 backend - to using VS 2005 with SQL Express 2005.

I cannot seem to figure out how to set my connection string to work in the new environment. I have already recreated my database in SQL Express 2005 and ported the VS project to VS 2005.

The old string was (in my app.config file):

<add key="ConnectionString" value="workstation id=MAIN1;packet size=4096;user id=sa;data source=MAIN1;persist security info=True;initial catalog=Dbname;password=####" />

Any ideas?

Thanks,

Sam

Which error do you get ? SQL Server Express is disabled for remote connection by default, you might check the Screencast on my site to see how to enable that.

HTH; jens Suessmeyer.

http://www.sqlserver2005.de

Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:

The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike

Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:

The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike

connection strings

This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.

hi,

a long as no particular keywords for SQL Server 2005 are used (say, the unsupported "MARS_Connection=yes;", and the like), the connection string is quite similar.. please have a look at http://www.connectionstrings.com/ for samples..

regards

|||Also have a look at the driver, if you install the application on a non SQL System, the SNAC will probably not exist. YOu will either have to ship it within your application, or use a MADC driver which is installed on the most systems.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

connection strings

I am trying to develop a web site. I have a local ms sql database on my machine.

I am trying to connect to a ms Sql database on a goDaddy server from the application.

I am trying to understand the connection string and its total properties.

here is what I think should be in my web.config file

<

add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;

Database=DB_XX10;

User ID=myID;

Password=myypassword;

Trusted_Connection=False" providerName="System.Data.SqlClient"

/>

<remove name="LocalSqlServer"/>

can someone please tell me where I am going wrong, Thanks for your help....

If you search these forums for "GoDaddy" you will find that a lot of people have had problems with them. One being, that Go Daddy does not allow remote SQL Server connections.|||

Do you think it might be possible that somene would share their web.config that would overcome the problem?

Am I asking too much to think that I could develop my site locally and then have some reasonable way of uploading the web site AND update the database on the GODaddy ms sql server with the new data etc.

I am really looking for an overview of the process.

Thanks for your response......

|||You can NOT use a GoDaddy SQL Server from any server except their own, that is how they have things configured. It isn't about the Connection String.|||

So that means I can not access The GODADDY server from my local development enviroment?

SO to set up a development enviroment I would have to update the database directly as well as the web site it self. Is there a overview of such an enviroment available on the net AnyWhere?

Thanks for your quick response

|||

krisfr:

So that means I can not access The GODADDY server from my local development enviroment?

Pretty much. Can't do it. As for your other question, I am not sure. Google can help though.

|||Can I set up the web.config to look for the database locally first, and if it can not find the database then look for the GoDaddy datadase?|||Not really, no. What you can do is setup two connection strings, one for local and another for GoDaddy, and switch them out.

connection strings

I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.

I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.

My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.

I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what???

Can't you just change the default schema of the user you use to login with through asp.net to the new schema?

|||

I'm not sure I understand what you are saying when you say change the schema of the user. I'm pretty sure the hosting account accesses via the user ASPNET via IIS and I also assume that Visual Web Developer 2005 accesses via my user account on the development machine, but I don't understand "user schema". Are we referring to access privileges?

|||

No I'm talking about the schema. When you say that you have to write owner.tablename to access a table that means that that table is in the schema called owner, which is also the default schema for the user with the same name. Normally the default schema for most database objects is dbo. So when you write a select statement like "select * from sometable" it's really the same as writing "select * from dbo.sometable". In sql server you have the option to change the default schema from dbo to something else. Say that all the database objects you want to work on are in a schema called "aspnet". If the default schema of the user you want to access these objects with is not "aspnet", you would have to write all your queries like "select * from aspnet.sometable". However if you change the default schema for that user to "aspnet" you can now write "select * from sometable" with the same result. The way to change schema is to open the properties sheet for the user and change the default schema. It's right there on the main page.

Hope it helps

|||

I tried

CREATE SCHEMA AUTHORIZATION usernamefromisp

GRANT ALL TO usernamefromisp

then changed to point to the restored production database and got the same error message that objects (tables) do not exist.

|||

Hi,

From your description, it seems that you are not able to select the tables in your database which restored from a backup file, right?

As you have mentioned, if you added the database owner in your select statement, it works well. So the cause of the problem is that the database(all datatables) owner is not dbo which is the default owner in your database. So while you are writing select statements without database owner, the system regard it as dbo.Object by default.

In your case, the system can't find the dbo.YourObject since the owner of YourObject is other than dbo. Now you have two workarounds, first, just write the select statement with the object owner. The second way is to change the object owner to dbo.

For how to change, you may refer the following link:
http://msdn2.microsoft.com/en-us/library/ms190909.aspx

Thanks.

|||

Thanks,

Your reply didn't quite fix the problem, but put me on the right track. I didn't give you enough definition because it was not just the database ownership at issue, but also many of the individual tables within the database. I used the sp_changeobjectowner on each of them and the problem cleared.

Thank you for your help.

Connection String options list?

Can anyone point me to a comprehensive list of the options available in connection strings? I know that a lot of the options are able to be set elsewhere, but I'd like to have a complete listing for interests sake.

Thankswww.connectionstrings.com

Also, the docs included with the .NET Framework include all possible options.

Connection string

I am connecting to a SQL Express database from Excel 2K.
I have three connection strings examples. The first two work however the
third was the one I wanted to use as I do not want to specify a local db.
These two work fine:
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=(local);INITIAL CATALOG=IPS;
Trusted_Connection=yes;"
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=(local);INITIAL CATALOG=IPS;
INTEGRATED SECURITY=sspi;"
This one doesn't which I got from http://www.connectionstrings.com/ I like
it as it is simple and easily understood.
strConn = " Server=dimension9150;Database=IPS;Truste
d_Connection=yes;"
The server name is dimension9150 as is verified in the following:
C:\>sqlcmd -S dimension9150
1> SELECT name FROM sys.databases
2> GO
name
----
--
---
master
tempdb
model
msdb
IPS
(5 rows affected)
1> exit
C:\>
Thanks in advance...Hi,
Yuu can use all of the above mentioned, the (local) can be exchanged by
the servername you want to work on like:
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=Dimension9150;INITIAL
CATALOG=IPS;
INTEGRATED SECURITY=sspi;"
(local) is only a pointer like "." or localhost to your current machine
you are connecting from. if you want to use another instance than the
default one, you have to additionally specify the instancename within
the servername, something like this: servername\instancename if you are
using a different port number than the original one,you can use
servername\instancename,Portnumber.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Connection string

I am connecting to a SQL Express database from Excel 2K.
I have three connection strings examples. The first two work however the
third was the one I wanted to use as I do not want to specify a local db.
These two work fine:
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=(local);INITIAL CATALOG=IPS;
Trusted_Connection=yes;"
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=(local);INITIAL CATALOG=IPS;
INTEGRATED SECURITY=sspi;"
This one doesn't which I got from http://www.connectionstrings.com/ I like
it as it is simple and easily understood.
strConn = "Server=dimension9150;Database=IPS;Trusted_Connect ion=yes;"
The server name is dimension9150 as is verified in the following:
C:\>sqlcmd -S dimension9150
1> SELECT name FROM sys.databases
2> GO
name
master
tempdb
model
msdb
IPS
(5 rows affected)
1> exit
C:\>
Thanks in advance...
Hi,
Yuu can use all of the above mentioned, the (local) can be exchanged by
the servername you want to work on like:
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=Dimension9150;INITIAL
CATALOG=IPS;
INTEGRATED SECURITY=sspi;"
(local) is only a pointer like "." or localhost to your current machine
you are connecting from. if you want to use another instance than the
default one, you have to additionally specify the instancename within
the servername, something like this: servername\instancename if you are
using a different port number than the original one,you can use
servername\instancename,Portnumber.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de