Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

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 script to generate a script?

Hi,
I'm using 2005's management studio, and I was wondering if there's
anyway to script the "generate scripts - script wizard" so that when I
want to take a snapshot of my database structure I can just run a quick
script instead of having to use the wizard. The fact that it's a
"wizard" leads me to think that there is a "non-wizard" way of doing
it, but I can't find anything in the docs.
Thanks
Chris.Hmm, just a guess , take a look at SMO object library (former SQL DMO)
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>|||You can use SMO...
check this link.. for a start
http://davidhayden.com/blog/dave/ar...02/09/2795.aspx
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>

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 problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif

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.