ETL Package Problem

I'm going through the Integration tutorial, Lesson 1: Creating a Simple ETL Package. Everything goes fine until I run the package. I get an error about inserting Null in the CurrencyKey field which is set to not null. The flat file looks okay.

Any help would be appreciated.

SSIS package "Lesson 1.dtsx" starting.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Extract Sample Currency Data, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Extract Sample Currency Data, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has started.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Currency Key [30]: component "Lookup Currency Key" (30) has cached 105 rows.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Date Key [124]: component "Lookup Date Key" (124) has cached 992 rows.
Information: 0x4004300C at Extract Sample Currency Data, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Extract Sample Currency Data, Extract Sample Currency Data [1]: The total number of data rows processed for file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" is 1097.
Information: 0x402090DF at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has started.
Error: 0xC0202009 at Extract Sample Currency Data, Sample OLE DB Destination [158]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'CurrencyKey', table 'AdventureWorksDW.dbo.FactCurrencyRate'; column does not allow nulls. INSERT fails.".
Information: 0x402090E0 at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Extract Sample Currency Data, DTS.Pipeline: The ProcessInput method on component "Sample OLE DB Destination" (158) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Extract Sample Currency Data, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Information: 0x40043008 at Extract Sample Currency Data, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has ended.
Information: 0x40043009 at Extract Sample Currency Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Extract Sample Currency Data, DTS.Pipeline: "component "Sample OLE DB Destination" (158)" wrote 1097 rows.
Task failed: Extract Sample Currency Data
Warning: 0x80019002 at Lesson 1: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Lesson 1.dtsx" finished: Failure.
The program '[3372] Lesson 1.dtsx: DTS' has exited with code 0 (0x0).

CurrencyKey Column is set to Not Null means, it cannot take Null values.

If I remember right, CurrencyKey Column of the AdventureWorksDW database is a Primary key.

Thanks,

S Suresh

|||Then there is a problem with the tutorial. Do you know how to fix the problem?
|||

Hi kinny_k,

I was just wondering if you ever got an answer to this problem with the SSIS ETL tutorial. I've run into the same issue and I've checked and rechecked my steps and can not see anything wrong.

If you have the solution/workaround I'd appreciate the input!

-mayalu

|||

Did you review if any of the mapping in your destination componnet is missing?

Rafael Salas

|||

I had a problem with this too. It turns out that the Flat File dates are in a MM/DD/YYYY format and since my default is DD/MM/YYYY most dates aren't correctly identified.

To resolve this:

1. In the ‘Flat File Connection Manager’, changed the CurrencyDate DataType to string [DT_STR].

2. In the ‘Data Flow’ diagram, a Warning displays on the ‘Extract Sample Currency Data’ dataflow.Double click to open it. A message is displayed warning that the format has changed, click Yes and when the ‘Flat File Source Editor’ is displayed, click OK to close.

3. In the Date Lookup Transformation Editor, Reference Table tab, select 'Use results of an SQL query' and paste in:

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR) + '/' +
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 0:00' USFormatDate
FROM dbo.DimTime

Check to output looks OK by clicking the preview button.

4. In the Date Lookup Transformation Editor, Columns tab, link the CurrencyDate to the new USFormatDate field.

I'd imagine there's a nicer way of fixing this but I'm not at all familiar with SSIS yet.

|||

Hi Tim,

Thanks for the input. I tried it and all goes well until the last step where I try to map the CurrencyDate to the new USFormatDate field where I get a data type mismatch. I guess there's something else peculiar to my setup.

At least I know where to look closer!

-Elsie

|||

Elsie,

Hum, I think I'd check the first step again. You have to change the flat file Currency Date to be a string otherwise the comparison wont work and you’ll get a type mismatch. Other than that, I'm afraid I don't know.

You could always change the FactCurrencyRate table definition to allow Null in the TimeKey field so you don't get the error. In retrospect I should have done that as it’s a lot quicker to get the lesson working.

Tim

|||It seems that the only solution is to use SSIS in a db instance with English(US) as default language.
At least, for db instances with non-English default languages (I have Russian one) nothing else works
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1|||

In both step 9's of the add lookup transformation, be sure to click on the box to select the(CurrencyKey) lookup value. You should have a checkbox before moving on. Then you can pam this lookup to the column in the later step.

Enjoy!

