Get a timeout error when trying to export to excel

Hi I generate a report successfully in SQL reporting services . Once
the report is generated I then select the export option to export it
to excel
I then get this error message( see below) after waiting about 20 mins
for the excel file to be generated.
There is a lot of data in this report . There is 1240 pages and there
is about 50 rows for each page. That makes it 62000 rows in the
report.
I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
Is there some limitation to the amount of data you can export to
excel.'
thanks in advance
Aidan Geraghty
Server Error in '/Reports' Application.
________________________________________
For more information about this error navigate to the report server on
the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Exception: For more information about this
error navigate to the report server on the local server machine, or
enable remote errors
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace
below.
Stack Trace:
[Exception: For more information about this error navigate to the
report server on the local server machine, or enable remote errors]
[Exception: An error occurred during rendering of the report.]
[Exception: The operation has timed out. The report server has
canceled the operation.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
isAbortable, String url, Stream outputStream, String& mimeType,
String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
isAbortable, String format, String deviceInfo, NameValueCollection
urlAccessParameters, Stream reportStream, String& mimeType, String&
fileNameExtension) +958
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
format, String deviceInfo, NameValueCollection additionalParams,
String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +64
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.210
When I look at the log files on the server I find this error message:
2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
- 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
ReportServer?%2fReports
%2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
- 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
%20Mar%2030%2014%3A49%3A52%202007 - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
POST /reports/ReportService2005.asmx - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
DefaultAppPool
2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -On Apr 2, 9:04 am, aidan.gerag...@.gmail.com wrote:
> Hi I generate a report successfully in SQL reporting services . Once
> the report is generated I then select the export option to export it
> to excel
> I then get this error message( see below) after waiting about 20 mins
> for the excel file to be generated.
> There is a lot of data in this report . There is 1240 pages and there
> is about 50 rows for each page. That makes it 62000 rows in the
> report.
> I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
> Is there some limitation to the amount of data you can export to
> excel.'
> thanks in advance
> Aidan Geraghty
> Server Error in '/Reports' Application.
> ________________________________________
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Exception: For more information about this
> error navigate to the report server on the local server machine, or
> enable remote errors
> Source Error:
> An unhandled exception was generated during the execution of the
> current web request. Information regarding the origin and location of
> the exception can be identified using the exception stack trace
> below.
> Stack Trace:
> [Exception: For more information about this error navigate to the
> report server on the local server machine, or enable remote errors]
> [Exception: An error occurred during rendering of the report.]
> [Exception: The operation has timed out. The report server has
> canceled the operation.]
> Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
> isAbortable, String url, Stream outputStream, String& mimeType,
> String& fileNameExtension) +489
> Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
> isAbortable, String format, String deviceInfo, NameValueCollection
> urlAccessParameters, Stream reportStream, String& mimeType, String&
> fileNameExtension) +958
> Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
> format, String deviceInfo, NameValueCollection additionalParams,
> String& mimeType, String& fileExtension) +84
> Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
> urlQuery, HttpResponse response) +143
> Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
> context) +75
> System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
> +154
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
> Boolean& completedSynchronously) +64
> ________________________________________
> Version Information: Microsoft .NET Framework Version:2.0.50727.42;
> ASP.NET Version:2.0.50727.210
> When I look at the log files on the server I find this error message:
> 2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
> - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
> GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
> ReportServer?%2fReports
> %2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
> - 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
> %20Mar%2030%2014%3A49%3A52%202007 - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
> POST /reports/ReportService2005.asmx - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
> DefaultAppPool
> 2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
I'm not sure about a limitation in MS Excel 2007; but, in MS Excel
2003, the limit was somewhere around 65000 rows. In my opinion, it
sounds like it might be too much data to stream into Excel. Sorry I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Gerating a xml file from oracle 9i in a asp program

Hi, is any of the user of that list works with Oracle9i ? because i need to generate xml file from a intranet asp site ... so i don't know where to find information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp program ...is someone did that ? how i can read a recorset if i use sqlxml of oracle ?
thanks in advance
cheers
jp
With SQL Server you could use the FOR XML clause to get the data out in XML
format. If you need help for the same, with Oracle, then please post this to
an Oracle newsgroup.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:20812732-5D8B-4374-8383-763B56F688AD@.microsoft.com...
Hi, is any of the user of that list works with Oracle9i ? because i need to
generate xml file from a intranet asp site ... so i don't know where to find
information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp
program ...is someone did that ? how i can read a recorset if i use sqlxml
of oracle ?
thanks in advance
cheers
jp

