Dynamic Create Table in Stored Procedure

Hi,
Sorry, I know this is a very easy question.
I as a amature SQL learner because of my college project have to create a stored procedure in a SQL SERVER Database with this condition :
-get Columns and datatypes as parameters like this :
col1,datatupe1,col2,datatype2,...
-create table with this properties ... .
I have some problem about syntax. :o
I would appreciate your help.Why don't you cut and paste the assignment as is so we can all see it.

Better yet, why don't you go to a lab and ask for help?

I find it very hard to believe that the assignment is to build objects dynamically.

What school did you say this was?

Dynamic create table

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....

Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address

Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

You need to create your "CREATE TABLE.." statement dynamically. How would a user select the columns? If you dont know the column names do you know what datatype they should be?|||

Yeah I know the DataType they are all of float datatype...

But the rows in TableA may change so I need to create a temporary table with the column names with how many ever rows are available in the TableA.

Eventually in the stored proc I am going to drop this Temporary table after selecting the Data from it....

|||Can you please let me know if i can create a table by using both as select and some already knowm column names|||

something like that ?

declare @.sql nvarchar(2000)

set @.sql = N'create table myTempTable ( id int identity, name varchar(100) );'

execute sp_executesql @.sql

|||

You can try this .It's working correctly.

Create proc prcTempTab

AS
SET NOCOUNT ON
declare @.j int
set @.j=1
declare @.strtemp varchar(1000)
declare @.c varchar(4)
Create table #temptab(i int)
while(@.j<4)
begin
set @.c='a'+convert(varchar(2),@.j)
set @.strtemp='ALTER TABLE #temptab ADD '+@.c+' int'
set @.j=@.j+1
exec (@.strtemp)
end

insert into #temptab values(1,2,3,4)
select * from #temptab

--exec prcTempTab

All the best

|||Good work Sarika, appreciable

Dynamic create table

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....

Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address

Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

You need to create your "CREATE TABLE.." statement dynamically. How would a user select the columns? If you dont know the column names do you know what datatype they should be?|||

Yeah I know the DataType they are all of float datatype...

But the rows in TableA may change so I need to create a temporary table with the column names with how many ever rows are available in the TableA.

Eventually in the stored proc I am going to drop this Temporary table after selecting the Data from it....

|||Can you please let me know if i can create a table by using both as select and some already knowm column names|||

something like that ?

declare @.sql nvarchar(2000)

set @.sql = N'create table myTempTable ( id int identity, name varchar(100) );'

execute sp_executesql @.sql

|||

You can try this .It's working correctly.

Create proc prcTempTab

AS
SET NOCOUNT ON
declare @.j int
set @.j=1
declare @.strtemp varchar(1000)
declare @.c varchar(4)
Create table #temptab(i int)
while(@.j<4)
begin
set @.c='a'+convert(varchar(2),@.j)
set @.strtemp='ALTER TABLE #temptab ADD '+@.c+' int'
set @.j=@.j+1
exec (@.strtemp)
end

insert into #temptab values(1,2,3,4)
select * from #temptab

--exec prcTempTab

All the best

|||Good work Sarika, appreciable

Dynamic controls in report prompt