|||I am sure I verifшув that (and everything else) dozens of times,
BTW it does not address the error and the problem (incompatible datetime fields)|||

To get this fully working, following steps are required:

1. In the Sample Flat File Source Data connection manager, change CurrencyDate Data Type to String [DT_STR]

2. In the data flow task editor, double click data Flow souce 'Extract Sample Currency Data' then accept the warning

3. In Look Up Time Key task, under Reference Table tab, choose use Results of an SQL query, then use the following SQL statement, which I modified from an early post,

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR)+ '/'
+
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 00:00:00' USFormatDate
FROM dbo.DimTime

4. In Look Up Time Key task, Link Currency date from Available Input Columns to USFormatDate in Available Look UP Columns.

5. Make sure you link the output of the look up to the destination columns.

Good luck,

Celine

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

|||http://www.codeplex.com/MSFTDBProdSamples

ETL Package Problem

I'm going through the Integration tutorial, Lesson 1: Creating a Simple ETL Package. Everything goes fine until I run the package. I get an error about inserting Null in the CurrencyKey field which is set to not null. The flat file looks okay.

Any help would be appreciated.

SSIS package "Lesson 1.dtsx" starting.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Extract Sample Currency Data, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Extract Sample Currency Data, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has started.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Currency Key [30]: component "Lookup Currency Key" (30) has cached 105 rows.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Date Key [124]: component "Lookup Date Key" (124) has cached 992 rows.
Information: 0x4004300C at Extract Sample Currency Data, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Extract Sample Currency Data, Extract Sample Currency Data [1]: The total number of data rows processed for file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" is 1097.
Information: 0x402090DF at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has started.
Error: 0xC0202009 at Extract Sample Currency Data, Sample OLE DB Destination [158]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'CurrencyKey', table 'AdventureWorksDW.dbo.FactCurrencyRate'; column does not allow nulls. INSERT fails.".
Information: 0x402090E0 at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Extract Sample Currency Data, DTS.Pipeline: The ProcessInput method on component "Sample OLE DB Destination" (158) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Extract Sample Currency Data, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Information: 0x40043008 at Extract Sample Currency Data, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has ended.
Information: 0x40043009 at Extract Sample Currency Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Extract Sample Currency Data, DTS.Pipeline: "component "Sample OLE DB Destination" (158)" wrote 1097 rows.
Task failed: Extract Sample Currency Data
Warning: 0x80019002 at Lesson 1: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Lesson 1.dtsx" finished: Failure.
The program '[3372] Lesson 1.dtsx: DTS' has exited with code 0 (0x0).

CurrencyKey Column is set to Not Null means, it cannot take Null values.

If I remember right, CurrencyKey Column of the AdventureWorksDW database is a Primary key.

Thanks,

S Suresh

|||Then there is a problem with the tutorial. Do you know how to fix the problem?
|||

Hi kinny_k,

I was just wondering if you ever got an answer to this problem with the SSIS ETL tutorial. I've run into the same issue and I've checked and rechecked my steps and can not see anything wrong.

If you have the solution/workaround I'd appreciate the input!

-mayalu

|||

Did you review if any of the mapping in your destination componnet is missing?

Rafael Salas

|||

I had a problem with this too. It turns out that the Flat File dates are in a MM/DD/YYYY format and since my default is DD/MM/YYYY most dates aren't correctly identified.

To resolve this:

1. In the ‘Flat File Connection Manager’, changed the CurrencyDate DataType to string [DT_STR].

2. In the ‘Data Flow’ diagram, a Warning displays on the ‘Extract Sample Currency Data’ dataflow.Double click to open it. A message is displayed warning that the format has changed, click Yes and when the ‘Flat File Source Editor’ is displayed, click OK to close.

3. In the Date Lookup Transformation Editor, Reference Table tab, select 'Use results of an SQL query' and paste in:

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR) + '/' +
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 0:00' USFormatDate
FROM dbo.DimTime

Check to output looks OK by clicking the preview button.

4. In the Date Lookup Transformation Editor, Columns tab, link the CurrencyDate to the new USFormatDate field.

I'd imagine there's a nicer way of fixing this but I'm not at all familiar with SSIS yet.

|||

Hi Tim,

Thanks for the input. I tried it and all goes well until the last step where I try to map the CurrencyDate to the new USFormatDate field where I get a data type mismatch. I guess there's something else peculiar to my setup.

