Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a table programatically!

Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@.Name nvarchar(25))
AS
CREATE TABLE [dbo].[@.Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@.Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.

How can I solve this problem.

Any help is appreciated!
Krish ChandraYou can use dynamic SQL to accomplish this. We wrote an article on ithere.

Creating a new variable with SQL Server Analysis Services

Hi!

I have just started working with SQL Server Analysis Services and I have already expierenced some problems:
I am working with the Adventure Works data and I want to create a new variable (Customer Value) out of the following data:

"Customer ID" and "Order Number"

I.e. a customer with the "customer ID" 00001 has ordered two products (so two order numbers are linked to this customer id) --> the new variable should identify the customer as a "C" customer.

therefore -->

"D" customer value: 0 orders
"C" customer value: 1-2 orders
"B" customer value: 3-4 orders
"A" customer value: >5 orders

Do you have any ideas to solve this problem?

Thanks

Cemens

Hello Cemens,

Do you want to create another attribute for the dimension Customers? Then I would suggest to create a named calculation in the DataSourceView (DSV) of your project with SQL

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a DECLARE variable with SMO

How can I create this stored proc with the SMO StoredProcedure class?

It doesn't want to work and I think it has something to do with the DECLARE statement. Anyone know how I can get this to work

Cheers
Jon

storedProc.TextBody = "DECLARE @.GroupID int"+

"SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")"+

"INSERT INTO gworkshop.Users"+

"(GroupID, Username, Password, Active, Deleted)"+

"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";

What does the rest of your code to create the procedure look like? Are you trying to create it and it's failing? If so, what's the error message?|||

I'm not in work right now so can't paste the code but basically its creating a server then the database name then in the storedprocedure I add parameters like this

StoredProcedure.Parameters.Add(new StoredProcedureParameter(storedprocedure,"@.Name", DataType.VarChar(30)));

The error is something like "There was an error creating stored procedure", then the procedure name

|||

If you are debugging, you can break on the exception it's throwing, then look at the inner exception(s) of that exception to find out exactly what the server is not liking about your stored procedure. Also, you can look at the script of the stored procedure ( use the script function), and look at the syntax it generates for you.

Whenever I create a stored procedure, I always do it like so:

Microsoft.SqlServer.Management.Smo.StoredProcedure proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure(database, procName);

proc.AnsiNullsStatus = true;

proc.QuotedIdentifierStatus = true;

proc.TextMode = false;

// Insert Params

Microsoft.SqlServer.Management.Smo.StoredProcedureParameter param = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(proc, "@." + paramName, paramDataType);

// If it's an output

param.IsOutputParameter = true;

proc.Parameters.Add(param);

// Now for the body

proc.TextMode = true;

proc.TextBody = "SELECT * FROM foo";

proc.Create();

|||

It seems that the problem was with the DECLARE @.GroupID

You need to add any parameters to the stored procedure, you can't add them in the SQL

storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Username", DataType.VarChar(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Password", DataType.Binary(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Active", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Deleted", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.GroupID", DataType.Int));

//storedProc.TextBody = "DECLARE @.GroupID int" +
storedProc.TextBody = "SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")" +
"INSERT INTO gworkshop.Users" +
"(GroupID, Username, Password, Active, Deleted)" +
"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";
storedProc.Create();

Creating a cached instance of a report for all variable values

I have a report that takes quite some time to render.
So I would like to create cached-instances of the report for each possible
variable value.
I suppose this is a rather common problem. Is there a solution (script,
program) available somwhere to do this?
(I've tried some things with the scripts but I can't get it to work, I keep
geeting "timed out' errors (although de report execution is set not to time
out) or security exeptions (althoug my user is a RS system user with all the
authoroty))
Thank youDid you ever figure out how to do this?
We thought that creating a data-driven subscription that dumped the report
to a file share when the report was setup to be cached would cache all
possible versions of the report, but we're finding that it's not caching
those executions and the report is being rendered on the first request.
Any thoughts?
Thx, Joel
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
>I have a report that takes quite some time to render.
> So I would like to create cached-instances of the report for each possible
> variable value.
> I suppose this is a rather common problem. Is there a solution (script,
> program) available somwhere to do this?
> (I've tried some things with the scripts but I can't get it to work, I
> keep
> geeting "timed out' errors (although de report execution is set not to
> time
> out) or security exeptions (althoug my user is a RS system user with all
> the
> authoroty))
> Thank you|||I did, but it's a workaround. I've written a small programme in VB.net
that will take the name of the report and the parameters and that will
render the report in a web-window for each possible combination of the
parameters.
This does the trick, but it's not what you would call "elegant", I hope MS
will solve this in the next version.
"Joel Rumerman" wrote:
> Did you ever figure out how to do this?
> We thought that creating a data-driven subscription that dumped the report
> to a file share when the report was setup to be cached would cache all
> possible versions of the report, but we're finding that it's not caching
> those executions and the report is being rendered on the first request.
> Any thoughts?
> Thx, Joel
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
> >I have a report that takes quite some time to render.
> > So I would like to create cached-instances of the report for each possible
> > variable value.
> > I suppose this is a rather common problem. Is there a solution (script,
> > program) available somwhere to do this?
> >
> > (I've tried some things with the scripts but I can't get it to work, I
> > keep
> > geeting "timed out' errors (although de report execution is set not to
> > time
> > out) or security exeptions (althoug my user is a RS system user with all
> > the
> > authoroty))
> >
> > Thank you
>
>

Create/Alter view with declare variable

Hi all,

I not sure whether is there a way to create/alter a view with variable declare in the same statement. I encounter some error when I run the below statement.

Error:

Server: Msg 156, Level 15, State 1, Procedure Employee_Details , Line 2
Incorrect syntax near the keyword 'DECLARE'.
Server: Msg 170, Level 15, State 1, Procedure Employee_Details , Line 16
Line 16: Incorrect syntax near ')'.

My SQL Statement

ALTER VIEW Employee_Details as (
DECLARE @.usr nvarchar(250)
SET @.usr = user
SELECT
E.ID
,E.NAME
,E.DEPARTMENT
,E.JOB_TITLE
,E.JOIN_DATE
,E.RESIGN_DATE
FROM
EMPLOYEE AS E
WHERE
E.DEPARTMENT = (SELECT *
FROM GETCURRENTUSER(@.usr))
)

The GetCurrentUser is a function I have create to get the department that the user can view.

Please advice. Thanks in advance.

Hello,

You cannot declare variables in view defiunitions. You can either create your logic in a procedure or modify your view to make use of the in-built user_name() function in order to return filtered results.

Cheers,
Rob

|||

Hi Rob,

Yes, I have try to use another way of doing and I think I have solve the problem.

Really thanks for your advice, it does give me another idea of doing this.