What I would like to do is have a drop down that would basically have a list of countries. When the user selects the USA, another drop down in the report prompt would have a filtered list of states in the USA. Finally, the user would select a state and a third drop down would have a list of cities. Obviously this would need to be done via post backs. Bottomline is can this be done. Can the report prompt be more dynamic. If not I will need to develope some aspx report prompt and pass the data, which I want to avoid. PLEASE HELP!That can be done with cascading parameters.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AG" <AG@.discussions.microsoft.com> wrote in message
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
> list of countries. When the user selects the USA, another drop down in
> the report prompt would have a filtered list of states in the USA.
> Finally, the user would select a state and a third drop down would have a
> list of cities. Obviously this would need to be done via post backs.
> Bottomline is can this be done. Can the report prompt be more dynamic. If
> not I will need to develope some aspx report prompt and pass the data,
> which I want to avoid. PLEASE HELP!|||"Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> That can be done with cascading parameters.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
Could you please clarify on what database the description is based? Neither
in Northwind nor in Advantureworks is a table named Divisions.
r.|||Yes, this uses an imaginary database example. :) The documentation folks
have fixed this for next release.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:uHB10GtVEHA.1952@.TK2MSFTNGP12.phx.gbl...
> "Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
> news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > That can be done with cascading parameters.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Could you please clarify on what database the description is based?
Neither
> in Northwind nor in Advantureworks is a table named Divisions.
> r.
>|||Actually the easy way is the postback method.
But you can develop your own javascript code to do this job, but I think its
harder to code and to debug.
Why the postback cause a problem for you?
"AG" <AG@.discussions.microsoft.com> a écrit dans le message de
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
list of countries. When the user selects the USA, another drop down in the
report prompt would have a filtered list of states in the USA. Finally, the
user would select a state and a third drop down would have a list of cities.
Obviously this would need to be done via post backs. Bottomline is can this
be done. Can the report prompt be more dynamic. If not I will need to
develope some aspx report prompt and pass the data, which I want to avoid.
PLEASE HELP!

Dynamic Connectionstring and Email delivery