At least I know where to look closer!

-Elsie

|||

Elsie,

Hum, I think I'd check the first step again. You have to change the flat file Currency Date to be a string otherwise the comparison wont work and you’ll get a type mismatch. Other than that, I'm afraid I don't know.

You could always change the FactCurrencyRate table definition to allow Null in the TimeKey field so you don't get the error. In retrospect I should have done that as it’s a lot quicker to get the lesson working.

Tim

|||It seems that the only solution is to use SSIS in a db instance with English(US) as default language.
At least, for db instances with non-English default languages (I have Russian one) nothing else works
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1|||

In both step 9's of the add lookup transformation, be sure to click on the box to select the(CurrencyKey) lookup value. You should have a checkbox before moving on. Then you can pam this lookup to the column in the later step.

Enjoy!

|||I am sure I verifшув that (and everything else) dozens of times,
BTW it does not address the error and the problem (incompatible datetime fields)|||

To get this fully working, following steps are required:

1. In the Sample Flat File Source Data connection manager, change CurrencyDate Data Type to String [DT_STR]

2. In the data flow task editor, double click data Flow souce 'Extract Sample Currency Data' then accept the warning

3. In Look Up Time Key task, under Reference Table tab, choose use Results of an SQL query, then use the following SQL statement, which I modified from an early post,

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR)+ '/'
+
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 00:00:00' USFormatDate
FROM dbo.DimTime

4. In Look Up Time Key task, Link Currency date from Available Input Columns to USFormatDate in Available Look UP Columns.

5. Make sure you link the output of the look up to the destination columns.

Good luck,

Celine

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

|||http://www.codeplex.com/MSFTDBProdSamples

Estimated Execution Plan Fails, XML Error?

Good afternoon, all. Whenever I try to use Query | Display Estimated
Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
message:
Error processing execution plan results. The error message is:
There is an error in XML document (1, 501).
There is an unclosed literal string. Line 1, position 501.
The only suggestion I've heard of is to instal SQL SPs, but that didn't
help. This is SQL 2K5 running on Win2K3. I don't believe there's any
strangeness like odd characters in instance names, and all language settings
are default. I've seen this before on another server, but Google has nothing.
Any ideas? Thanks!
I think I heard that the XML used for this can not handle some unusual
object names, but at best that is a long shot.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Aug 2007 13:32:02 -0700, JonOfAllTrades
<JonOfAllTrades@.discussions.microsoft.com> wrote:

> Good afternoon, all. Whenever I try to use Query | Display Estimated
>Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
>message:
>Error processing execution plan results. The error message is:
>There is an error in XML document (1, 501).
>There is an unclosed literal string. Line 1, position 501.
> The only suggestion I've heard of is to instal SQL SPs, but that didn't
>help. This is SQL 2K5 running on Win2K3. I don't believe there's any
>strangeness like odd characters in instance names, and all language settings
>are default. I've seen this before on another server, but Google has nothing.
> Any ideas? Thanks!

Establishing connection to SQL 2005 Server Express

Using localhost (127.0.0.1) an error occurred while establishing a connection to SQL Server 2005.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

Error Number: 10061
Event Source: MSSQLServer

Message text: Cannot connect to 127.0.0.1.

Any ideas?

I have just tried this idea and it worked ! ! !

- Removed SQL Native Client through Add or Remove programmes

- Reinstalled SQLServer 2005 Express

- Used .\sqlexpress as server name

- Clicked Connect

and I got connected to the server ! ! !

Thanks.

|||I was having trouble connecting to the sql server from visual studio.
Then I realized that "server name" actually means "sql server name".
I'll consider it a bug.

The drop down list contains server names, such as the name of my local computer as well as other servers on my LAN. Since the list contains server names, and the label says server name, you'd think they mean server name.... but they don't; they mean sql server name.

It's easiest to use ".\" as the server name for localhost, and "sqlexpress" as the name of the sql server instance. So for server name, enter ".\sqlexpress".

By the way, visual studio 2005 is great. What's gotten into microsoft? Ever since windows xp, they seem to be getting their act together... taking time to make things right (e.g. delaying vista, adding visual queues for docking so you don't have to guess where things are gonna land when you drop them). Good job!
|||thanks for posting this! was having the same problems but now it worked in a few seconds!
|||Thank you! Thank You! This is most helpfull. I won't say how much time I spent trying to reconnect to my server after upgrading SQL Express to SP2.

