ASP to SQL Connection Prob, Same Server, IIS

Yes, it should be easy...

I have a single box hosing IIS and SQL Server 2000. In my ASP script I've
tried using the following connection strings to attempt to connect to no
avail:

"Provider=SQLOLEDB;Server=servername\instancename,1 433;Database=TEST;network=DBMSSOCN;uid=TEST;pwd=TE ST"
"Provider=SQLOLEDB;Server=local IP
address\instancename,1433;Database=TEST;network=DB MSSOCN;uid=TEST;pwd=TEST"

"Provider=SQLOLEDB;Server=external IP
address\servername\instancename;Database=TEST;netw ork=DBMSSOCN;uid=TEST;pwd=TEST"

Getting error:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found."

SQL Server is set up for SQL and Windows authentication. Server utility is
configured for both named pipes and TCP/IP. Port is set to 1433.

I can connect to the DB using Query Analyzer both from the local server
itself AND from a remote laptop over the internet.

So, what am I missing???

Thanks in advance,

ChadChad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> SQL Server is set up for SQL and Windows authentication. Server utility is
> configured for both named pipes and TCP/IP. Port is set to 1433.

But you have a named instance. Yes, you can of course have a named
instance on port 1433, but normally this is where you have the unnamed
instance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. But if you can have one, than do you have any other ideas on why it
would not connect?

I have this same set up on my development laptop and it connects fine,
except that my web site on the laptop is served up from a virtual directory.
On the home development box, the web site is located under the
InetPub\wwwroot folder. Not sure if this would change the "perspective" that
the connection call is coming from and hence would require a different
syntax...

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960E3D672D9AYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> SQL Server is set up for SQL and Windows authentication. Server utility
>> is
>> configured for both named pipes and TCP/IP. Port is set to 1433.
> But you have a named instance. Yes, you can of course have a named
> instance on port 1433, but normally this is where you have the unnamed
> instance.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> OK. But if you can have one, than do you have any other ideas on why it
> would not connect?

I asked my question mainly as incentive for you to double-check.

> I have this same set up on my development laptop and it connects fine,
> except that my web site on the laptop is served up from a virtual
> directory. On the home development box, the web site is located under
> the InetPub\wwwroot folder. Not sure if this would change the
> "perspective" that the connection call is coming from and hence would
> require a different syntax...

I don't really see why either. Then again, I'm completely ignorant about
IIS.

But there is one thing I don't like in your connection string:
network=DBMSSOCN. I believe this library is out-dated. Not that I
think it matters, but remove it.

And, hm, what about removing 1433? After all there is a service to
find the port. And then there is shared memory which does not even need a
port...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

AS400 DB2 connection strings

I am trying to construct a connection string via iSeries OleDb provider. The default list of libraries on the AS400 for my user is fine for production. But in test scenarios the code/data libraries are rolled out through a sequence of test libraries. How do I specify the additional libraries for testing.

I connect fine and can execute sql. the statements like insert into TestLibrary.Table work fine, but the associated trigger code isn't called.

Would it be better to use the .Net Connection set of classes instead. I switched from ODBC to OleDb because of assumed thread safety issues.I don't understand what you're looking for. Can you be more specific? If trigger code isn't called, how is that an SSIS issue?|||Maybe not.

The problem is building a connection string to communicate withe as400 db2. I found other threads in this forum dealing with other issues on this connectivity. The point about the trigger code not executing is because the library on the as400 that contains the trigger code is not part of the default library list for the connecting user.

the question is how to construct the connection string to add additional libraries into the scope of the connection. I was able to do so using ODBC. I wanted to use either the OleDb or the ADO clone iSeries connection stuff to do it as well.|||This link might help: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061900&SiteID=1

AS 400 data being interpreted as unicode strings (DT_WSTR) datatype

I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).

Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.

The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:

Column "BYStOK" cannot convert between unicode and non-unicode string data types.

Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.

Steve Wells

swells wrote:

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Hi Steve,

Those are simply limitations of the Data Reader Src. The data reader source is a managed component that gets data as CLR types, so by the time strings get to the Data Reader Src they are already Unicode. The data reader src component itself does not do data conversion, so it does not allow its output column types to be changed.

You will need to put a Data Conversion transform downstream of the Data Reader Src to convert the DT_WSTR column to DT_STR before inserting in the destination.

Thanks
Mark

|||I haven't worked with an AS400 in about a year and a half so things may have changed with newer releases. That being said, IBMs OLEDB was merely a wrapper around their ODBC drivers and not all of the functionality exists in OLEDB. I remember having problems with scrollable cursors and such with OLEDB. Unless IBM has changed their architecture, OLEDB is really ODBC anyways just with fewer features and added overhead.

Microsoft has their own OLEDB driver for DB2 and you can get it from the here. Since I don't have access to an AS400 anymore, I can't say if it works or whether performance is better. Hit Software also provides very good data access libraries although they do cost quite a bit.|||