Generation of a report in background or asynchronously.?

Hello, I have a report that takes many seconds to generate, as I am making an spanish application I dont want the user to see that the report is being generating, I would like to put my own message and then show the report when its ready.

Any ideas?

If you are using the report in a windows application I would suggest using the new backgroundworker control. It is very easy to use and works great! You could also you the subscriptions feature to schedule large reports to run in off hours and be delivered automatically to a windows share or email.

|||I am using a web application not winforms|||Ajax may be what you are looking for?
http://ajax.schwarz-interactive.de/csharpsample/default.aspx
Another workaround may be to run the report in an Iframe that is very small, and then redirect to the exported html afterwards.

Generating XSD schema from an sql server table

I am using XSD schemas in .net but have to generate them manually.
Is it possible to automatically get the validation information from a table in SQL server?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
<chris4england> wrote in message
news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
>I am using XSD schemas in .net but have to generate them manually.
> Is it possible to automatically get the validation information from a
> table in SQL server?
You could probably write a stored procedure that generated XSD from the
sysobjects table, but it would be pretty complex and might not contain all
the validation you need.
This would be the only way that I know of. Part of the problem is that XSD's
generally contain information that isn't in SQL Server.
Bryant
|||In SQL Server 2005, you will be able to infer an XSD for the RAW and AUTO
modes in FOR XML.
E.g.,
select top 0 * from table for xml auto, xmlschema
In SQL Server 2000, you can generate an XDR schema in the following way
select top 0 * from table for xml auto, xmldata
and then use one of the XDR->XSD tools to generate the XSD from it.
Note that you probably still want to then edit the schema to add your own
constraints.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:u9ykRxblEHA.3104@.TK2MSFTNGP14.phx.gbl...
> <chris4england> wrote in message
> news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
> You could probably write a stored procedure that generated XSD from the
> sysobjects table, but it would be pretty complex and might not contain all
> the validation you need.
> This would be the only way that I know of. Part of the problem is that
> XSD's generally contain information that isn't in SQL Server.
> --
> Bryant
>

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

Generating unique nvarchar unique key

Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks

Generating unique id strings

Hi
I want to generate a unique record number in the following format
Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)

Could someone recommend how I go about generating this number?

Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?

... or should I just create a stored procedure to generate the number each time?

... or should I do something completely different?

Regards
John :confused:First, I'd recommend against doing this unless this code is required by the business process, i.e. recognized by the system users. Such codes are of little or no value to the application.

If all four of these values already exist in the table, then you could add a calculated column to your table that concatenates them to form the ID. These types of keys are often called "Superkeys", but with the availability of composite keys they are of little use these days and are often difficult to maintain.|||Thanks blindman, I'll take your advise and k.i.s.|||you could create a formula to concatenate the other fields and use it as the default value of a new field in the same row

I don't think I explained this very well

er

read about using formulas as default values in BOL

GW|||As a default value it would not update automatically if any of it's components change. That's the advantage of a calculated field.|||Apologies

U right of course blindman

I was'nt thinking

GW

Generating Unique Check Digits

Hi all,
I've implemented a small routine that generates "ID" numbers in hexadecimal
format. Does anyone know of any routines to generate a unique check digit
for a hexadecimal number? I've found several ways to do it in Base 10, but
not so much for hex. I really like the UCC mod 10(3) methods, but the check
digits are fairly linear in progression when I try to apply it to hex.
Here's an example of the types of ID's I'll be generating:
B951-D300-1E73-C1xx
Note that the dashes are just to "prettify it", and can be ignored for my
purposes. "xx" represents a placeholder for the 2 hexadecimal check digits.
B951D3001E73C1xx
Thanks. Any help is appreciated.Michael,
Do you need the check digit for more than
catching transcription or typing errors? Is there
a reason "fairly linear" in progression is not acceptable?
Steve Kass
Drew University
Michael C# wrote:

>Hi all,
>I've implemented a small routine that generates "ID" numbers in hexadecimal
>format. Does anyone know of any routines to generate a unique check digit
>for a hexadecimal number? I've found several ways to do it in Base 10, but
>not so much for hex. I really like the UCC mod 10(3) methods, but the chec
k
>digits are fairly linear in progression when I try to apply it to hex.
>Here's an example of the types of ID's I'll be generating:
>B951-D300-1E73-C1xx
>Note that the dashes are just to "prettify it", and can be ignored for my
>purposes. "xx" represents a placeholder for the 2 hexadecimal check digits
.
>B951D3001E73C1xx
>Thanks. Any help is appreciated.
>
>|||Like you mentioned, catching transcription errors is part of it; I also
don't want people to be able to reverse engineer and guess the previous ID
code; for instance, if the code ends with -C1B6, I don't want them to be
able to take a wild guess at -C0B5 being the previous ID.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:OYxYyK7SFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Michael,
> Do you need the check digit for more than
> catching transcription or typing errors? Is there
> a reason "fairly linear" in progression is not acceptable?
> Steve Kass
> Drew University
>
> Michael C# wrote:
>sql

generating sql string for execution

I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

To generate this: 'abcd' like 'a%'
& verify if the condition is satisfied I've done the following:

CREATE TABLE #Temp(Result varchar(10))
declare @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50),
@.expr as varchar(50),@.result as varchar(10)
set @.cond1 = '''abcd'''
set @.op = 'like'
set @.cond2 = '''a%'''
set @.expr = @.cond1 + ' ' + @.op + ' ' + @.cond2
insert into #temp exec ('select case when ' + @.expr + ' then ''true'' else ''false'' end')
if exists (select result from #temp where result = 'true')
select 'it is true'
else
select 'it is false'
drop table #temp

It works, but a bit clumsy. Is there a more elegant way to do this ?How about something mildly perverse like:DECLARE @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

EXECUTE ('SELECT ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END')The extra parentheses are just digital "seat belts" in case anything goes wrong cooking up your expression.

-PatP|||Thanks Pat, very creative.

An extension on that: how do I get the result of the execute into a variable ?

with a select one can do this:

declare @.temp as varchar(10)
select @.temp = (select 'abc')
print @.temp

but that doesn't work with an exec

Thanks
Colin|||Now we need to get "creative" to make that happen!DECLARE
@.cond1 AS VARCHAR(50)
, @.cond2 AS VARCHAR(50)
, @.cmd AS NVARCHAR(200)
, @.op AS VARCHAR(4)
, @.result AS NVARCHAR(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

SET @.cmd = 'SELECT @.i = ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END'

EXECUTE sp_executesql @.cmd, N'@.i NVARCHAR(50) OUTPUT ', @.result OUTPUT

SELECT @.result -- Just to show it worked-PatP

Generating SQL scripts for tables

Hi,
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
ThanksOn the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegroups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>

Generating SQL scripts for tables

Hi,
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
Thanks
On the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/
|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegr oups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>
sql

Generating SQL Scripts

Hi,
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.

This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
Here is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Sorry, my methods don't copy the data.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the[vbcol=seagreen]
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully
> best
> ----
> --
> ----
> --
> have
>
|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. www.dbghost.com
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
sql

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > Hi All
> >
> > I have SQL Data Base . I am using asp application to access this Data
> Base.
> >
> > I want to create a script to generate Data Base on other machines. I
have
> > some data in that Data Base, which I required to run my application.
> >
> > So the final SCRIPT should contain all relations, default values ,
> > Identities and Data.
> >
> > Which method you advise to achieve these.
> >
> >
> > Kind Regards
> >
> >
> >
> >
> >
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> > Here is an article I wrote about generating SQL Server scripts.
Hopefully
> > this will provide you with enough information to help you determine the
> best
> > way to generate your script.
> >
> > http://www.dbazine.com/larsen4.shtml
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> > news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > > Hi All
> > >
> > > I have SQL Data Base . I am using asp application to access this Data
> > Base.
> > >
> > > I want to create a script to generate Data Base on other machines. I
> have
> > > some data in that Data Base, which I required to run my application.
> > >
> > > So the final SCRIPT should contain all relations, default values ,
> > > Identities and Data.
> > >
> > > Which method you advise to achieve these.
> > >
> > >
> > > Kind Regards
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:
> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>