Great formum. Hopefully I'll know enough to give back some day.
|||

Another greatful user of your post. Allow me to add that my database name ended up being msdb

I had been trying to use Northwind

Establishing connection to SQL 2005 Server Express

Using localhost (127.0.0.1) an error occurred while establishing a connection to SQL Server 2005.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

Error Number: 10061
Event Source: MSSQLServer

Message text: Cannot connect to 127.0.0.1.

Any ideas?

I have just tried this idea and it worked ! ! !

- Removed SQL Native Client through Add or Remove programmes

- Reinstalled SQLServer 2005 Express

- Used .\sqlexpress as server name

- Clicked Connect

and I got connected to the server ! ! !

Thanks.

|||I was having trouble connecting to the sql server from visual studio.
Then I realized that "server name" actually means "sql server name".
I'll consider it a bug.

The drop down list contains server names, such as the name of my local computer as well as other servers on my LAN. Since the list contains server names, and the label says server name, you'd think they mean server name.... but they don't; they mean sql server name.

It's easiest to use ".\" as the server name for localhost, and "sqlexpress" as the name of the sql server instance. So for server name, enter ".\sqlexpress".

By the way, visual studio 2005 is great. What's gotten into microsoft? Ever since windows xp, they seem to be getting their act together... taking time to make things right (e.g. delaying vista, adding visual queues for docking so you don't have to guess where things are gonna land when you drop them). Good job!|||thanks for posting this! was having the same problems but now it worked in a few seconds!|||Thank you! Thank You! This is most helpfull. I won't say how much time I spent trying to reconnect to my server after upgrading SQL Express to SP2.

Great formum. Hopefully I'll know enough to give back some day.|||

Another greatful user of your post. Allow me to add that my database name ended up being msdb

I had been trying to use Northwind

Establishing connection to SQL 2005 Server Express

Using localhost (127.0.0.1) an error occurred while establishing a connection to SQL Server 2005.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

Error Number: 10061
Event Source: MSSQLServer

Message text: Cannot connect to 127.0.0.1.

Any ideas?

I have just tried this idea and it worked ! ! !

- Removed SQL Native Client through Add or Remove programmes

- Reinstalled SQLServer 2005 Express

- Used .\sqlexpress as server name

- Clicked Connect

and I got connected to the server ! ! !

Thanks.

|||I was having trouble connecting to the sql server from visual studio.
Then I realized that "server name" actually means "sql server name".
I'll consider it a bug.

The drop down list contains server names, such as the name of my local computer as well as other servers on my LAN. Since the list contains server names, and the label says server name, you'd think they mean server name.... but they don't; they mean sql server name.

It's easiest to use ".\" as the server name for localhost, and "sqlexpress" as the name of the sql server instance. So for server name, enter ".\sqlexpress".

By the way, visual studio 2005 is great. What's gotten into microsoft? Ever since windows xp, they seem to be getting their act together... taking time to make things right (e.g. delaying vista, adding visual queues for docking so you don't have to guess where things are gonna land when you drop them). Good job!
|||thanks for posting this! was having the same problems but now it worked in a few seconds!
|||Thank you! Thank You! This is most helpfull. I won't say how much time I spent trying to reconnect to my server after upgrading SQL Express to SP2.

Great formum. Hopefully I'll know enough to give back some day.
|||

Another greatful user of your post. Allow me to add that my database name ended up being msdb

I had been trying to use Northwind

|||Scripting as new Table with insert data isn't supported in SQL Server. I found a C# program that does this; http://www.eggheadcafe.com/articles/20040913.asp

Just needed the server name. Worked beautifully.

Thank you Thank you.

Fred

sql

Establishing connection to SQL 2005 Server Express

Using localhost (127.0.0.1) an error occurred while establishing a connection to SQL Server 2005.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

Error Number: 10061
Event Source: MSSQLServer

Message text: Cannot connect to 127.0.0.1.

Any ideas?

I have just tried this idea and it worked ! ! !

- Removed SQL Native Client through Add or Remove programmes

- Reinstalled SQLServer 2005 Express

- Used .\sqlexpress as server name

- Clicked Connect

and I got connected to the server ! ! !

Thanks.

