a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

A SSIS package

Hi,

I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.

So query in OLE DB Source:

Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between ‘1/1/2006’ and ‘1/31/2006’

OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.

My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.

Thanks,

That is a verry common scenario when loading data. The most popular solution in this forum is tu used a Lookup transform in the data flow against the destination table; no matches in LU transform are treated as errors; so you can configure the error output of the LU transform to 'redirect error'; then the error output is your 'new rows' out put and the no-error output is the 'existing rows' output.:

Somewhere in this thread there is a link to Jamie's blog where that technique is explanied (an other options discussed).

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=779836&SiteID=17

Be sure to understand how Lookup transform uses memory resources as that could play against you if the volume of data is to big or if memory not enough

|||I second Rafael's comments.

A SSIS package

Hi,

I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.

So query in OLE DB Source:

Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between '1/1/2006' and '1/31/2006'

OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.

My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.

Thanks,

I can't find a simple way to do this in SSIS. I'd better do this in T-SQL, usingLinked Servers. Suppose you've set a linked server (namedSourceServer) for the source server on destination server, you can use such query to accomplish INSERT/UPDATE:

UPDATE TableDest
SET [Desc]=src.[Desc], [CreateDate]=src.[CreateDate]
FROM TableDest dest JOIN [SourceServer].SourceDB..TableSrc src
ON dest.ID=src.ID
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'

INSERT INTO TableDest
SELECT * FROM [SourceServer].SourceDB.TableSrc src
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'
AND src.ID NOT IN (SELECT ID FROM TableDest)

A simple question on the connection string format on sql server 2005!

Hi everyone!
I am new to visual studio 2005 and sql server 2005.
Up to now i was using visual studio 2003 and ms access.

When i wanted to create a web application that communicates withan access database, i just had to take the *.mdb file and put itinto a specific folder inside my application's folder and then useServer.MapPath on the connection string (so it can run on any pc).

Now, i want to do the same with sql server 2005 and visual studio 2005.I use the connection string: "Server=MYSERVER;InitialCatalog=test_db;Integrated Security=SSPI".

My problems are:
1) Can i just take the *.mdf file put it inside the App_Data folder like i did on access?
2) What should be the connection string so that my application can run on any pc (with sql server 2005)?

Thanx in advance guys!Right click the app_data folder in solution explorer, say add new item. Choose the SQL Database, give it a name, and click ok. Drop a sqldatasource on a form, and choose the correct connection string from the dropdown, and when it asks if you want to put the connection string in web.config, say yes.

a simple question on compatibility

Hello,

I was wondering if there is a version conflict with SSRS for SQL 2000 and Visual Studio 2005? When I went to install the client it was looking for visual studio 2003 and well, I don't have that. Asides from stating it during the install, the client appeared to fully install and of course when I opened VS, no option for SSRS.

I was installing the client over a WAN and figured I should ask before I go on a bug hunt for what I did wrong.

Thanks

VS 2005 will produce 2k5 reports which can′t be deployed on a SQL 2k RS.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

A request to send data to the computer running IIS has failed

Hi,

I Have an application for Pocket PC 2003 developed in Visual Studio 2003(VB.Net , Smart Device App). I Use Sql Server CE as the database which Synchronizes with Sql Server 2000, SP4. The IIS and the Sql Server 2000 are in different Machines. I Have Installed the proper Sqlserver Ce in the IIS machine and created virtual directory with Basic authentication.I connect my device through Active Sync 3.7. I could browse the sscesa20.dll from Pocket IE and get the ' SQL Server CE Server Agent ' message. The PC to which my pocket PC is connected is under a Proxy.

When I tried the RDA.Pull() , the function fails and returns the error
" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT. [,,,,,]
Minor Error : 28037
Source: Sql Server 2000 Windows CE Edition. "

The same application works in other network which is not under a proxy.

Any clues ?

Thanks,
Jibu
eSystem Software

What happens if you try to use InternetProxy, InternetProxyLogin, ...etc.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

Hi Laxmi Narsimha Rao,

Yes. That was exactly the problem, which I had rectified sometimes back. Any way thanks for the reply.

Thanks,

Jibu

|||

Glad to hear that things are working fine from your end.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

I am attempting to run (on the emulator) a starterkit application for Pocket PC 2003 SE that I downloaded which uses replication/merge. I received the following error:

" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT.
Minor Error : 28037

Source: Microsoft SQL Server 2005 Mobile Edition. "

You recommended to use InternetProxy, InternetProxyLogin, etc.

What do you mean by that?

|||

Hi,