It looks like a lot of the trouble I am having between the AS400 and SQL server 2005 using SSIS is due to conversion problems. I did a simple "Hi World" app where I take a table with a NChar, Int, and Decimal fields. I move the data from the 400 to SQL then from SQL to the 400. I use the ADO.net for ODBC for the 400 to SQL and have to convert the NChar fields to DT_STR but the int and Decimal fields move as is. On the way back, I have to convert all the NChar fields to DT_STR and all the numeric fields to DT_NUMERIC. Not fun but it works. Before the upgrade to SQL 2005 I did not have to do any of this and it all just worked. I have over 100 DTS and SSIS packages working every day.

Microsoft is trying to push the problem to IBM saying their driver is not up to the task. That may be so but why did all of this stuff work pre 2005? Isn't it conveniant that MS has a driver but to use it you have to buy the more expensive version of Sql Server. For now we will just scrap the upgrade to SQL 2005 and hope that Microsoft and IBM can work it out. For now it is just not worth the trouble. Guess I can put off buying those licenses at least one more year.

|||

I have the same probleme to transfert data from AS400 to SQL Server 2005 database.

"cannot convert between unicode and non-unicode string data types"

( I dont want to add a Data Conversion Transformation,... too slow ! )

Any solution ?

|||Use a derived column and cast the field to DT_STR (or DT_WSTR is necessary).

It won't be slow.|||

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

|||

Sombre wrote:

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

Try using a derived column to cast the field!!!!!!

(DT_STR,50,1252)[Your_Field] (convert to non-unicode, 50 character length)|||

Edited: In fact my performance problem was about the FastLoad option (Locktable).

Nevertheless, it boring to have to explicitly rechange the data types :(

AS 400 data being interpreted as unicode strings (DT_WSTR) datatype

I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).

Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.

The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:

Column "BYStOK" cannot convert between unicode and non-unicode string data types.

Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.

Steve Wells

swells wrote:

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Hi Steve,

Those are simply limitations of the Data Reader Src. The data reader source is a managed component that gets data as CLR types, so by the time strings get to the Data Reader Src they are already Unicode. The data reader src component itself does not do data conversion, so it does not allow its output column types to be changed.

You will need to put a Data Conversion transform downstream of the Data Reader Src to convert the DT_WSTR column to DT_STR before inserting in the destination.

Thanks
Mark

|||I haven't worked with an AS400 in about a year and a half so things may have changed with newer releases. That being said, IBMs OLEDB was merely a wrapper around their ODBC drivers and not all of the functionality exists in OLEDB. I remember having problems with scrollable cursors and such with OLEDB. Unless IBM has changed their architecture, OLEDB is really ODBC anyways just with fewer features and added overhead.

Microsoft has their own OLEDB driver for DB2 and you can get it from the here. Since I don't have access to an AS400 anymore, I can't say if it works or whether performance is better. Hit Software also provides very good data access libraries although they do cost quite a bit.|||

It looks like a lot of the trouble I am having between the AS400 and SQL server 2005 using SSIS is due to conversion problems. I did a simple "Hi World" app where I take a table with a NChar, Int, and Decimal fields. I move the data from the 400 to SQL then from SQL to the 400. I use the ADO.net for ODBC for the 400 to SQL and have to convert the NChar fields to DT_STR but the int and Decimal fields move as is. On the way back, I have to convert all the NChar fields to DT_STR and all the numeric fields to DT_NUMERIC. Not fun but it works. Before the upgrade to SQL 2005 I did not have to do any of this and it all just worked. I have over 100 DTS and SSIS packages working every day.

Microsoft is trying to push the problem to IBM saying their driver is not up to the task. That may be so but why did all of this stuff work pre 2005? Isn't it conveniant that MS has a driver but to use it you have to buy the more expensive version of Sql Server. For now we will just scrap the upgrade to SQL 2005 and hope that Microsoft and IBM can work it out. For now it is just not worth the trouble. Guess I can put off buying those licenses at least one more year.

|||

I have the same probleme to transfert data from AS400 to SQL Server 2005 database.

"cannot convert between unicode and non-unicode string data types"

( I dont want to add a Data Conversion Transformation,... too slow ! )

Any solution ?

|||Use a derived column and cast the field to DT_STR (or DT_WSTR is necessary).

It won't be slow.|||

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

|||

Sombre wrote:

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

Try using a derived column to cast the field!!!!!!

(DT_STR,50,1252)[Your_Field] (convert to non-unicode, 50 character length)|||

Edited: In fact my performance problem was about the FastLoad option (Locktable).

Nevertheless, it boring to have to explicitly rechange the data types :(

AS 400 data being interpreted as unicode strings (DT_WSTR) datatype

I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).

Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.

The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:

Column "BYStOK" cannot convert between unicode and non-unicode string data types.

Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.

Steve Wells

swells wrote:

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Hi Steve,

