Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A

My objective is to use Enterprise Manager to move (copy) my SQL db from the server to my windows desktop computer.

I downloaded MSDE and am having trouble installing it, no doubt because I do not understand the documentaion (ReadMeMSDE2000A.htm).

When I try to run setup, I get that message that says:

"A strong SA password is required for security reasons. Please use SAPWD switch to supply the same."

Considering my purpose, do I need a "strong" SA password? If not, how do I get around it? If yes, how do I set it up?

I am a Mac user so I have poor windows skills, please make it as painless as possible for me, thanks!

RonCreate 2 file in the same director with setup.exe

1. Setup.bat
setup.exe /settings Settings.ini /L*v C:/MSDELog.log

2. Settings.ini
[Options]
SAPWD=YourPassword
DISABLENETWORKPROTOCOLS=0
SECURITYMODE=SQL
DATADIR="C:\MSDE"
TARGETDIR="C:\MSDE"

Run the setup.bat|||I have tried your code and other code like that and in each case it fails. I keep getting this error message that says:

"The instance name specified is not valid."

Note that your code didn't even use the instance name.

In your code you use:

DATADIR="C:\MSDE"
TARGETDIR="C:\MSDE"

Actually on my C drive the folder is called MSDERela. I tried changing your code to indicate that and it still doesn't work. Same error message.

Any other suggestions? Thanks!|||I added the INSTANCENAME and added quote marks to some of the code and it seems to have worked. It produced a folder on my C drive wtih a number of files in it.

Now can someone tell me how to launch MSDE?

Now you know why I call myself SQL_Dummy!sql

Microsoft SQL Server 2000 Desktop Engine

Trying to install Microsoft SQL Server 2000 Desktop Engine on my laptop. I downloaded the software and opened it to a folder on the C drive. Then from the command prompt I entered this:

C:\msderela>setup sapwd = hillary instancename=mjh targetdir=c:\mike

The program starts to install and seems to get near to the end of instal and then the progress bar starts going backwards and nothing gets installed...any ideas of what I'm doing wrong...I really don't know anything about this at all.

ThanksSounds like you've tried it a couple of time already, huh?

Bounce the box, see what you got :)|||Like I said...I know nothing about this...bounce the box? sorry for being so stupid|||ummm. Reboot? Restart? Unplug and then re-plug? Either one will work ;)|||Originally posted by rdjabarov
Sounds like you've tried it a couple of time already, huh?

Bounce the box, see what you got :)

Is it possible to do if it does not look like a box (laptop)... ;)|||I've rebooted several times and still get the same results...|||What is the OS on your laptop?|||2000 pro|||progress bar going backwards?? check if the monitor is kept upside down??|||Thanks....|||I don't know if this will help but I have attached the error log. I don't understand what it is saying.|||Where is your log file?|||I'm sending the file again|||Do you have enough hard disk space?|||I have 2.43 GB|||I'm wondering if the problem is
C:\msderela>setup sapwd = hillary instancename=mjh targetdir=c:\mike

I pasted the text from Microsoft website:

TARGETDIR="executable_folder_path"

Specifies the folder where the Desktop Engine executable files are to be installed. The default for executable_folder_path when installing a default instance is C:\Program Files\Microsoft SQL Server\MSSQL\Binn\. The default for executable_folder_path when installing a named instance is C:\Program Files\Microsoft SQL Server\MSSQL$<InstanceName>\Binn\, where <InstanceName> is the name specified in the INSTANCENAME option. The file path for this parameter must end with a backslash (\).

Read the details at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_84xl.asp|||Nope...still won't install|||Did you try to click the file Setup.exe for the installation and use the default folder?

Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

Insert Into SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

Insert Into MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

Insert Into SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

Insert Into MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