i have created publication on server.and when i try to create sql server mobile subscription on my machine, i get following error:

TITLE: Microsoft SQL Server Management Studio

A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80072EE2 (28037)

BUTTONS:

OK

my machine and server are on different.

i am able to get "SQL Server Mobile Server Agent 3.0" message when i run /sqlcesa30.dll" href="http://links.10026.com/?link=http://_253cserver_name_253e/_253Cvirtual_dir_253Esqlcesa30.dll" target=_blank>http://<server_name>/<virtual_dir>sqlcesa30.dll through Internet explorer
what should i do now.please help me.

|||

This is a solution which I found online and it works for me.

1.

Click Start, click Run, type firewall.cpl, and then click OK.

2.

In the Windows Firewall dialog box, click the Advanced tab.

3.

In the Network Connection Settings box, click the connection that your computer uses, and then click Settings.

4.

In the Advanced Settings dialog box, click Web Server (HTTP), and then click Secure Web Server (HTTPS).

Note For additional information about when you must allow users to access the Secure Web Server (HTTPS) on your computer, see the "More Information" section.

5.

Click OK.

6.

In the Windows Firewall dialog box, click OK.

|||

Hi,

I am getting following error

"A request to send data to the computer running IIS has failed. For more information, see HRESULT."

I have SSL on Proxy Server which redirect the request to web server. Communication between Client and Proxy use SSL but the communication between PROXY and Web Server is normal (without SSL). I installed Sql Server Mobile Tool on Web Server and configured virtual directory without SSL and Basic Authentication. When client hit this URL from out side of the world, it must go through SSL on Proxy. When I directly go to URL (https://website/test/sqlcesa30.dll) on web browser, it's working fine. But when i use it in my .Net code i am getting error as above.

Same code is working without SSL on proxy.

Can you please tell me what could be the problem?

Thanks & Regards,

D

A request to send data to the computer running IIS has failed

Hi,

I Have an application for Pocket PC 2003 developed in Visual Studio 2003(VB.Net , Smart Device App). I Use Sql Server CE as the database which Synchronizes with Sql Server 2000, SP4. The IIS and the Sql Server 2000 are in different Machines. I Have Installed the proper Sqlserver Ce in the IIS machine and created virtual directory with Basic authentication.I connect my device through Active Sync 3.7. I could browse the sscesa20.dll from Pocket IE and get the ' SQL Server CE Server Agent ' message. The PC to which my pocket PC is connected is under a Proxy.

When I tried the RDA.Pull() , the function fails and returns the error
" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT. [,,,,,]
Minor Error : 28037
Source: Sql Server 2000 Windows CE Edition. "

The same application works in other network which is not under a proxy.

Any clues ?

Thanks,
Jibu
eSystem Software

What happens if you try to use InternetProxy, InternetProxyLogin, ...etc.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

Hi Laxmi Narsimha Rao,

Yes. That was exactly the problem, which I had rectified sometimes back. Any way thanks for the reply.

Thanks,

Jibu

|||

Glad to hear that things are working fine from your end.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

I am attempting to run (on the emulator) a starterkit application for Pocket PC 2003 SE that I downloaded which uses replication/merge. I received the following error:

" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT.
Minor Error : 28037

Source: Microsoft SQL Server 2005 Mobile Edition. "

You recommended to use InternetProxy, InternetProxyLogin, etc.

What do you mean by that?

|||

Hi,

i have created publication on server.and when i try to create sql server mobile subscription on my machine, i get following error:

TITLE: Microsoft SQL Server Management Studio

A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80072EE2 (28037)

BUTTONS:

OK

my machine and server are on different.

i am able to get "SQL Server Mobile Server Agent 3.0" message when i run /sqlcesa30.dll" href="http://links.10026.com/?link=http://%3cserver_name%3e/%3Cvirtual_dir%3Esqlcesa30.dll" target=_blank>http://<server_name>/<virtual_dir>sqlcesa30.dll through Internet explorer
what should i do now.please help me.

|||

This is a solution which I found online and it works for me.

1. Click Start, click Run, type firewall.cpl, and then click OK. 2. In the Windows Firewall dialog box, click the Advanced tab. 3. In the Network Connection Settings box, click the connection that your computer uses, and then click Settings. 4. In the Advanced Settings dialog box, click Web Server (HTTP), and then click Secure Web Server (HTTPS).

Note For additional information about when you must allow users to access the Secure Web Server (HTTPS) on your computer, see the "More Information" section. 5. Click OK. 6. In the Windows Firewall dialog box, click OK.

|||

Hi,

I am getting following error

"A request to send data to the computer running IIS has failed. For more information, see HRESULT."

I have SSL on Proxy Server which redirect the request to web server. Communication between Client and Proxy use SSL but the communication between PROXY and Web Server is normal (without SSL). I installed Sql Server Mobile Tool on Web Server and configured virtual directory without SSL and Basic Authentication. When client hit this URL from out side of the world, it must go through SSL on Proxy. When I directly go to URL (https://website/test/sqlcesa30.dll) on web browser, it's working fine. But when i use it in my .Net code i am getting error as above.

Same code is working without SSL on proxy.

Can you please tell me what could be the problem?

Thanks & Regards,

D

A request to send data to the computer running IIS has failed

Hi,

I Have an application for Pocket PC 2003 developed in Visual Studio 2003(VB.Net , Smart Device App). I Use Sql Server CE as the database which Synchronizes with Sql Server 2000, SP4. The IIS and the Sql Server 2000 are in different Machines. I Have Installed the proper Sqlserver Ce in the IIS machine and created virtual directory with Basic authentication.I connect my device through Active Sync 3.7. I could browse the sscesa20.dll from Pocket IE and get the ' SQL Server CE Server Agent ' message. The PC to which my pocket PC is connected is under a Proxy.

When I tried the RDA.Pull() , the function fails and returns the error
" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT. [,,,,,]
Minor Error : 28037
Source: Sql Server 2000 Windows CE Edition. "

The same application works in other network which is not under a proxy.

Any clues ?

Thanks,
Jibu
eSystem Software

What happens if you try to use InternetProxy, InternetProxyLogin, ...etc.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

Hi Laxmi Narsimha Rao,

Yes. That was exactly the problem, which I had rectified sometimes back. Any way thanks for the reply.

Thanks,

Jibu

|||

Glad to hear that things are working fine from your end.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

I am attempting to run (on the emulator) a starterkit application for Pocket PC 2003 SE that I downloaded which uses replication/merge. I received the following error:

" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT.
Minor Error : 28037

Source: Microsoft SQL Server 2005 Mobile Edition. "

You recommended to use InternetProxy, InternetProxyLogin, etc.

What do you mean by that?

|||

Hi,

i have created publication on server.and when i try to create sql server mobile subscription on my machine, i get following error:

TITLE: Microsoft SQL Server Management Studio

A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80072EE2 (28037)

BUTTONS:

OK

my machine and server are on different.

i am able to get "SQL Server Mobile Server Agent 3.0" message when i run /sqlcesa30.dll" href="http://links.10026.com/?link=http://%3cserver_name%3e/%3Cvirtual_dir%3Esqlcesa30.dll" target=_blank>http://<server_name>/<virtual_dir>sqlcesa30.dll through Internet explorer
what should i do now.please help me.

|||

This is a solution which I found online and it works for me.

1. Click Start, click Run, type firewall.cpl, and then click OK. 2. In the Windows Firewall dialog box, click the Advanced tab. 3. In the Network Connection Settings box, click the connection that your computer uses, and then click Settings. 4. In the Advanced Settings dialog box, click Web Server (HTTP), and then click Secure Web Server (HTTPS).

Note For additional information about when you must allow users to access the Secure Web Server (HTTPS) on your computer, see the "More Information" section. 5. Click OK. 6. In the Windows Firewall dialog box, click OK.

|||

Hi,

I am getting following error

"A request to send data to the computer running IIS has failed. For more information, see HRESULT."

I have SSL on Proxy Server which redirect the request to web server. Communication between Client and Proxy use SSL but the communication between PROXY and Web Server is normal (without SSL). I installed Sql Server Mobile Tool on Web Server and configured virtual directory without SSL and Basic Authentication. When client hit this URL from out side of the world, it must go through SSL on Proxy. When I directly go to URL (https://website/test/sqlcesa30.dll) on web browser, it's working fine. But when i use it in my .Net code i am getting error as above.

Same code is working without SSL on proxy.

Can you please tell me what could be the problem?

Thanks & Regards,

D

A request to send data to the computer running IIS has failed

Hi,

I Have an application for Pocket PC 2003 developed in Visual Studio 2003(VB.Net , Smart Device App). I Use Sql Server CE as the database which Synchronizes with Sql Server 2000, SP4. The IIS and the Sql Server 2000 are in different Machines. I Have Installed the proper Sqlserver Ce in the IIS machine and created virtual directory with Basic authentication.I connect my device through Active Sync 3.7. I could browse the sscesa20.dll from Pocket IE and get the ' SQL Server CE Server Agent ' message. The PC to which my pocket PC is connected is under a Proxy.

When I tried the RDA.Pull() , the function fails and returns the error
" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT. [,,,,,]
Minor Error : 28037
Source: Sql Server 2000 Windows CE Edition. "

The same application works in other network which is not under a proxy.

Any clues ?

Thanks,
Jibu
eSystem Software

What happens if you try to use InternetProxy, InternetProxyLogin, ...etc.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

Hi Laxmi Narsimha Rao,

Yes. That was exactly the problem, which I had rectified sometimes back. Any way thanks for the reply.

Thanks,

Jibu

|||

Glad to hear that things are working fine from your end.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

I am attempting to run (on the emulator) a starterkit application for Pocket PC 2003 SE that I downloaded which uses replication/merge. I received the following error:

" Error Code: 80072EE7
A request to send data to the computer running IIS has failed. For more information see HRESULT.
Minor Error : 28037

Source: Microsoft SQL Server 2005 Mobile Edition. "

You recommended to use InternetProxy, InternetProxyLogin, etc.

What do you mean by that?

|||

Hi,

i have created publication on server.and when i try to create sql server mobile subscription on my machine, i get following error:

TITLE: Microsoft SQL Server Management Studio

A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80072EE2 (28037)

BUTTONS:

OK

my machine and server are on different.

i am able to get "SQL Server Mobile Server Agent 3.0" message when i run /sqlcesa30.dll" href="http://links.10026.com/?link=http://%3cserver_name%3e/%3Cvirtual_dir%3Esqlcesa30.dll" target=_blank>http://<server_name>/<virtual_dir>sqlcesa30.dll through Internet explorer
what should i do now.please help me.

|||

This is a solution which I found online and it works for me.

1. Click Start, click Run, type firewall.cpl, and then click OK. 2. In the Windows Firewall dialog box, click the Advanced tab. 3. In the Network Connection Settings box, click the connection that your computer uses, and then click Settings. 4. In the Advanced Settings dialog box, click Web Server (HTTP), and then click Secure Web Server (HTTPS).

Note For additional information about when you must allow users to access the Secure Web Server (HTTPS) on your computer, see the "More Information" section. 5. Click OK. 6. In the Windows Firewall dialog box, click OK.

|||

Hi,

I am getting following error

"A request to send data to the computer running IIS has failed. For more information, see HRESULT."

I have SSL on Proxy Server which redirect the request to web server. Communication between Client and Proxy use SSL but the communication between PROXY and Web Server is normal (without SSL). I installed Sql Server Mobile Tool on Web Server and configured virtual directory without SSL and Basic Authentication. When client hit this URL from out side of the world, it must go through SSL on Proxy. When I directly go to URL (https://website/test/sqlcesa30.dll) on web browser, it's working fine. But when i use it in my .Net code i am getting error as above.

Same code is working without SSL on proxy.

Can you please tell me what could be the problem?

Thanks & Regards,

D

A newbie question

Hi all,

I am an experienced developer but a TOTAL newbie when it comes to databases. I am creating a new Visual Basic app for which I need a database and I'm using SQL Server Express with VS 2005.

The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?

To clarify, I don't know the entire schema of the database yet; I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices:

1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.

2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.

So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?

The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?

Sorry for such basic questions, but I'm really confused.

Thanks!

Phil

hi Phil,

first and always, [IMVHO]

pwinant wrote:

The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?

To clarify, I don't know the entire schema of the database yet;

bad sentence, this one... this mean no design has been actually implemented... just a brute force "write through" approach...

ok.. that can be an experience as well.. and you see some of the effects when using User Instances feature ... personally I do not use that paradigm so this trouble is not included in my day to day problems list

I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices:

1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.

2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.

So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?

what if you copy back your actual mdf + ldf files in the project folder when you are finished executing/debugging the app, so that you can continue modifying it at design time and maintain the inserted data?

The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?

my answer is, again, not to use User Instance feature and connect to a "traditional" instance of SQL Server/SQLExpress providing traditional connection parameters... you connect that way to the instance present in your (usually) \Program Files\Microsoft SQL Server\MSSQL.x folder, and it's database are usually in the \MSSQL\Data subfolder...

so you provide InstaceName, InitialCatalog, and authentication info (trusted or standard SQL Server login's credentials) parameters like

connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
or
connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"

but, if you use an Express edition of Visual Studio, you loose the possibility to use the integrated designers as they only work with User Instances implementations..

you can code (manually code) your app to connect to "standard" SQL Server\SQLExpress editions, but not via the designers...

[/IMVHO]

regards

|||

Hi Phil,

I am not the most experienced guy in this forum but I believe I can make a suggestion. I believe you are updating your DB schema in VS IDE. I suggest you use of SQL Management Studio Express (It's a free download from Microsoft). In object explorer window of SQL MSE you can right click on databases node and select attach option. This will allow you to select an mdf file from somewhere on your computer and attach it to your DB. I hope it helps.

FlatWhite

|||

Hi Phil,

Check out my last post in this thread. In the post I discuss user instances and how they work and point to a white paper that explains them in more detail. The behavior you're seeing is the result of how VS works with files and is by design. When you include your database as part of your project, it gets moved around just like any project file; when you are debuging (F5) you are actually running your application from the .\bin\debug directly, not from your project directory, which is why you see this behavior. This behavior is required for ClickOnce to work, but is targeted as single user applications. If you're creating a multi-user application you shouldn't be using user instances, you should put your database on a central server and connect to it through tat.

There is no one right way to deal with the whole "lost data" issue caused by the VS behavior related to embedded databases. I've described a couple ways, here and here and MF mentions a variation in the post I've already linked to above. There are others as well, it's all about how you want to deploy your database.

Mike

|||

Hi all,

I've received three emails now saying that different people have replied to my post, but when I click on the link in the email it says there's no such post, and I don't see anything here in the forum except my original post. Not sure why that's happening (I hope this post will show up!). Anyway, thanks to all those who are trying to answer my question. :-) Hopefully your posts will actually show up for me at some point so I can read your entire message!

Phil

Edit: ok, now that I've replied I can see the other posts! Yay!

|||

hi Phil,

actually I do not see your last post as well ... does this one shows up?

let's see..

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures an
d
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
ChrisChris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:

> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>
>|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:

> Well, try to drop the indexes during the insertion and later recreate them
.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.

> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Thanks for the replies. You have been helpful.|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:

>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures and
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
Chris
Chris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:

> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>
|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:

> Well, try to drop the indexes during the insertion and later recreate them.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.
|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.

> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Thanks for the replies. You have been helpful.
|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:

>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures and
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
ChrisChris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:
> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>> Chris
>> Do you have any indexes define on the table ?
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
>|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:
> Well, try to drop the indexes during the insertion and later recreate them.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.
> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Thanks for the replies. You have been helpful.|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:
>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

a generic error occurred in gdi+

I'm receiving "a generic error occurred in gdi+" processing error while running a preview of my report inside visual studio. When this report is opened by a client, their browser will just hang (Not Responding).

Other reports don't have error. The report will not render any chart although thousands of record will be need to output.

Hope anyone can help. Thanks!

Is there any solution to this problem?

Other threads suggest that this type of error may be due to security permissions.

|||Might be an issue with an older version of GDI+. Maybe try this one: http://www.microsoft.com/downloads/details.aspx?FamilyID=6a63ab9c-df12-4d41-933c-be590feaa05a&DisplayLang=en.|||

Brian,

Thanks for your response.

In the redist.txt it tells you that you cannot update the gdiplus.dll in Windows XP because it is a protected file; however, we replaced it and after a reboot, it fixed the problem.

If anyone has trouble replacing the gdiplus.dll in the "windows\system32" directory, it may be a good idea to reboot in safe mode, replace the gdiplus.dll and then reboot into Windows normally.

|||

When Drawing an Image from a Stream, make sure that the Stream has been re-positioned back to the beginning if it has been accessed before.

GDI+ is very powerful; too bad it doesn't have a more robust error message interface. Are there even any error codes available? I couldn't even find an InnerException.

HTH someone else

Cheers,

Robert

a generic error occurred in gdi+

I'm receiving "a generic error occurred in gdi+" processing error while running a preview of my report inside visual studio. When this report is opened by a client, their browser will just hang (Not Responding).

Other reports don't have error. The report will not render any chart although thousands of record will be need to output.

Hope anyone can help. Thanks!

Is there any solution to this problem?

Other threads suggest that this type of error may be due to security permissions.

|||Might be an issue with an older version of GDI+. Maybe try this one: http://www.microsoft.com/downloads/details.aspx?FamilyID=6a63ab9c-df12-4d41-933c-be590feaa05a&DisplayLang=en.|||

Brian,

Thanks for your response.

In the redist.txt it tells you that you cannot update the gdiplus.dll in Windows XP because it is a protected file; however, we replaced it and after a reboot, it fixed the problem.

If anyone has trouble replacing the gdiplus.dll in the "windows\system32" directory, it may be a good idea to reboot in safe mode, replace the gdiplus.dll and then reboot into Windows normally.