Long running SQL query in ASP.Net

Greetings,
I have to run a parametirzed sproc on SQL that can take 5-10 minutes to
complete. The query returns a few megs of data that I want to let the
clients download as a CSV. Any ideas on a quick and dirty way to do this
without invoiking SSIS?
ThanksOn Jun 12, 2:44 am, "Shkedy" <shk...@.newsgroups.nospam> wrote:
> Greetings,
> I have to run a parametirzed sproc on SQL that can take 5-10 minutes to
> complete. The query returns a few megs of data that I want to let the
> clients download as a CSV. Any ideas on a quick and dirty way to do this
> without invoiking SSIS?
> Thanks
You can use bcp but this requires xp_cmdshell nned to be enabled ,if
you call from SQL Server|||Hi Shkedy,
I recommend that you try using bcp command and Import/Export Data Wizard.
Either of the two methods is very convenient, but Import/Export Data Wizard
may be more convenient for you.
For bcp utility, you can refer to:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802(SQL.90).aspx
For Import/Export Data Wizard, please refer to:
How to: Start the SQL Server Import and Export Wizard
http://technet.microsoft.com/en-us/library/ms140052(SQL.90).aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks for the prompt and informative response.
I ended up using C# to implement this by launching a new thread that
traverses a reader and writes a text file. I added some more bells and
whistles that I omitted from here but this is the gist of the code:
private void btnSubmit_Click(object sender, System.EventArgs e)
{
if (Page.IsValid)
{
File.Delete(Server.MapPath("~") + "\\reports\\" + CreateFileName());
System.Threading.ThreadStart launcher = new System.Threading.ThreadStart(
CreateCSVReport);
System.Threading.Thread thread = new System.Threading.Thread(launcher);
thread.Start();
Page.RegisterStartupScript("popup", String.Format("<script
language='javascript'>popitup('ReportStatus.aspx?f={0}')</script>'",CreateFileName()));
}
}
private void CreateCSVReport()
{
SqlDataReader rdr;
SqlConnection cnn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString"));
SqlCommand cmd = new SqlCommand("sp_mysproc");
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.param1", SomeValue);
cmd.Parameters.Add("@.param2", SomeValue);
cmd.Connection.Open();
cmd.CommandTimeout = 900;
string tempFileName = Server.MapPath("~") + "\\reports\\temp\\" +
Guid.NewGuid().ToString() + ".csv";
System.IO.StreamWriter wrtr = (File.CreateText(tempFileName));
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int rowCount = 0;
for (int i =0; i < rdr.FieldCount; i++)
{
wrtr.Write("{0}",rdr.GetName(i));
if (i < rdr.FieldCount -1 )
wrtr.Write(",");
}
wrtr.WriteLine();
while (rdr.Read())
{
for (int i =0; i < rdr.FieldCount; i++)
{
wrtr.Write("{0}",rdr[i]);
if (i < rdr.FieldCount -1 )
wrtr.Write(",");
}
rowCount++;
wrtr.WriteLine();
if (rowCount % 1000 == 0)
wrtr.Flush();
}
wrtr.Flush();
rdr.Close();
wrtr.Close();
File.Move(tempFileName, Server.MapPath("~") + "\\reports\\" +
CreateFileName());
}
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:XKigCINrHHA.1032@.TK2MSFTNGHUB02.phx.gbl...
> Hi Shkedy,
> I recommend that you try using bcp command and Import/Export Data Wizard.
> Either of the two methods is very convenient, but Import/Export Data
> Wizard
> may be more convenient for you.
> For bcp utility, you can refer to:
> bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802(SQL.90).aspx
> For Import/Export Data Wizard, please refer to:
> How to: Start the SQL Server Import and Export Wizard
> http://technet.microsoft.com/en-us/library/ms140052(SQL.90).aspx
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||Hi Shkedy,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

Long Oracle report

Hi,
I'm having problems with a report that has a Oracle datasource.
The query source is a stored procedure that takes 3 hours to complete but
reporting services don't wait this time and shows an error page.
Reporting Services is configured to "Do not timeout report execution" and
the report uses "Use default setting"
This works ok with a SLQ Server Stored procedure.
Any ideas?
AndrésHello Andres,
I'd like to know the exact error message you encountered. Also, how long
did it take to display error message after you started to execute the
report? You mentioned, the SP works in SQL but I wonder if the report from
SQL server also takes 3 hours to render?
As I know, IE version 5.x and higher have 60 minute time-out limit. A quick
registry setting and a restart fixes the problem.
1. Add a ReceiveTimeout DWORD value with a data value of (<number
ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
For example, if you want the timeout duration to be 8 minutes, set the
ReceiveTimeout data value to 480000 (<480>*1000).
2. Restart your computer.
You can find more info at:
http://support.microsoft.com/kb/q181050/
You could check the datasource of Oracle in RS designer by open the data
source->Edit->Advanced and set connection timeout to 0 (not timeout).
Also, the issue could occur if Oracle disconnect the connection from RS due
to some issues. You may want to include Oracle support on this specific
issue and look into the logs under
C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>Subject: Long Oracle report
>Date: Tue, 27 Dec 2005 09:33:15 -0500
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi,
>I'm having problems with a report that has a Oracle datasource.
>The query source is a stored procedure that takes 3 hours to complete but
>reporting services don't wait this time and shows an error page.
>Reporting Services is configured to "Do not timeout report execution" and
>the report uses "Use default setting"
>This works ok with a SLQ Server Stored procedure.
>Any ideas?
>Andrés
>
>
>
>|||Hello Peter
I tried with this with SQL Server (A report based in a stored procedure with
a waifor command before a select .... ) and it works fine!.
Now we are working with Oracle to find if it works also. The problem is that
each try is 4 hours!
Regards,
Andres
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hello Andres,
> I'd like to know the exact error message you encountered. Also, how long
> did it take to display error message after you started to execute the
> report? You mentioned, the SP works in SQL but I wonder if the report from
> SQL server also takes 3 hours to render?
> As I know, IE version 5.x and higher have 60 minute time-out limit. A
> quick
> registry setting and a restart fixes the problem.
> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
> ofseconds>)*1000 in the following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
> For example, if you want the timeout duration to be 8 minutes, set the
> ReceiveTimeout data value to 480000 (<480>*1000).
> 2. Restart your computer.
> You can find more info at:
> http://support.microsoft.com/kb/q181050/
>
> You could check the datasource of Oracle in RS designer by open the data
> source->Edit->Advanced and set connection timeout to 0 (not timeout).
> Also, the issue could occur if Oracle disconnect the connection from RS
> due
> to some issues. You may want to include Oracle support on this specific
> issue and look into the logs under
> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>|||Hello Andres,
Thank you for taking time to write in about the status of the issue. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>References: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
<FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Long Oracle report
>Date: Tue, 3 Jan 2006 11:51:19 -0500
>Lines: 111
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <OCz67XIEGHA.1424@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:66018
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hello Peter
>I tried with this with SQL Server (A report based in a stored procedure
with
>a waifor command before a select .... ) and it works fine!.
>Now we are working with Oracle to find if it works also. The problem is
that
>each try is 4 hours!
>Regards,
>Andres
>
>
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
>> Hello Andres,
>> I'd like to know the exact error message you encountered. Also, how long
>> did it take to display error message after you started to execute the
>> report? You mentioned, the SP works in SQL but I wonder if the report
from
>> SQL server also takes 3 hours to render?
>> As I know, IE version 5.x and higher have 60 minute time-out limit. A
>> quick
>> registry setting and a restart fixes the problem.
>> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
>> ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
>> For example, if you want the timeout duration to be 8 minutes, set the
>> ReceiveTimeout data value to 480000 (<480>*1000).
>> 2. Restart your computer.
>> You can find more info at:
>> http://support.microsoft.com/kb/q181050/
>>
>> You could check the datasource of Oracle in RS designer by open the data
>> source->Edit->Advanced and set connection timeout to 0 (not timeout).
>> Also, the issue could occur if Oracle disconnect the connection from RS
>> due
>> to some issues. You may want to include Oracle support on this specific
>> issue and look into the logs under
>> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
>> Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>>
>
>

Logins dont work after restoring on backup server

I am doing a complete backup on a sql 7 db and then doing a complete
restore (with overwrite existing db) on a sql 2000 server. This is now
our hot standby server. I have the process automated and it works
great. The only problem I have now is the logins dont work.

I have tried running EXEC sp_change_users_login 'Report', and the
logins appear.
However, when I run EXEC sp_helplogins 'joe', the results are empty.

So, I am guessing all I need is a sp that will re-associate my logins
with the correct db and grant the appropriate permissions.

If anyone has any ideas that would be great.

I have also considered doing a log ship instead of a full backup and
restore. Does anyone have any suggections or good examples of how to
make that happen?

ThanxUzytkownik "Dave" <funkdm1@.yahoo.com> napisal w wiadomosci
news:f5174e0f.0307110451.5a75ae07@.posting.google.c om...
> I am doing a complete backup on a sql 7 db and then doing a complete
> restore (with overwrite existing db) on a sql 2000 server. This is now
> our hot standby server. I have the process automated and it works
> great. The only problem I have now is the logins dont work.

O course, because they are stored in master-->sysxlogins table.
You have only database users in your database, but they are not connected
with logins.

> I have tried running EXEC sp_change_users_login 'Report', and the
> logins appear.
> However, when I run EXEC sp_helplogins 'joe', the results are empty.

O fourse, becaues sp _helplogins provides information about logins and the
associated users in each database, but they are not exist, right (its not
the same database master)

> So, I am guessing all I need is a sp that will re-associate my logins
> with the correct db and grant the appropriate permissions.

Use sp_change_users procedure with Auto_Fix option (but be cousious. Why?
See BOL ;)

> If anyone has any ideas that would be great.

I hope it will be enough.

> I have also considered doing a log ship instead of a full backup and
> restore. Does anyone have any suggections or good examples of how to
> make that happen?
Hmm? Can you specify? I don tunderstand what is you problem (sorry, probably
my english is not enough:(

regards
Marcin D