|||I was having trouble connecting to the sql server from visual studio.
Then I realized that "server name" actually means "sql server name".
I'll consider it a bug.

The drop down list contains server names, such as the name of my local computer as well as other servers on my LAN. Since the list contains server names, and the label says server name, you'd think they mean server name.... but they don't; they mean sql server name.

It's easiest to use ".\" as the server name for localhost, and "sqlexpress" as the name of the sql server instance. So for server name, enter ".\sqlexpress".

By the way, visual studio 2005 is great. What's gotten into microsoft? Ever since windows xp, they seem to be getting their act together... taking time to make things right (e.g. delaying vista, adding visual queues for docking so you don't have to guess where things are gonna land when you drop them). Good job!|||thanks for posting this! was having the same problems but now it worked in a few seconds!|||Thank you! Thank You! This is most helpfull. I won't say how much time I spent trying to reconnect to my server after upgrading SQL Express to SP2.

Great formum. Hopefully I'll know enough to give back some day.|||

Another greatful user of your post. Allow me to add that my database name ended up being msdb

I had been trying to use Northwind

Establishing connection to SQL 2005 Server Express

Using localhost (127.0.0.1) an error occurred while establishing a connection to SQL Server 2005.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

Error Number: 10061
Event Source: MSSQLServer

Message text: Cannot connect to 127.0.0.1.

Any ideas?

I have just tried this idea and it worked ! ! !

- Removed SQL Native Client through Add or Remove programmes

- Reinstalled SQLServer 2005 Express

- Used .\sqlexpress as server name

- Clicked Connect

and I got connected to the server ! ! !

Thanks.

|||I was having trouble connecting to the sql server from visual studio.
Then I realized that "server name" actually means "sql server name".
I'll consider it a bug.

The drop down list contains server names, such as the name of my local computer as well as other servers on my LAN. Since the list contains server names, and the label says server name, you'd think they mean server name.... but they don't; they mean sql server name.

It's easiest to use ".\" as the server name for localhost, and "sqlexpress" as the name of the sql server instance. So for server name, enter ".\sqlexpress".

By the way, visual studio 2005 is great. What's gotten into microsoft? Ever since windows xp, they seem to be getting their act together... taking time to make things right (e.g. delaying vista, adding visual queues for docking so you don't have to guess where things are gonna land when you drop them). Good job!
|||thanks for posting this! was having the same problems but now it worked in a few seconds!
|||Thank you! Thank You! This is most helpfull. I won't say how much time I spent trying to reconnect to my server after upgrading SQL Express to SP2.

Great formum. Hopefully I'll know enough to give back some day.
|||

Another greatful user of your post. Allow me to add that my database name ended up being msdb

I had been trying to use Northwind

Establish SQL Connection

Hi! (my first post)

I'm ASP.NET newbie and i´ve got anSQL connection error, i know why, ididn´t establish a connection...
my questions are: how do i establish the connection state?, and where in my project should i do it?

I added the connection section to my web.config, is that enough to establish a connection?

<connectionStrings>
<add name="buscomConnectionString" connectionString="Data Source=myserver.net;Initial Catalog=mycatalog;Persist Security Info=True;User ID=myuser;Password=mypassword"
providerName="System.Data.SqlClient" />
</connectionStrings>

The error i get is:

Object reference not set to an instance of an object.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 2443:
Line 2444://If connection is not open then attempt to open it.
Line 2445:if (connection.State == System.Data.ConnectionState.Closed)
Line 2446:{
Line 2447:try

Source File: d:\User\buscom\App_Code\PHP.cs Line: 2445

Thanks and cheers!

Bill Murphy (litio.net)

Welcome to ASP.NET Forums The section you added in web.config is just to store a conneciton string which then can be used in your application. You can create a connection using the connection string like:

SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["Access"].ConnectionString.ToString());

Then use Open method to open it:

Conn.Open();

Or you can create a SqlCommand with the connection:

SqlCommand cmd= new SqlCommand("SELECT * FROM sysobjects",conn);

Or do other things as you like. To learn various database connections in ASP .NET, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms254937.aspx

However I suggest you use 'using' block to embed database operations so that the connection will be automatically disposed, so to avoid leaving unuseful open connections. That is:

using (SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["Access"].ConnectionString.ToString()))

{//add your code

}

To learn more about connection Close/Dispose in .NET, you can take a look at this article:

http://codebetter.com/blogs/sahil.malik/archive/2004/12/31/40036.aspx

|||

That was what I was looking for!! just didn´t seem to find it :D
Both link where very helpfull.

Thanks a lot!

|||

sorry, buy looking into that didn´t work out. i have to reopen my question...

I am using the PHPMigration assistant, so i went ahead and generated the connect command and got the following:

try {PHP.MSSQLSupport.Connect(host, user, password); }

catch(System.Exception) {Response.Write("Error, no server"); }

try {PHP.MSSQLSupport.ChangeDatabas
________________________________________________________

This is a duplicate of your other post:http://forums.asp.net/thread/1387576.aspx

Duplicate posts are not permitted on theASP.NET Forums. As a result, the duplicate post has been deleted. We trust however that you will receive helpful replies to your other post, linked above.

Please be aware that theASP.NET Forums are moderated. This means that there will always be a delay between submitting a message and seeing it appear on the forums. There is no need to submit a message multiple times.

Thanks,
ASP.NET Forums team

",0]);//-->e(bbdd);}

catch (System.Exception) { Response.Write( bbdd + " not found"); }

Inserted it into my page and the error above persists.

i am passing a null to PHP.MSSQLSupport.Query, that should reopen the last connection, so i guessI'm just not opening the connection as i should...

How should iestablish the connection with the PHPMigration assistant generatedcode? should i remove the info on the web. config or the above lines?

should i mention my connection string name ( buscomConnectionString )anywhere in the code? or should the above code be enough?

if the databese isn´t connecting properly, would i get a different error? (maybe Error, no server as i specified??)

|||This is what i meant to post:

sorry, buy looking into that didn´t work out. i have to reopen my question...

I am using the PHPMigration assistant, so i went ahead and generated the connect command and got the following:

try {PHP.MSSQLSupport.Connect(host, user, password); }

catch(System.Exception) {Response.Write("Error, no server"); }

try {PHP.MSSQLSupport.ChangeDatabase(bbdd);}

catch (System.Exception) { Response.Write( bbdd + " not found"); }

Inserted it into my page and the error above persists.

i am passing a null to PHP.MSSQLSupport.Query, that should reopen the last connection, so i guessI'm just not opening the connection as i should...

How should iestablish the connection with the PHPMigration assistant generatedcode? should i remove the info on the web. config or the above lines?

should i mention my connection string name ( buscomConnectionString )anywhere in the code? or should the above code be enough?

if the databese isn´t connecting properly, would i get a different error? (maybe Error, no server as i specified??)

escaping single quotes

Hi,

I need to have an varchar value with single quotes. For eg: the below code throws compilation error.

Declare @.val VARCHAR(20)
SELECT @.val = ''+name+''
print @.val

Error: Invalid column 'name'

I want to print name enclosed with single quotes. Please guide me.

Regards,
SamDeclare @.val VARCHAR(20)
SELECT @.val = 'Kaiowas'
PRINT @.VAL

EDIT (added): SET @.VAL = @.VAL + '''Kaiowas'''
EDIT (added): PRINT @.VAL

SET @.VAL = @.VAL + '''' + @.VAL + ''''
PRINT @.VAL

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escape clause causes error in lookup modified sql statement

I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'
ESCAPE '_'

Is this is a bug? Any help will be greatly appreciated.

Thanks

Akin
The escape clause shouldn't be affecting the parameter usage if the SQL works without it. The lookup doesn't parse the SQL, so I suspect the problem is with the provider. Essentially, the lookup asks the provider to prepare the command and then to derive parameter information. The provider is failing in one of the above steps (probably the prepare step). I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

Errr VSS service

Problem NTBACKUP and Job SQL is in parallel carried out
Sqllib error: Database TRADE is not simple.Nac,
Please provide more information. What problem are you having, give exact
error messages. What SQL Job? What does it do? What backup program are
you using, etc.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nac wrote:
> Problem NTBACKUP and Job SQL is in parallel carried out
> Sqllib error: Database TRADE is not simple.|||At 01.00 AM preservation of documents under the schedule by utility NTBACKUP
utility (the standard utility in windows server 2003) is carried out. Thus
the mistake is given out:
Sqllib error: Database PRODTRADE is not simple.
Preservation of database SQL was in 11.00PM on the same server under the sch
edule (carries out SQL server agent).

Errr VSS service

Problem NTBACKUP and Job SQL is in parallel carried out
Sqllib error: Database TRADE is not simple.
Nac,
Please provide more information. What problem are you having, give exact
error messages. What SQL Job? What does it do? What backup program are
you using, etc.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nac wrote:
> Problem NTBACKUP and Job SQL is in parallel carried out
> Sqllib error: Database TRADE is not simple.
|||At 01.00 AM preservation of documents under the schedule by utility NTBACKUP utility (the standard utility in windows server 2003) is carried out. Thus the mistake is given out:
Sqllib error: Database PRODTRADE is not simple.
Preservation of database SQL was in 11.00PM on the same server under the schedule (carries out SQL server agent).
sql

Errr VSS service

Problem NTBACKUP and Job SQL is in parallel carried out
Sqllib error: Database TRADE is not simple.Nac,
Please provide more information. What problem are you having, give exact
error messages. What SQL Job? What does it do? What backup program are
you using, etc.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nac wrote:
> Problem NTBACKUP and Job SQL is in parallel carried out
> Sqllib error: Database TRADE is not simple.|||At 01.00 AM preservation of documents under the schedule by utility NTBACKUP utility (the standard utility in windows server 2003) is carried out. Thus the mistake is given out
Sqllib error: Database PRODTRADE is not simple.
Preservation of database SQL was in 11.00PM on the same server under the schedule (carries out SQL server agent).

Errot :can not connect to server

i am getting error to connect C#.net with sql server. how to solve the problem?

frstly check u hv written proper code and given proper path to ur database..

i hv a copy of it. see this.. this may help

using System.Data.SqlClient;

SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");

try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}