Those are simply limitations of the Data Reader Src. The data reader source is a managed component that gets data as CLR types, so by the time strings get to the Data Reader Src they are already Unicode. The data reader src component itself does not do data conversion, so it does not allow its output column types to be changed.

You will need to put a Data Conversion transform downstream of the Data Reader Src to convert the DT_WSTR column to DT_STR before inserting in the destination.

Thanks
Mark

|||I haven't worked with an AS400 in about a year and a half so things may have changed with newer releases. That being said, IBMs OLEDB was merely a wrapper around their ODBC drivers and not all of the functionality exists in OLEDB. I remember having problems with scrollable cursors and such with OLEDB. Unless IBM has changed their architecture, OLEDB is really ODBC anyways just with fewer features and added overhead.

Microsoft has their own OLEDB driver for DB2 and you can get it from the here. Since I don't have access to an AS400 anymore, I can't say if it works or whether performance is better. Hit Software also provides very good data access libraries although they do cost quite a bit.|||

It looks like a lot of the trouble I am having between the AS400 and SQL server 2005 using SSIS is due to conversion problems. I did a simple "Hi World" app where I take a table with a NChar, Int, and Decimal fields. I move the data from the 400 to SQL then from SQL to the 400. I use the ADO.net for ODBC for the 400 to SQL and have to convert the NChar fields to DT_STR but the int and Decimal fields move as is. On the way back, I have to convert all the NChar fields to DT_STR and all the numeric fields to DT_NUMERIC. Not fun but it works. Before the upgrade to SQL 2005 I did not have to do any of this and it all just worked. I have over 100 DTS and SSIS packages working every day.

Microsoft is trying to push the problem to IBM saying their driver is not up to the task. That may be so but why did all of this stuff work pre 2005? Isn't it conveniant that MS has a driver but to use it you have to buy the more expensive version of Sql Server. For now we will just scrap the upgrade to SQL 2005 and hope that Microsoft and IBM can work it out. For now it is just not worth the trouble. Guess I can put off buying those licenses at least one more year.

|||

I have the same probleme to transfert data from AS400 to SQL Server 2005 database.

"cannot convert between unicode and non-unicode string data types"

( I dont want to add a Data Conversion Transformation,... too slow ! )

Any solution ?

|||Use a derived column and cast the field to DT_STR (or DT_WSTR is necessary).

It won't be slow.|||

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

|||

Sombre wrote:

Well...

- old DTS ( AS400 --> SQL2000 ) is working in 1 minute.
- DTSX ( AS400 --> SQL2005 ) doesnt work ( "cannot convert between unicode and non-unicode string data types" )
- DTSX ( AS400 --> SQL2005 using a Data Conversion Transformation * ) is working in 10 minutes !!

Data Conversion Transformation :
ex: 'Field_A' --> 'Copy of Field_A' with Data type = "DT_STR"

Moreover, I have 80 tables with 50 to 80 fields to transfer,... if I must set manualy Conversions for all fields i will spend days and days !

Any solution to change the "DataReader Output (external Columns) (Output Columns)" ?

Try using a derived column to cast the field!!!!!!

(DT_STR,50,1252)[Your_Field] (convert to non-unicode, 50 character length)|||

Edited: In fact my performance problem was about the FastLoad option (Locktable).

Nevertheless, it boring to have to explicitly rechange the data types :(

arrays in tsql?

Hi;

I have been writing a lot of short tsql scripts to fix a lot of tiny
database issues.

I was wondering if a could make an array of strings in tsql that I
could process in a loop, something like

array arrayListOfTablesToProcess = { "orders", "phone",
"complaints"}

for( int i = 0; i < arrayListOfTablesToProcess.length; i++ )
delete from arrayListOfTablesToProcess[i]

Can you do something like this in tsql?

I should probably stop asking all of these questions.

Is there a good book on TSQL alone ( I'm not interested in wizards,
just scripting )...that is short?

SteveThere are no arrays in TSQL. You are thinking in terms of a procedural
solution rather than a set-based solution. In general, you should be able to
use a table and set-based, SELECT/UPDATE/DELETE statements to process
"lists" of data. If you define your problem more specifically someone here
may be able to help with the set-based solution.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

In addition to David's post check out:

http://www.algonet.se/~sommar/arrays-in-sql.html

John

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0310040337.52427942@.posting.google.c om...
> Hi;
> I have been writing a lot of short tsql scripts to fix a lot of tiny
> database issues.
> I was wondering if a could make an array of strings in tsql that I
> could process in a loop, something like
>
> array arrayListOfTablesToProcess = { "orders", "phone",
> "complaints"}
> for( int i = 0; i < arrayListOfTablesToProcess.length; i++ )
> delete from arrayListOfTablesToProcess[i]
>
>
> Can you do something like this in tsql?
> I should probably stop asking all of these questions.
> Is there a good book on TSQL alone ( I'm not interested in wizards,
> just scripting )...that is short?
> Steve