Hi,
Im new to reporting services.
My requirement is to develop only one set of reports and to pass the
connectionstring dynamically based on the user logs in.I used the
setdatasourcecontents API as it overwrites the connectionstring and Im
passing it just before the retrieval of the reports.Can anyone suggest me
whether it is the best way or is there any other way to acheive this.
Also the user will have an option of subscribing to a report and he can
specify a scheduled time for that.As the report runs just before the delivery
and Im Overwriting the datasource the mail will be sent with wrong report
data.So,please help me in solving this.
Im using standard edition.
Thanks..What are you trying to accomplish with this? Is it a matter of several
databases on the same server and based on the user you are allowing them to
see data from that database?
What you are doing is very problematic (as you have seen) but there might be
some other solution for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> Hi,
> Im new to reporting services.
> My requirement is to develop only one set of reports and to pass the
> connectionstring dynamically based on the user logs in.I used the
> setdatasourcecontents API as it overwrites the connectionstring and Im
> passing it just before the retrieval of the reports.Can anyone suggest me
> whether it is the best way or is there any other way to acheive this.
> Also the user will have an option of subscribing to a report and he can
> specify a scheduled time for that.As the report runs just before the
> delivery
> and Im Overwriting the datasource the mail will be sent with wrong report
> data.So,please help me in solving this.
>
> Im using standard edition.
> Thanks..
>|||Hello Bruce,
Thanks for your relply,I have seen some articles and custom data extension
might be a solution for this,and I don't know how to implement this and
whether it solves our email issue.
Definitely "Setdatasourcecontents" is not a solution as it applies changes
globally.
Our client have different companies wherein the database structure is
same.So I can create the reports for one company and pass the connection
string based on the employee login,so that he can view the reports specific
to his company.
Also when an employee subscribes for a report,how to pass the connection
string at the time of delivery of the report so that he receives his
company's report data.
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish with this? Is it a matter of several
> databases on the same server and based on the user you are allowing them to
> see data from that database?
> What you are doing is very problematic (as you have seen) but there might be
> some other solution for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "GK" <GK@.discussions.microsoft.com> wrote in message
> news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > Hi,
> > Im new to reporting services.
> > My requirement is to develop only one set of reports and to pass the
> > connectionstring dynamically based on the user logs in.I used the
> > setdatasourcecontents API as it overwrites the connectionstring and Im
> > passing it just before the retrieval of the reports.Can anyone suggest me
> > whether it is the best way or is there any other way to acheive this.
> > Also the user will have an option of subscribing to a report and he can
> > specify a scheduled time for that.As the report runs just before the
> > delivery
> > and Im Overwriting the datasource the mail will be sent with wrong report
> > data.So,please help me in solving this.
> >
> >
> > Im using standard edition.
> >
> > Thanks..
> >
> >
>
>|||There are several solutions for this with one exception. The problem is with
subscriptions. There is the User!userid global variable which will work for
everything but a subscription. The subscription will not know who to run as.
I have trouble seeing how to get around that even for a custom data
extension.
Leaving aside subscriptions you can solve the problem without a custom data
extension. Have a table that maps userid to database. Could also use dynamic
sql in Reporting Services but it would be cleaner with Stored Procedures.
Pass the User!Userid to the SP. Look up the database to use and then
assemble your SQL string to use it and execute it. This is clean and cannot
be modified by the user. It does not, however deal with subscriptions. I
don't have a good solution for you for that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:F76DFED5-42A9-4351-9284-E82370257FCB@.microsoft.com...
> Hello Bruce,
> Thanks for your relply,I have seen some articles and custom data extension
> might be a solution for this,and I don't know how to implement this and
> whether it solves our email issue.
> Definitely "Setdatasourcecontents" is not a solution as it applies changes
> globally.
> Our client have different companies wherein the database structure is
> same.So I can create the reports for one company and pass the connection
> string based on the employee login,so that he can view the reports
specific
> to his company.
> Also when an employee subscribes for a report,how to pass the connection
> string at the time of delivery of the report so that he receives his
> company's report data.
>
> "Bruce L-C [MVP]" wrote:
> > What are you trying to accomplish with this? Is it a matter of several
> > databases on the same server and based on the user you are allowing them
to
> > see data from that database?
> >
> > What you are doing is very problematic (as you have seen) but there
might be
> > some other solution for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "GK" <GK@.discussions.microsoft.com> wrote in message
> > news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > > Hi,
> > > Im new to reporting services.
> > > My requirement is to develop only one set of reports and to pass the
> > > connectionstring dynamically based on the user logs in.I used the
> > > setdatasourcecontents API as it overwrites the connectionstring and Im
> > > passing it just before the retrieval of the reports.Can anyone suggest
me
> > > whether it is the best way or is there any other way to acheive this.
> > > Also the user will have an option of subscribing to a report and he
can
> > > specify a scheduled time for that.As the report runs just before the
> > > delivery
> > > and Im Overwriting the datasource the mail will be sent with wrong
report
> > > data.So,please help me in solving this.
> > >
> > >
> > > Im using standard edition.
> > >
> > > Thanks..
> > >
> > >
> >
> >
> >

Dynamic connections rsInvalidDataSourceCredentialSetting

Hello,

Im having a few issues with a report this should use dynamic connections.

I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.

This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

The format of connection string i am using is :

Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"

I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.

as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error

I have also tried it on another server and get the same problem.

Any suggestions?

Thanks

Paul

Did you find an answer for this? I have the same problem.|||

No ive tried a number of different work arrounds but no luck

Just coming to terms with the fact i may have to have 10 reports instead of the 1

|||

Hello,

I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.

To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.

Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.

Regards,

Sonu.

|||

Added an execution account and the report runs like a dream!!

Cheers

Paul

Dynamic connections rsInvalidDataSourceCredentialSetting

Hello,

Im having a few issues with a report this should use dynamic connections.

I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.

This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

The format of connection string i am using is :

Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"

I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.

as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error

I have also tried it on another server and get the same problem.

Any suggestions?

Thanks

Paul

Did you find an answer for this? I have the same problem.|||

No ive tried a number of different work arrounds but no luck

Just coming to terms with the fact i may have to have 10 reports instead of the 1

|||

Hello,

I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.

To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.

Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.

Regards,

Sonu.

|||

Added an execution account and the report runs like a dream!!

Cheers

Paul