InsertInto SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC)values('JP002','Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

InsertIntoMRPLVOBJ.FOEINTPL (IPLACE, IPLDSC)values('JP002','Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

Insert Into SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

Insert Into MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

sql

Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

Insert Into SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

Insert Into MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

Microsoft Data Analyzer

I have downloaded Microsoft Data Analyzer and it is
asking for a CUBE on online OLAP server. Is there anyway
to connect it to Data in a plain 2000 SQL server.
if I do need OLAP what do I need to install on my SQL
server
Thanks
Steven Hill wrote:
> I have downloaded Microsoft Data Analyzer and it is
> asking for a CUBE on online OLAP server. Is there anyway
> to connect it to Data in a plain 2000 SQL server.
> if I do need OLAP what do I need to install on my SQL
> server
You need to have OLAP services installed on your SQL Server. From MDA FAQ -
OLAP refers to an online analytical processing database capable of handling
queries more complex than those handled by standard relational databases. It
does this through multidimensional access to data, intensive calculation
capability, and specialized indexing techniques. To learn more about this
technology, visit the OLAP and Data Mining page
(http://msdn.microsoft.com/library/de...-us/createdw/c
reatedw_1vqf.asp) on the MSDN site.
sincerely,
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Microsoft Baseline Security Analyser 2.1

Hi

I downloaded MBSA and ran it against my SQL 2005 Server. It tells me that I have a severe risk because

'The following databases have public access.Remove the public access if it is not required - tempdb , model , msdb , ReportServer , ReportServerTempDB'

I have checked these databases and each have the Guest User but it is disabled. If I check the database properties the public role has no permissions against the listed databases.

Is this a bug with MBSA? If not how do I remove Public Access?

Hi Ewan, are you using a Xp_cmdshell enabled?

|||

Hi Emanuel

I an not sure what you mean. I have installed the product MBSA on a PC running VISTA business and am scanning the SQL server from this PC. The server operating system is Windows Server 2003 R2

Regards

|||

Hi Emanuel

I have now looked it up and xp_cmdshell is not enabled

Regards

|||

The MBSA given't u more details about this vulnerability message?

|||

Hi Emanuel

No MBSA gives no further information on this message

|||

Ewan, has your sql server a valid internet ip address or any other network vulnerability?

|||I mean, does your sql server is exposed on internet?|||

Hi Emanuel

Only via Reporting Services

|||

I'm suggest you investigate any vulnerability on SSRS like a public access on web service.

see the link below, there is a cool tool to do a verification on any possible network vulnerability.

http://www.gfi.com/lannetscan/?adv=40&loc=3&adclickid=14102298

Microsoft Access Database Synchronizer (ADS) RTW Help

Someone please help, Ive downloaded and instsalled Microsoft Access Database Synchronizer (ADS) RTW onto my desktop and the files have installed to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\Sync\Access. Ive got a device running windows CE 5.0 connected to the desktop via USB. Ive installed all the necessary CAB files onto the device but how do I get the ADS working? What do I have to do with the files installed? I am fairly new to this so can someone please help me. Isnt there supose to be an option in the start menu to run the wizard, i don't seem to be getting this?

Thanks in advance

Sami

You should read the readme.doc in the C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\sync\Access folder. Specifically from page 11 and on it is explained how to get started with the sample wizard.

|||

I have looked at this readme doc but the first step says:

Click Start, Programs, and then click Access Database Synchronization Sample Wizard

But this is not in my programs list, why is that? I have installed the msi file and was expecting the program to appear in the start menu automatically as it says in the readme, but it is not. Any ideas?|||

You must do these thing first on your Windows Mobile device (as stated):

First, you should install .NET Compact Framework 2.0 and device cab files on the device for the Access Database Synchronization Sample Wizard.

The device cab file is in your Program Files\Microsoft SQL Server Compact Edition\v3.1\Sync\Access folder, ot is called:

sqlce.access.sample.PPC.wce5.armv4i.CAB

Install .NET Compact Framework 2.0 and copy the sqlce.access.sample.PPC.wce5.armv4i.CAB file to your Windows Mobile device (or emulator) and install it. Then it will appear under Start, Programs as stated in the readme.doc

|||

I installed .NET Compact Framework 2.0 and device cab files. When I try to install the cab file sqlce.access.sample.PPC.wce5.armv4i.CAB on the device i get the following error:

The file "\My Documents\sqlce.access.sample.PPc.wce5.armv4i.CAB is not a valid Windows CE Setup file.

What could be the problem for this?

|||

Your device must be Windows Mobile 5, is that the case?

|||

The device is running Windows CE 5.0 not Window Mobile 5. I thought that the software was Windows CE compatible? Is this incorrect?

|||Yes, Windows CE is supported, but the sample application only runs under Windows Mobile 5.0. Try it in the emualtor to explore the capabilities (and limitations) of the "product".

Microsoft Access Database Synchronizer (ADS) RTW Help

Someone please help, Ive downloaded and instsalled Microsoft Access Database Synchronizer (ADS) RTW onto my desktop and the files have installed to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\Sync\Access. Ive got a device running windows CE 5.0 connected to the desktop via USB. Ive installed all the necessary CAB files onto the device but how do I get the ADS working? What do I have to do with the files installed? I am fairly new to this so can someone please help me. Isnt there supose to be an option in the start menu to run the wizard, i don't seem to be getting this?

Thanks in advance

Sami

You should read the readme.doc in the C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\sync\Access folder. Specifically from page 11 and on it is explained how to get started with the sample wizard.

|||

I have looked at this readme doc but the first step says:

Click Start, Programs, and then click Access Database Synchronization Sample Wizard

But this is not in my programs list, why is that? I have installed the msi file and was expecting the program to appear in the start menu automatically as it says in the readme, but it is not. Any ideas?|||

You must do these thing first on your Windows Mobile device (as stated):

First, you should install .NET Compact Framework 2.0 and device cab files on the device for the Access Database Synchronization Sample Wizard.

The device cab file is in your Program Files\Microsoft SQL Server Compact Edition\v3.1\Sync\Access folder, ot is called:

sqlce.access.sample.PPC.wce5.armv4i.CAB

Install .NET Compact Framework 2.0 and copy the sqlce.access.sample.PPC.wce5.armv4i.CAB file to your Windows Mobile device (or emulator) and install it. Then it will appear under Start, Programs as stated in the readme.doc

|||

I installed .NET Compact Framework 2.0 and device cab files. When I try to install the cab file sqlce.access.sample.PPC.wce5.armv4i.CAB on the device i get the following error:

The file "\My Documents\sqlce.access.sample.PPc.wce5.armv4i.CAB is not a valid Windows CE Setup file.

What could be the problem for this?

|||

Your device must be Windows Mobile 5, is that the case?

|||

The device is running Windows CE 5.0 not Window Mobile 5. I thought that the software was Windows CE compatible? Is this incorrect?

|||Yes, Windows CE is supported, but the sample application only runs under Windows Mobile 5.0. Try it in the emualtor to explore the capabilities (and limitations) of the "product".