myCommand.Connection = myConnection;

myCommand.CommandText = "ur query"

myCommand.ExecuteNonQuery();

try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("select * from table",
myConnection);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
Console.WriteLine(myReader["Column1"].ToString());
Console.WriteLine(myReader["Column2"].ToString());
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
Bala
Don't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:
>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>sql

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error-Unable to get report parameters:Unable to access Web Service

Hi - In our RS + SP1 installation on Windows Server 2003, we recently began
to get the following error:
"Unable to get report parameters: Unable to access Web Service: The request
failed with HTTP status 403: Forbidden."
We are calling Render() in code using an account which has admin rights to
the RS box, is not locked out, etc. Event logs show no failures and the RS
logs are as useless as ever. I see no access denieds from Windows auditing
either.
Thoughts?
thanks muchHi,
Thanks for your posting!
From your descriptions, I understood that you are not able to access Web
Service with the error message "Unable to get report parameters: Unable to
access Web Service: The request failed with HTTP status 403: Forbidden."
Have I understood you? Correct me if I was wrong.
Based on my knowledge, it seems that "anonymous" for Reportserver virual
folder is enabled. Please try the following steps and let me know whether
it works
1. Right click ReportServer in IIS->Properties->Directory Security
2. Make sure "Intergrated Windows authentication" is checked and "enable
anonymouse access" is unchecked.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael, I looked at my anonymous setting but it was set properly.
By chance, I tried to execute http://server/reportserver/reportservice.asmx
and it said that there were no permissions to execute scripts in this virtual
root. Sure enough, I looked at the ReportServer virtual root config and the
Execute permissions were improperly set. Not sure how changed, but once I
put it back to "Scripts and Executables" I was good.
Thanks much
"Michael Cheng [MSFT]" wrote:
> Hi,
> Thanks for your posting!
> From your descriptions, I understood that you are not able to access Web
> Service with the error message "Unable to get report parameters: Unable to
> access Web Service: The request failed with HTTP status 403: Forbidden."
> Have I understood you? Correct me if I was wrong.
> Based on my knowledge, it seems that "anonymous" for Reportserver virual
> folder is enabled. Please try the following steps and let me know whether
> it works
> 1. Right click ReportServer in IIS->Properties->Directory Security
> 2. Make sure "Intergrated Windows authentication" is checked and "enable
> anonymouse access" is unchecked.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hi,
Thanks for your prompt updates!
It's great to hear that it did turn out to be an authority issue and you
yourself have resolved it.
Thank you for your patience and corporation. If you have any questions or
concerns on SQL Server, don't hesitate to let me know. We are always here
to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql