"Server Application Unavailable" error on generating PDF reports

Hi,
I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
Most of the time it works well.
But sometimes PDF doesn't come out (esp. if the report is complicated),
with the error message at below.
I use 2 separate servers for Web Server / Reporting Service.
And i suspect a cause is not enough memory?
Thanks,
Azul
-- error message --
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur.
--.: at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
at LYNX.ReportServices.ReportingService.Render(...) in ...
at LYNX.BLL.reportServices.getCustomReport(...) in ...
at LYNX.BLL.reportServices.getPDFReport(...) in ...
at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
-- end of error message --I found the solution from another post in this newsgroup!
Just downloaded and installed Reporting Services SP1, everything works fine
Although the earlier post talked about long long table,
the solution also applies in my case of generating complicated graphs.
"Azul" <a@.b.c> wrote in message
news:ebcX1F1qEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
> Most of the time it works well.
> But sometimes PDF doesn't come out (esp. if the report is complicated),
> with the error message at below.
> I use 2 separate servers for Web Server / Reporting Service.
> And i suspect a cause is not enough memory?
> Thanks,
> Azul
>
> -- error message --
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web
> server. Please review this log entry to discover what caused this error to
> occur.
> --.: at
> System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
> at LYNX.ReportServices.ReportingService.Render(...) in ...
> at LYNX.BLL.reportServices.getCustomReport(...) in ...
> at LYNX.BLL.reportServices.getPDFReport(...) in ...
> at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
> -- end of error message --
>

"Must declare variable" error caused by stored procedure

I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >

"Generate SQL Script" functionality

By default, if you use the "Generate SQL Script" menu item when
right-clicking on a table, SQL Server will generate a scripts such as:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Simple and straightforward, but presents a problem when the table exists and
contains data. Is there a way to modify SQL Server (e.g., is there a template
somewhere?) such that all future scripts generated using this functionality
would have a check to verify that the table does not contain data, such as in:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
exists (select count(*) from dbo.zztest having count(*) = 0))
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The above is just an example, if there is some other way to NOT drop the
table if if contains data please advise.
Thank you!
--
Brad AshforthAdd:
if count(*) > 0 from table
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Hi Jay ... thank you, but my question was not clear. I am looking for a way
to alter SQL Server such that when we use the "Script" function it would
automatically change the output script to inlude this test, which is already
in the code I posted.
This way we can avoid having to manually alter the scripts each time a table
is created/changed.
Thanks
--
Brad Ashforth
"Jay" wrote:
> Add:
> if count(*) > 0 from table
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> > By default, if you use the "Generate SQL Script" menu item when
> > right-clicking on a table, SQL Server will generate a scripts such as:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[zzTest]
> > GO
> >
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > Simple and straightforward, but presents a problem when the table exists
> > and
> > contains data. Is there a way to modify SQL Server (e.g., is there a
> > template
> > somewhere?) such that all future scripts generated using this
> > functionality
> > would have a check to verify that the table does not contain data, such as
> > in:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> > and
> > exists (select count(*) from dbo.zztest having count(*) = 0))
> > drop table [dbo].[zzTest]
> > GO
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > The above is just an example, if there is some other way to NOT drop the
> > table if if contains data please advise.
> >
> > Thank you!
> >
> > --
> > Brad Ashforth
>
>|||Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
particular feature is there, though. You can wish this for a future version:
http://connect.microsoft.com/sqlserver.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> Hi Jay ... thank you, but my question was not clear. I am looking for a way
> to alter SQL Server such that when we use the "Script" function it would
> automatically change the output script to inlude this test, which is already
> in the code I posted.
> This way we can avoid having to manually alter the scripts each time a table
> is created/changed.
> Thanks
> --
> Brad Ashforth
>
> "Jay" wrote:
>> Add:
>> if count(*) > 0 from table
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> > By default, if you use the "Generate SQL Script" menu item when
>> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> > drop table [dbo].[zzTest]
>> > GO
>> >
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > Simple and straightforward, but presents a problem when the table exists
>> > and
>> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> > template
>> > somewhere?) such that all future scripts generated using this
>> > functionality
>> > would have a check to verify that the table does not contain data, such as
>> > in:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> > and
>> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> > drop table [dbo].[zzTest]
>> > GO
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > The above is just an example, if there is some other way to NOT drop the
>> > table if if contains data please advise.
>> >
>> > Thank you!
>> >
>> > --
>> > Brad Ashforth
>>|||Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
2000, where I did not find anything under Tools/Options. I'll send it on as a
wish ... thank you!
--
Brad Ashforth
"Tibor Karaszi" wrote:
> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
> particular feature is there, though. You can wish this for a future version:
> http://connect.microsoft.com/sqlserver.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
> > to alter SQL Server such that when we use the "Script" function it would
> > automatically change the output script to inlude this test, which is already
> > in the code I posted.
> >
> > This way we can avoid having to manually alter the scripts each time a table
> > is created/changed.
> >
> > Thanks
> > --
> > Brad Ashforth
> >
> >
> > "Jay" wrote:
> >
> >> Add:
> >>
> >> if count(*) > 0 from table
> >>
> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> >> > By default, if you use the "Generate SQL Script" menu item when
> >> > right-clicking on a table, SQL Server will generate a scripts such as:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> >
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > Simple and straightforward, but presents a problem when the table exists
> >> > and
> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
> >> > template
> >> > somewhere?) such that all future scripts generated using this
> >> > functionality
> >> > would have a check to verify that the table does not contain data, such as
> >> > in:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> >> > and
> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > The above is just an example, if there is some other way to NOT drop the
> >> > table if if contains data please advise.
> >> >
> >> > Thank you!
> >> >
> >> > --
> >> > Brad Ashforth
> >>
> >>
> >>
>
>|||Sorry, I'm referring to 2005. No such option in 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:396AC362-1D58-4E5B-8E16-A106DB86A522@.microsoft.com...
> Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
> 2000, where I did not find anything under Tools/Options. I'll send it on as a
> wish ... thank you!
> --
> Brad Ashforth
>
> "Tibor Karaszi" wrote:
>> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that
>> this
>> particular feature is there, though. You can wish this for a future version:
>> http://connect.microsoft.com/sqlserver.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
>> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
>> > to alter SQL Server such that when we use the "Script" function it would
>> > automatically change the output script to inlude this test, which is already
>> > in the code I posted.
>> >
>> > This way we can avoid having to manually alter the scripts each time a table
>> > is created/changed.
>> >
>> > Thanks
>> > --
>> > Brad Ashforth
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> Add:
>> >>
>> >> if count(*) > 0 from table
>> >>
>> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> >> > By default, if you use the "Generate SQL Script" menu item when
>> >> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > Simple and straightforward, but presents a problem when the table exists
>> >> > and
>> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> >> > template
>> >> > somewhere?) such that all future scripts generated using this
>> >> > functionality
>> >> > would have a check to verify that the table does not contain data, such as
>> >> > in:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> >> > and
>> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > The above is just an example, if there is some other way to NOT drop the
>> >> > table if if contains data please advise.
>> >> >
>> >> > Thank you!
>> >> >
>> >> > --
>> >> > Brad Ashforth
>> >>
>> >>
>> >>
>>|||Why are you manually scripting tables and then running the DDL on your
server?
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Here is a bit of T-SQL code from a Data Dictionary program I'm writing.
Shouldn't be too hard to make it produce basic DDL.
--USE msdb Change to DB you want. The proc will be stored there too.
IF EXISTS (SELECT * FROM dbo.sysobjects where id =object_id(N'[dbo].[ScriptTable]') and OBJECTPROPERTY(id, N'IsProcedure') =1)
DROP PROCEDURE [dbo].[ScriptTable]
GO
CREATE PROCEDURE [ScriptTable]
AS
DECLARE @.Table SYSNAME
DECLARE @.TableID INT
DECLARE @.Column SYSNAME
DECLARE @.ColID SMALLINT
DECLARE @.VarType TINYINT
DECLARE @.VarName SYSNAME
DECLARE @.VarLen INT
DECLARE @.ColStatus TINYINT
DECLARE @.StatusDesc CHAR(8)
DECLARE @.IsNullable INT
DECLARE @.ConstraintName SYSNAME
DECLARE @.ColumnDefault CHAR(50)
DECLARE @.Pad1 CHAR(5)
DECLARE @.Pad2 CHAR(5)
DECLARE @.ColNameLen SMALLINT
DECLARE @.ObjName SYSNAME
DECLARE @.ObjType CHAR(4) -- Yes I know it's 2
DECLARE @.PKFK CHAR(11)
DECLARE @.DispPKFK CHAR(6)
DECLARE cTables CURSOR FOR
SELECT so.name, so.id
FROM sysobjects so
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
ORDER BY so.name
OPEN cTables
FETCH FROM cTables INTO @.Table, @.TableID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT 'Table: ' + RTRIM(CONVERT(CHAR(30), @.Table)) --+ ' id: ' +
CONVERT(CHAR(12), @.TableID)
PRINT 'Stored procedures (P), Triggers (TR) and Views (V) that access: "' +
RTRIM(@.Table) + '"'
PRINT 'Type Name'
PRINT '-- --'
DECLARE cProcedures CURSOR FOR
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
OPEN cProcedures
FETCH cProcedures INTO @.ObjName, @.ObjType
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT @.ObjType + ' ' + CONVERT(CHAR(40), @.ObjName)
FETCH cProcedures INTO @.ObjName, @.ObjType
END
CLOSE cProcedures
DEALLOCATE cProcedures
PRINT ''
PRINT 'PK/FK Column Null Type Default Constraint Comment'
PRINT
'-- -- -- -- -- -- --'
DECLARE cColumns CURSOR FOR
SELECT sc.name, sc.colid, st.name, sc.length, sc.xtype, sc.status,
sc.isnullable
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE id = @.TableID
ORDER BY sc.colorder
OPEN cColumns
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Get the column constraint (if available)
SET @.ConstraintName = ''
SELECT @.ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
-- Get the default value
SET @.ColumnDefault = ' '
SELECT @.ColumnDefault = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
IF @.ColumnDefault IN ('('''')', 'NULL') SET @.ColumnDefault = ' '
IF @.ColumnDefault IS NULL
SET @.ColumnDefault = ' '
ELSE
BEGIN
IF CHARINDEX('(', @.ColumnDefault, 1) = 1
SET @.ColumnDefault = SUBSTRING(@.ColumnDefault, 2, LEN(@.ColumnDefault)-2)
END
-- Find IDENTITY, NOT NULL
SET @.StatusDesc = ' '
IF @.ColStatus = 0
SET @.StatusDesc = ' '
IF @.ColStatus = 128
SET @.StatusDesc = 'IDENTITY'
ELSE IF @.IsNullable = 0
SET @.StatusDesc = 'NOT NULL'
-- Define the pad
SET @.ColNameLen = LEN(RTRIM(@.Column))
IF @.ColNameLen >= 24
SET @.Pad1 = ''
ELSE IF @.ColNameLen >= 16
SET @.Pad1 = ' '
ELSE IF @.ColNameLen >= 8
SET @.Pad1 = ' '
ELSE
SET @.Pad1 = ' '
SET @.ColNameLen = LEN(RTRIM(@.VarName))
IF @.VarType IN (175, 167)
BEGIN
SET @.Pad2 = ''
END
ELSE
BEGIN
IF @.ColNameLen >= 16
SET @.Pad2 = ''
ELSE IF @.ColNameLen >= 8
SET @.Pad2 = ''
ELSE
SET @.Pad2 = ' '
END
-- Is PKFK?
DECLARE cPKFK CURSOR FOR
SELECT TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME =KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
ORDER BY TC.CONSTRAINT_TYPE DESC
OPEN cPKFK
SET @.DispPKFK = ''
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- PRINT '[' + @.PKFK + ']'
IF @.PKFK = 'PRIMARY KEY' SET @.DispPKFK = 'PK'
IF @.PKFK = 'FOREIGN KEY' SET @.DispPKFK = ' FK'
IF @.PKFK = 'UNIQUE ' SET @.DispPKFK = ' U'
-- PRINT 'XXX[' + @.DispPKFK + ']XXX'
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
END
CLOSE cPKFK
DEALLOCATE cPKFK
IF @.VarType IN (175, 167)
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ UPPER(RTRIM(CONVERT(CHAR(20), @.VarName))) + '('
+ RTRIM(CONVERT(CHAR(5), @.VarLen)) + ')' + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + ' s: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
ELSE
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ RTRIM(UPPER(CONVERT(CHAR(20), @.VarName))) + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + 's: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
END
PRINT ''
CLOSE cColumns
DEALLOCATE cColumns
--BREAK
FETCH FROM cTables INTO @.Table, @.TableID
PRINT
'==============================================================================================================================='
PRINT ''
PRINT ''
END
CLOSE cTables
DEALLOCATE cTables
GO
EXECUTE [ScriptTable]
/*
*/|||Hi Brad,
This feature is not included in both SQL Server 2000 SP4 and SQL Server
2005 SP2. I agree with Tibor that you give Microsoft feedback so that your
voice will be heard by the product team and hope that this feature can be
included in the next release of SQL Server. Appreciate your understanding
that by the original design of SQL Server, it could not think all the
aspects of customers needs. Your feedback is a great impetus to empower
Microsoft product to become better and better.
Now if you are very concerned with this issue, you may consider developing
a tool by yourself to do this. Per my experiences, this should not be a
hard work.
If you have any other questions or concerns, please feel free to let us
know. Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================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.
======================================================|||Here's an open source console app you could easily modify to give you
want you are looking for:
http://www.codeplex.com/scriptdb
It will work against any 2000 or 2005 db, requires SMO to be installed
on the client.
On Sep 5, 6:38 am, Brad Ashforth <banos...@.nospam.nospam> wrote:
> By default, if you use the "GenerateSQLScript" menu item when
> right-clicking on a table,SQLServer willgeneratea scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists and
> contains data. Is there a way to modifySQLServer (e.g., is there a template
> somewhere?) such that all future scripts generated using this functionality
> would have a check to verify that the table does not contain data, such as in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth

"Failed to generate a user instance of SQL Server" using sql server express and vwde. why?

I've set up a website on another computer using sql server express and visual web developer express, and it works as expected. However halfway through the development i've had to move to another computer. I installed visual web developer express + sql server express, and copied the project files across, but now when I attempt to run it I get the error:

Failed to generate a user instance of SQL Server due to afailure in starting the process for the user instance. The connectionwill be closed.

I've tried reinstalling the software and it doesn't help. Any idea why this is happening, and what I can do to fix it?

thanks

How to fix it is covered in the link below change the configuration. Hope this helps.

http://blog.krisvandermast.com/FailedToGenerateAUserInstanceOfSQLServerDueToAFailureInStartingTheProcessForTheUserInstanceTheConnectionWillBeClosed.aspx

|||

Hi,

I changed the built in account to 'local system' in the SQL server configuration manager, and deleted my SQLEXPRESS folder, then restarted the computer, but the same thing is happening. Is there anything else I need to do to fix this?

thanks for your help

|||

I am sorry you error sent me on the wrong track because moving a database created in one box and moving it to another is not a simple copying files over. That is the reason the solution I gave you did not fix your problem so delete the folder. When you are moving your database to a new computer you need to do it manually through code like the second link below. You need to check your access permissions to the new computer and follow the guide created by Microsoft in the second link if the forum posting in the first link below did not fix your problem. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

"Failed to generate a user instance of SQL Server" - Redux

I have SQL Server 2005 SP1 Dev Edition installed on my laptop along with Visual Web Developer 2005. I installed SQL Server Express 2005 SP1. I then created a new web site using the Club Web Site template.

I did the recommended initial build to get the Membership database installed and got an error page (http://localhost:1667/WebSitesVB2/):

SQLExpress database file auto-creation error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

There were 4 recomme4ndations from the error page. I have met three of them: (1) the AppDaa folder that was created when I used the Club Web Site template; (2) my machine's ASPNET user set up with full control permissions on the APP Data Folder; (3) SQL Server Express successfully installed on my machine.

The 4th recommendation from the error page is "The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. ". I have no idea of how to do this or how/where to find the elusive the "readme" file.

I am at a loss as to what to do next. I have spent the day going over forums on this error and none seem to provide consistent or relevant answers.

Try to remove User Instance=true from your connection string, if you don't need to generate a user instance at runtime. For more information about the user instance attribute, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx|||

Changed userinstance = TRUE to userinstance=FALSE. Same result - failure to generate userinstance.

Is it possible that there is some setting in VWD that provides a connection string for the ASPNET db that is to be created? The only connection string in the WebConfig from the original template is the one for the ClubSite DB. There is none for the ASPNET db (because the template does not provide the ASPNET DB.

After 2 tries, I still get this error and when I look at the database explorer, there is only one DB file: Club.MDF.

Anyone else have any answers?

|||

I probably should have included this stack trace:

Stack Trace:

[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1084 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +355 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +91 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +115 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +110[HttpException (0x80004005): Unable to connect to SQL Server database.] System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +181 System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +167 System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +46 System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +681

---

|||Sorry I forgot to mention: there is a connection string named LocalSqlServer, which is used by .NET 2.0 built-in providers (Profile, Membership, RoleManager, etc.). You can override it and try again, for details you can refer to:
Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

"Cannot Generate SSPI Context" for the server itself

Basically the windows authentication is not working at all on this newly
installed sql 2k on win 2k, even it is not coming up at the server itself.
The Authentication is on the mix mode but only sql authentication works. It
is interesting when I use the sa (at the server) to connect it shows few
databases (excluding master, temp, msdb,…) and when I login locally as admin
to the box using sa it shows all dbs. This machine is connected to the SAN
but it is not clustered. The identical server at the disaster site does not
have this problem!
I looked at articles but they are all refer to a computer connecting to the
server. The admin who installed this left the organization and I have no idea
how he did the installation or even if he renamed this sql server after the
installation.
Any ideas where I should look?
Thank you,
I take the part with system databases not shown back.
The Authentication problem exist when the server is on the netwrok. Anywhere
I should look?
Thank you.
"ktf" wrote:

> Basically the windows authentication is not working at all on this newly
> installed sql 2k on win 2k, even it is not coming up at the server itself.
> The Authentication is on the mix mode but only sql authentication works. It
> is interesting when I use the sa (at the server) to connect it shows few
> databases (excluding master, temp, msdb,…) and when I login locally as admin
> to the box using sa it shows all dbs. This machine is connected to the SAN
> but it is not clustered. The identical server at the disaster site does not
> have this problem!
> I looked at articles but they are all refer to a computer connecting to the
> server. The admin who installed this left the organization and I have no idea
> how he did the installation or even if he renamed this sql server after the
> installation.
> Any ideas where I should look?
> Thank you,
|||FYI,
I added 'Named Pipes' into Client Network Utility and it fixed my problem.
ktf
"ktf" wrote:
[vbcol=seagreen]
> I take the part with system databases not shown back.
> The Authentication problem exist when the server is on the netwrok. Anywhere
> I should look?
> Thank you.
> "ktf" wrote:

"Cannot Generate SSPI Context" for the server itself

Basically the windows authentication is not working at all on this newly
installed sql 2k on win 2k, even it is not coming up at the server itself.
The Authentication is on the mix mode but only sql authentication works. It
is interesting when I use the sa (at the server) to connect it shows few
databases (excluding master, temp, msdb,â?¦) and when I login locally as admin
to the box using sa it shows all dbs. This machine is connected to the SAN
but it is not clustered. The identical server at the disaster site does not
have this problem!
I looked at articles but they are all refer to a computer connecting to the
server. The admin who installed this left the organization and I have no idea
how he did the installation or even if he renamed this sql server after the
installation.
Any ideas where I should look?
Thank you,I take the part with system databases not shown back.
The Authentication problem exist when the server is on the netwrok. Anywhere
I should look?
Thank you.
"ktf" wrote:
> Basically the windows authentication is not working at all on this newly
> installed sql 2k on win 2k, even it is not coming up at the server itself.
> The Authentication is on the mix mode but only sql authentication works. It
> is interesting when I use the sa (at the server) to connect it shows few
> databases (excluding master, temp, msdb,â?¦) and when I login locally as admin
> to the box using sa it shows all dbs. This machine is connected to the SAN
> but it is not clustered. The identical server at the disaster site does not
> have this problem!
> I looked at articles but they are all refer to a computer connecting to the
> server. The admin who installed this left the organization and I have no idea
> how he did the installation or even if he renamed this sql server after the
> installation.
> Any ideas where I should look?
> Thank you,|||FYI,
I added 'Named Pipes' into Client Network Utility and it fixed my problem.
ktf
"ktf" wrote:
> I take the part with system databases not shown back.
> The Authentication problem exist when the server is on the netwrok. Anywhere
> I should look?
> Thank you.
> "ktf" wrote:
> > Basically the windows authentication is not working at all on this newly
> > installed sql 2k on win 2k, even it is not coming up at the server itself.
> > The Authentication is on the mix mode but only sql authentication works. It
> > is interesting when I use the sa (at the server) to connect it shows few
> > databases (excluding master, temp, msdb,â?¦) and when I login locally as admin
> > to the box using sa it shows all dbs. This machine is connected to the SAN
> > but it is not clustered. The identical server at the disaster site does not
> > have this problem!
> > I looked at articles but they are all refer to a computer connecting to the
> > server. The admin who installed this left the organization and I have no idea
> > how he did the installation or even if he renamed this sql server after the
> > installation.
> > Any ideas where I should look?
> > Thank you,

"Cannot Generate SSPI Context" for the server itself

Basically the windows authentication is not working at all on this newly
installed sql 2k on win 2k, even it is not coming up at the server itself.
The Authentication is on the mix mode but only sql authentication works. It
is interesting when I use the sa (at the server) to connect it shows few
databases (excluding master, temp, msdb,…) and when I login locally as adm
in
to the box using sa it shows all dbs. This machine is connected to the SAN
but it is not clustered. The identical server at the disaster site does not
have this problem!
I looked at articles but they are all refer to a computer connecting to the
server. The admin who installed this left the organization and I have no ide
a
how he did the installation or even if he renamed this sql server after the
installation.
Any ideas where I should look?
Thank you,I take the part with system databases not shown back.
The Authentication problem exist when the server is on the netwrok. Anywhere
I should look?
Thank you.
"ktf" wrote:

> Basically the windows authentication is not working at all on this newly
> installed sql 2k on win 2k, even it is not coming up at the server itself.
> The Authentication is on the mix mode but only sql authentication works. I
t
> is interesting when I use the sa (at the server) to connect it shows few
> databases (excluding master, temp, msdb,…) and when I login locally as a
dmin
> to the box using sa it shows all dbs. This machine is connected to the SAN
> but it is not clustered. The identical server at the disaster site does no
t
> have this problem!
> I looked at articles but they are all refer to a computer connecting to th
e
> server. The admin who installed this left the organization and I have no i
dea
> how he did the installation or even if he renamed this sql server after th
e
> installation.
> Any ideas where I should look?
> Thank you,|||FYI,
I added 'Named Pipes' into Client Network Utility and it fixed my problem.
ktf
"ktf" wrote:
[vbcol=seagreen]
> I take the part with system databases not shown back.
> The Authentication problem exist when the server is on the netwrok. Anywhe
re
> I should look?
> Thank you.
> "ktf" wrote:
>

"Cannot generate SSPI context" error message in SQL 2005 Beta 2

executing dimension process, the system return an error :
- Cannot generate SSPI context"
I'm not understand where is the problem....all config parameters are
correct...
What could be the reason of this?
my system configuration
Win XP SP2 Pro Ed.
SQL 2005 Beta 2
SQL Server 2005 CTP/Beta questions to
http://communities.microsoft.com/new...r2005&slcid=us
please.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmarchi" <cmarchi@.discussions.microsoft.com> wrote in message
news:7FF32AE8-3632-40C9-BA97-7D2116606D42@.microsoft.com...
> executing dimension process, the system return an error :
> - Cannot generate SSPI context"
> I'm not understand where is the problem....all config parameters are
> correct...
> What could be the reason of this?
> my system configuration
> Win XP SP2 Pro Ed.
> SQL 2005 Beta 2

"Cannot generate SSPI context" error message in SQL 2005 Beta 2

executing dimension process, the system return an error :
- Cannot generate SSPI context"
I'm not understand where is the problem....all config parameters are
correct...
What could be the reason of this?
my system configuration
Win XP SP2 Pro Ed.
SQL 2005 Beta 2SQL Server 2005 CTP/Beta questions to
http://communities.microsoft.com/ne...lcid=us

please.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmarchi" <cmarchi@.discussions.microsoft.com> wrote in message
news:7FF32AE8-3632-40C9-BA97-7D2116606D42@.microsoft.com...
> executing dimension process, the system return an error :
> - Cannot generate SSPI context"
> I'm not understand where is the problem....all config parameters are
> correct...
> What could be the reason of this?
> my system configuration
> Win XP SP2 Pro Ed.
> SQL 2005 Beta 2