Creating DB from scripts: dynamic

Showing posts with label dynamic. Show all posts

Monday, March 19, 2012

Creating a Picture in CLR?

I should want to be able to dynamic create a picture in SQL 2005. I have
looked at CLR and this look very intresstning but i can not import
System.Drawing into my project. Is it possible to do this in any way? The
dynamic picture will be returned in a SELECT so i need to use UDF.
/MartinWhy can't you do this in the client?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Martin Josefsson" <Martin Josefsson@.discussions.microsoft.com> wrote in
message news:1E02A10D-4EDE-4593-B709-388DAF0D96C8@.microsoft.com...
>I should want to be able to dynamic create a picture in SQL 2005. I have
> looked at CLR and this look very intresstning but i can not import
> System.Drawing into my project. Is it possible to do this in any way? The
> dynamic picture will be returned in a SELECT so i need to use UDF.
> /Martin|||The client will be Crystal Reports XI and here i can not use a UDF that
returns a image. So what i can see is the only way to produce the image on
the Server.
/Martin
"Adam Machanic" wrote:

> Why can't you do this in the client?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Martin Josefsson" <Martin Josefsson@.discussions.microsoft.com> wrote in
> message news:1E02A10D-4EDE-4593-B709-388DAF0D96C8@.microsoft.com...
>
>|||"examnotes" <Martin
Josefsson@.discussions.microsoft.com> wrote in
news:1E02A10D-4EDE-4593-B709-388DAF0D96C8@.microsoft.com:

> I should want to be able to dynamic create a picture in SQL 2005. I
> have looked at CLR and this look very intresstning but i can not
> import System.Drawing into my project. Is it possible to do this in
> any way? The dynamic picture will be returned in a SELECT so i need to
> use UDF.
>
The problem you are encountering is that System.Drawing is not part of
the "blessed" assemblies who are allowed to be loaded from the GAC; it
has to be loaded from the database. So what you need to do is to first
catalogue the System.Drawing assembly in the database through CREATE
ASSEMBLY. Then you should be able to use it.
HOWEVER!!! You probably have to create the assembly under the UNSAFE
permission set, which should tell uou that you should be really careful
with what you are doing. Just because you can do it in SQLCLR doesn't
mean it is a good idea!!
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||>You probably have to create the assembly under the UNSAFE permission set
Read more about it...
http://blogs.msdn.com/tims/archive/.../27/142798.aspx

Thursday, March 8, 2012

Creating a large dynamic View

I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

EDIT: Changed the title to read Procedurally generating a large view.you have a stored procedure that creates a view with dynamic sql? seems like a bad idea. stored procedures are for DML, not DDL.

why not just store the view definition as a script in source control and execute it as necessary? when your supplier adds columns to their tables, you just add those columns to your script and execute it again.|||I don't really know what you mean by storing it as a script in a source control. By a source control, do you mean a third party utility? I am not familiar with the term. I don't really have any third party utilities or compilers to work with, just SQL Server 2000. The stored procedure that I have has been working fine. I just wanted to find out if there is a more efficient way to do the same thing. Basically, I need the view to look at different tables every day. The tables are indicated at the end of the table name by day and month of the data they contain. The basic outline of the stored procedure I have is below.

Create Procedure ProcName
as
Declare @.DatabaseName as varchar (128)
Declare @.sql as varchar(8000)
Declare @.view_Name as varchar (128)
Declare @.table_Name as varchar (128)
Declare @.ProcDate as datetime
Declare @.cntr as Int
Declare @.sql2 as varchar(8000)
Declare @.sql3 as varchar(8000)

Select @.DatabaseName = DB_NAME()
exec usp_DayToProcess Null, @.ProcDate output

Set @.cntr = 0
Set @.view_Name = 'ViewName'
Set @.Path2 = ''
Set @.Path3 = ''
Set @.Path = 'CREATE VIEW ViewName AS SELECT * FROM ('

While (@.cntr < 15)
Begin
Set @.table_Name = '[TableName_' + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 1, 2) + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 4, 2) + ']'
if exists (select * from dbo.sysobjects where id = object_id(@.table_Name) and OBJECTPROPERTY(id, N'IsUserTable') = 1 and crdate > DateAdd(year, -1, @.ProcDate))
Begin
If @.cntr <> 0
Begin
Set @.Path = @.Path + 'UNION ALL '
End
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
End
Set @.cntr = @.cntr + 1
If Len(@.Path) > 7000
Begin
If Len(@.Path2) > 7000
Begin
Set @.Path3 = @.Path2
End
Set @.Path2 = @.Path
Set @.Path = ''
End
End

Set @.Path = @.Path + ') TempView ORDER BY ...'

EXECUTE (@.Path3 + @.Path2 + @.Path)
GO

Since I have several fields that have to be reformated from the tables as well as functions to perform on some of the fields in order to get the values I need, the sql gets fairly large. So, it ends up taking more than two varchar variables to store all of the sql to search 15 tables. I am trying to standardize the procedure a bit, so in case more than 15 days of tables are required, it would require more variables. I was wondering if there is a more efficient way of doing this with SQL Server 2000 alone.|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control|||What is the point of this:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
Are you manually coding the column names?|||Personally, I like the SELECT * part|||The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control

So, you are basically saying to modify the code each day/week/whenever it needs to be run?

What is the point of this:

Code:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_NameAre you manually coding the column names?

The '...' is where I am specifying the fields to use. I didn't include all of it, because it is a bit long. For instance, I am adding a date field into the view so that the date of the transaction is a field. The tables do not have a transaction date field, since they are a different table for each day. Also, I specify the field names, because there are times that the company who creates the code that makes the tables will change that code during an update. I could check the table for any changes each time they put out updates, but this aggregates several tables. So, some of the tables would be missing fields that others have within the tables that are being aggregated. This would cause an error if the fields to use were not specified.

Personally, I like the SELECT * part

After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

Originally Posted by Ishe
The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels

I know what you mean, but I didn't really design the tables or the code to make the tables. It's just the only thing I have to work with really.|||So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() (http://msdn2.microsoft.com/en-us/library/3y322t50.aspx) in C#. hard to debug, hard to know what the actual state of the system was at any given time.|||After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.|||SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.

I know, and it has an ORDER BY clause at the end, but I have found from experience with prior UNION views that if you slap an ORDER BY clause at the end of the last union, it doesn't order the entire result set, just the last SELECT. So, I wrapped the entire UNION query making the UNION query a subquery and put the ORDER BY clause at the end of the wrapping query.

Originally Posted by Ishe
So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.

Does that include creating tables through stored procedure?

In this case I am trying to create this in such a way that it won't take someone that knows anything much about SQL Server to be able to use the procedure. Since the tables that the user would need to use change on a daily basis, I don't know of any other way to accomplish this. I can't rewrite the code for them every day. I am actually trying to change the code to be less customized, not more so.|||The '...' is where I am specifying the fields to use.If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.|||If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.

The only reason that it is done in a stored procedure is because the tables that I have to work with are daily transaction tables. The table names indicate the day and month of the transactions that are contained within. I don't think I can create a standard view with daily changing table names.|||But you don't have standard columns!|||But you don't have standard columns!

I'm not sure what you mean. The below contains the code that I replaced '...' with in the example.

Set @.Path = @.Path + 'SELECT FB_MBRNO AS [Member No], Convert(datetime, ' + CHAR(39) + Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 102) + CHAR(39) + ') AS [Tran Date], FB_TLR AS Tlr, FR_TRAN_CODE AS [Tran Code], FB_STATUS AS [Status], FB_CNV_CASH AS CnvCash, FR_TIM AS [Time], FB_CASH AS [Cash], FB_CHECK AS [Checks], FB_APPLIED AS Applied, FB_CASHBACK AS [Cash Back], FB_REVERSED AS Reversed, FB_MISC_CODE As [Misc Code], FB_TRAN_NO As [Tran No] FROM ' + @.table_Name

I think the columns are fairly standard, except the [Tran Date] field, which is there because the tables I am looking at do not have a date field that indicates when the transaction occured.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.

I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.|||I guess you have no choice then. I wouldn't use that software provider again if I were you. ;)|||I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.

Have I mentioned lately I hate 3rd party vendors?

There is no silver bullet|||Have I mentioned lately I hate 3rd party vendors?Not often enough.

Wednesday, March 7, 2012

Creating a Dynamic YTD calculation in MDX

I have a YTD calculation that I want to make dynamic based on the real current date.

This is the regular YTD formula:

Sum(YTD([Date].[Calendar Hierarchy].CurrentMember),[Measures].[Planned Orders])

This is the YTD formula hard coded with the current date:

Sum(YTD([Date].[Calendar Hierarchy].[Calendar Year].&[2007].&[2007Q1].&[2007-01].&[2007-01-30T00:00:00]),[Measures].[Planned Orders])

The hard coded date works but I need the date to be dynamic. I’m not sure how to get it to be based from the current date.

I’ve been experimenting with using the NOW() function to return the date but I can’t get the syntax correct.

Thank you.

David

Hmmm i think you can find your answer here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

|||

The article is good but it doesn't address my need to make the date part of the calculation relative and based on the current date (down to the day level).

David

|||

Perhaps this thread can help you:

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

Regards

Thomas Ivarsson

|||

Thomas,

Unfortunately, I have budget like future date information in the cube. As a result, I probably need to develop a dynamic way of capturing the current date.

Would this possible scenario work using your suggestion:

Suppose I created a new fact table with one record where the date changed every day. I have a measure column called "Sales " with a value of 1.

I add this fact table to the cube and rewrite your named set:

Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[lSales])>0))

This would make the date always based on the current date. However, I'm not sure if would filter out other records that I need to show.

David

|||

If you have a measure that is updated daily, like actual sales, my solution will work even if you have a budget measure that points to future dates.

This link have some other suggestions.

http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

HTH

Thomas Ivarsson

Creating a Dynamic Temporary Table

When I execute the following Stored Procedure with a parameter (EXEC MyProc
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:

> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>

Creating a dynamic temporary table

I need to dynamically create a temporary table like this (I don't know its
structure until runtime - it is based on selections made by user):
DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(250)
+ ', ' + @.col2 + 'varchar2(250))'
EXEC(@.CreateStatement)
--following is code to fill this table
The table created is not accessible after the line EXEC(@.CreateStatement). I
know this (temp table have a scope limited to the stored procedure that
created them).
Is there another way to accomplish this? I also tried using table variables,
but I wasn't able to make a stored procedure that returns a table variable.YOU CAN USE FUNCTION INSTEAD OF STORED PROC
CREATE FUNCTION TEMP (@.col1 varchar(250) ,@.col2 varchar(250))
RETURNS TABLE
as
RETURN SELECT @.col1+','+@.col2 as TEXT
"razdanro" wrote:
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(25
0)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table variable
s,
> but I wasn't able to make a stored procedure that returns a table variable.[/color
]|||You can create tempdb..temptable or create ##temptable and it will be
available until it is dropped or the sql server is re-booted.. The
difference between #temptable and ##temptable
#temptable is a non-sharable connection specific temporary table. It goes
away when the SP (if created in an sp) or connection goes away..
##temptable can be seen by all spids, and lives until you drop it or the
server is re-booted... if you are wiriting for mutliple concurrnet users,
you may have to check for its existence before creating it... or come up
with some unque name, and/or attach a spid to separate your rows from those
inserted by another spid.
hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||create the table first using a hard coded "create table" statement (with at
least one column - a dummy column if needed) - then dynamically alter its
structure.
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||Do not write SQL this way.
Temporary tables tell us that you are really writing procedural code
and have not learned to think in sets and declarative code yet. A temp
table is a "scratch tape" for an algorithm based on procedural steps in
95% of the cases. You probably should be using derived tables or
VIEWs.
Dynamic SQL tell us that you do not know what you are doing, so you
have to let a random stranger create a table in your data model at the
last minute.
Using over-sized VARCHAR(n) values tells us that you did no research to
find the proper size, but just grabbed a large dummy value. This also
means that you have no data model and probalby no data dictionary.
Finally, you will never learn SQL this way. You have already decided
on HOW you want to solve a problem. So people will show you how to
write kludges for your bad solution. But if you had posted WHAT you
want to do, then you might get a relational answer.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Let's try again with the actual problem.|||Here is the data model that I didn't design, but I have to work with right n
ow.
I have three entities: Companies, Sites and Contacts. A Company has 0 or
more Sites, a Site has 0 or more Contacts.
The application must allow users to add properties of these entities
dynamically.
These Properties are held in a table, and the values allowed are in a
PropertyValues table. There is also a table EntityProperty which is an
intersection table between Entities and PropertyValues.
So I have a design that actually stores data and metadata.
And now I have to make a SQLBuilder based on these Entities. I don't know
what Property will be selected as output, that's why I need to create a
dynamic temporary table to return the result.

Creating a dynamic excel file

Is it possible that i can create a dynamic excel file (destination)

ex, i want to create a Dyanamic Excel destination file with a filename base on the date

this will run on jobs. Is this possible?

11172006.xls, 11182006.xls

Sure. With just about any destination, including Excel, the name/location can be dynamic.

1. Create a string variable which represents the excel file name, set the variable's EvaluateAsExpression property to true, and set the expression to something dynamic, for example:

"ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls"

2. For your excel connection manager, in the expressions node of the Properties tab, set the connection string property to the variable you just created. That's it.

You can skip step I and write the dynamic file name expression directly as in step 2. However, the advantage of a variable is that you can easily view it by setting breakpoints, and looking at the dynamic value in the Locals or Watch windows.

If you could evaluate expressions in the immediate window, there would be less need for the variable to contain the filename.|||

Hi Thanks

anyway I'm gonna test it, if it's going to work, I hope it does.

I'll reply again after i check it out

Anyway thanks, hope this work

|||

Jaegd,

Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far.

1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable.

The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly.

Jamie, Kirk or someone please comment on this.

2. The second approach is to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which is configured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection.

One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works.

Thanks....

|||

Ravi G wrote:

Jaegd,
Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far.
1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable.
The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly.
Jamie, Kirk or someone please comment on this.
2. The second approach is to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which is configured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection.
One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works.
Thanks....

My suggestion would be to tweak a bit your 2^nd approach:

You may have, perhaps, an empty file with the required structure, let's say TargetExcelFile.xls that you copy/rename to the excel destination component's expected location prior to the dataflow. For that, you could use a file system task that uses an expression to rename the file with the right name every time. Then in the data flow the excel connection string should use the same expression to find the just renamed file.

|||Ravi, I did indeed forget a step.

Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.

For example,
CREATE TABLE `Excel Destination` (
`GeneratedInt_1` INTEGER
)

Then create the connection string variable on the connection manager as follows:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

And yes, as you were intimating, the delay validation on the dataflow should be set.|||

Jaegd,

I was just about the post the same thing and you beat me to it. I tried my third approach and it works exactly the way I wanted.

By the way, you can set the filename property dynamically instead of the connection string property, its simpler and more readable.

|||

Hi,

I'm kinda new here in SSIS, is it possible that you can help me to do this step by step, I'm kinda lost

Hope you can help me this one

THanks

jaegd wrote:
Ravi, I did indeed forget a step.
Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.
For example,
CREATE TABLE `Excel Destination` (
`GeneratedInt_1` INTEGER
)
Then create the connection string variable on the connection manager as follows:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"
And yes, as you were intimating, the delay validation on the dataflow should be set.

|||

Sure. I was planning to post a summary of my findings anyway.

I'll be posting it soon.

|||

This example is useful for loading data from an OLEDB source into a dynamically created Excel file.

NOTE:
This is the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion.

Steps:
1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.

2. Create a package level variable "XLFileRootDir" as string and set it to the root
directory where you want the excel file to be created.
Example: C:\\Project\Data\

3. Create an Excel connection in the connection manager. Browse to the target directory
and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@.[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an xl file like 01132007.xls.

5. Add a SQL task to package and double click to edit.
In the general tab, set 'ConnectionType' to 'Excel'.
For 'SQLStatement', enter the create table SQL to create destination table.
For example:
CREATE TABLE `Employee List` (
`EmployeeId` INTEGER,
`EmployeeName` NVARCHAR(20)
)
Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.
Configure the source to select EmployeeId and EmployeeName from a table.

7. Connect this to Excel destination. In the destination editor, select the Excel connection in the
manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on
new button and paste the create table command from Step 5.
Map the columns appropriately in the mappings tab and you are done.

Let me know if you have any questions.

|||

Hi Ravi G and to other's who answer

thanks to all

anyway does anyone here know's how to generate a guid? and use it as a file name? do i need the script task?

lastly i hope this is not to much to ask, does anyone here know's how to connect to Active directory? the basic concept at least?

anyway thanks to all you guys!!!

cheers

|||

Hi, Ravi G

I successfully created the excel file but i still have one more problem, how would i dynamically map data from it after i created the excel file(I already have the filed and the table)? since the created excel file was the the destination file.

Hope you can still help me on this one

Thanks

Ravi G wrote:

This example is useful for loading data from an OLEDB source into a dynamically created Excel file.
NOTE:
This is the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion.
Steps:
1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.
2. Create a package level variable "XLFileRootDir" as string and set it to the root
directory where you want the excel file to be created.
Example: C:\\Project\Data\
3. Create an Excel connection in the connection manager. Browse to the target directory
and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.
4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@.[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an xl file like 01132007.xls.
5. Add a SQL task to package and double click to edit.
In the general tab, set 'ConnectionType' to 'Excel'.
For 'SQLStatement', enter the create table SQL to create destination table.
For example:
CREATE TABLE `Employee List` (
`EmployeeId` INTEGER,
`EmployeeName` NVARCHAR(20)
)
Copy the create table command. It will come in handy later.
6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.
Configure the source to select EmployeeId and EmployeeName from a table.
7. Connect this to Excel destination. In the destination editor, select the Excel connection in the
manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on
new button and paste the create table command from Step 5.
Map the columns appropriately in the mappings tab and you are done.
Let me know if you have any questions.

|||

You map the columns at design time. You dont need to do that everytime the package runs.

As long as the column names and data types remain the same, you dont have to do anything.

|||

so it's impossible that after i create dynamically the excel file, in the control flow

can i automatically use it as a destination file? will be any problem if i don't map it?

My goal for this one is create a dynamic file in the excel and use it automatically as the destination file

which runs in one package

Thanks

|||

arsonist wrote:

will be any problem if i don't map it?

The package will fail if you don't map it. At the very least you wont see any data in the Excel file.

What we are trying to do is create an excel connection that dynamically creates an excel file under the covers.

You will use the excel connection just as you would use a regular OLEDB connetion, to create your package, as if you are working with a static Excel file.

Hope its clearer.

Friday, February 24, 2012

Creating a cursor with a dynamic database name.

You will either know this or you won't. I want to do thisthe following two lines of TSQL in one dynamically but none of the Declare Cursor statements work (when I try to pass in the Database name using a parameter). How do i dynamicically create a cursor to a table using a dynamic database/catalog name?

DECLARE curTest1 CURSOR SELECT * FROM testDB1.dbo.MyTable
DECLARE curTest2 CURSOR SELECT * FROM testDB2.dbo.MyTable

I've tried the following

DECLARE @.CatalogName NVARCHAR(5)
DECLARE @.sqlStr NVARCHAR (4000)

SET @.CatalogName = 'TestDB'
SET @.sqlStr = 'SELECT * FROM ' + @.CatalogName + 'dbo.Mytable;'

DECLARE curTest CURSOR FOR SELECT * FROM @.CatalogName.dbo.MyTable -- Which obviously should not and does not work.
DECLARE curTest CURSOR FOR @.sqlSTR -- Which I thought would work but also does not work.

CLOSE curTest
DEALLOCATE curTest

My environment is SQL Server 2000 environment SP3the answer you are looking for is sp_executesql....

you can't use a parameter value in the way you are trying...

you would need to dynamicly create and execute your sql statement using sp_executesql.

there have been a few posts in the last few days that explain this to the nth degree.|||You can't declare with dynamic sql...

DECLARE @.declare varchar(2000)

SET @.declare = 'DECLARE @.x int'

sp_executesql(@.declare)

And why do you want to use a cursor?

Think of dynamic sql being "outside" the scope of the current thread...|||Actually you're both wrong. I figured it out.

Turns out you need to use the EXEC command to execute the string.|||Yes I am...and I wish I wasn't

Why would you want to do this?

You going to build the Fetches dynamically?

How about the Declarations of the variables...That I don't think you can do indynamic sql

but this (to my UTTER amazement)..wrks:

USE Northwind
GO

DECLARE @.cmd varchar(8000), @.ShippedDate datetime
SELECT @.cmd = 'DECLARE myCursor CURSOR FOR SELECT ShippedDate FROM Orders'
EXEC(@.Cmd)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.ShippedDate
SELECT @.ShippedDate
CLOSE myCursor
DEALLOCATE myCursor

Good luck...|||The short answer is consolidated reporting on Accounting systems.

Most modern accounting systems allow for multiple companies to be managed from one server. To accomodate this, a seperate database is created for each company but fortunately the structure of the tables does not change between companyies . As a result, to report consolidated figures for the entire organization you want to have catalog names passed in dynamically especially if your oganization contains many companies.|||Can you post the sproc?

I'd like to see if there's a non cursor way...|||Here is one of them.

CREATE PROCEDURE sp_Sales_Summary_Update_02_03

@.CatalogName NVARCHAR(5),
@.ItemNumber NVARCHAR(31),
@.Warehouse NVARCHAR(11),
@.PurchaseTableName NVARCHAR(8),
@.PurchaseLineTableName NVARCHAR(8)
AS

DECLARE @.FromTheYear INT
DECLARE @.FromTheWeek INT
DECLARE @.ToTheYear INT
DECLARE @.ToTheWeek INT
DECLARE @.QuantityOrdered NUMERIC(19,5)
DECLARE @.strCursorString NVARCHAR(4000)

SET @.strCursorString = ''
SET @.strCursorString = @.strCursorString + 'DECLARE curQuantityOrdered CURSOR FORWARD_ONLY FOR '
SET @.strCursorString = @.strCursorString + 'SELECT '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheWeek, '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheWeek, '
SET @.strCursorString = @.strCursorString + ' ''' + @.CatalogName + ''' AS CompanyID, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR AS ItemNumber, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE AS Warehouse, '

SET @.strCursorString = @.strCursorString + ' SUM(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER) AS QuantityOrdered '
SET @.strCursorString = @.strCursorString + 'FROM ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + ' LEFT OUTER JOIN ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + ' '
SET @.strCursorString = @.strCursorString + ' ON ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PONUMBER = ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.PONUMBER '
SET @.strCursorString = @.strCursorString + 'WHERE '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.POSTATUS <> 6) AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER <> 0) '
SET @.strCursorString = @.strCursorString + 'GROUP BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
SET @.strCursorString = @.strCursorString + 'HAVING '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR = ''' + @.ItemNumber + ''') AND '
SET @.strCursorString = @.strCursorString + ' (''' + @.CatalogName + ''' = ''' + @.CatalogName + ''') AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE = ''' + @.Warehouse + ''') '
SET @.strCursorString = @.strCursorString + 'ORDER BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
PRINT @.strCursorString
EXECUTE(@.strCursorString)

OPEN curQuantityOrdered

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE tblSalesSummary
SET QuantityOrdered = @.QuantityOrdered
FROM
tblSalesSummary
WHERE
(CompanyID = @.CatalogName) AND
(ItemNumber = @.ItemNumber) AND
(Warehouse = @.Warehouse) AND
CASE
WHEN TheWeek < 10 THEN
CAST(TheYear AS nvarchar(4)) + '0'+ CAST(TheWeek AS nvarchar(2))
ELSE
CAST(TheYear AS nvarchar(4)) + CAST(TheWeek AS nvarchar(2))
END
BETWEEN
CASE
WHEN @.FromTheWeek < 10 THEN
CAST(@.FromTheYear AS nvarchar(4)) + '0'+ CAST(@.FromTheWeek AS nvarchar(2))
ELSE
CAST(@.FromTheYear AS nvarchar(4)) + CAST(@.FromTheWeek AS nvarchar(2))
END
AND
CASE
WHEN @.ToTheWeek < 10 THEN
CAST(@.ToTheYear AS nvarchar(4)) + '0'+ CAST(@.ToTheWeek AS nvarchar(2))
ELSE
CAST(@.ToTheYear AS nvarchar(4)) + CAST(@.ToTheWeek AS nvarchar(2))
END

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
END
CLOSE curQuantityOrdered
DEALLOCATE curQuantityOrdered
GO|||It Looks like alot but is really 2 steps. The first BLOB of TSQL creates the cursor. The second BLOB of TSQL updates the destination table.|||WOW! Dude...how long does it take to run?|||This particular sp executes in a fraction of a second. However it is run multiple times and depending on the volume of data required for processing can add up to hours (inconjunction with the other sp's I have running).

creating a chart/graph

Good evening,

I was looking at creating a dynamic charts and graph and wanted to know if something like that would work for data being pulled from an SQL query?

SELECT category, COUNT(category) AS issue_count FROM ticket_view v GROUP BY category ORDER BY category

where i am just counting the records on the page and wanting to display them in a graph like below?

Graph Example:

Category:IS Security
Issues:2

Category:Maintenance
Issues:2

Category:Personnel
Issues:2

Category:Project
Issues:9

Category:Uptime
Issues:55

Hello,

Visit the following free chart controls

http://zedgraph.org/wiki/index.php?title=Main_Page

http://www.carlosag.net/Tools/WebChart/Default.aspx

Tuesday, February 14, 2012

CREATE via Dynamic SQL into new database?

From a stored procedure running in the context of one database, I would like
to create a set of objects (stored procedures, functions, views, users) into
a newly-created second database, where the name is dynamically determined.
Creating the new database and retrieving its name is no problem, the problem
is executing CREATE FUNCTION, CREATE PROCEDURE, etc. in the context of the
new database.
As you know, executing dynamic SQL 'use database' won't change the context
of an executing procedure. And 'use database; create function ...' doesn't
work, because the create statements need to be in their own batch. I cannot
store the objects in Master, so I can't have them automatically created with
the new database.
Is there a way to copy the objects from an existing (i.e. template) database
to the new one using dynamic SQL? Any way to attach a copy of a template
database file to a new database dynamically?
Or any out-of-the-box ideas?declare @.sql nvarchar(1000)
set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
exec sp_executesql @.sql
> From a stored procedure running in the context of one database, I would
> like to create a set of objects (stored procedures, functions, views,
> users) into a newly-created second database, where the name is dynamically
> determined. Creating the new database and retrieving its name is no
> problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> in the context of the new database.
> As you know, executing dynamic SQL 'use database' won't change the context
> of an executing procedure. And 'use database; create function ...' doesn't
> work, because the create statements need to be in their own batch. I
> cannot store the objects in Master, so I can't have them automatically
> created with the new database.
> Is there a way to copy the objects from an existing (i.e. template)
> database to the new one using dynamic SQL? Any way to attach a copy of a
> template database file to a new database dynamically?
> Or any out-of-the-box ideas?
--
new|||here's a real hum-dinger: (this is all on one line)
exec opendatasource('sqloledb', 'data
source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
N'create table mydatabase.dbo.newtable (myfield1 int)'
You'll want to change the following areas:
YourServer
UserId
Password
YourDatabase
... and the statement of course
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
>> From a stored procedure running in the context of one database, I would
>> like to create a set of objects (stored procedures, functions, views,
>> users) into a newly-created second database, where the name is
>> dynamically determined. Creating the new database and retrieving its name
>> is no problem, the problem is executing CREATE FUNCTION, CREATE
>> PROCEDURE, etc. in the context of the new database.
>> As you know, executing dynamic SQL 'use database' won't change the
>> context of an executing procedure. And 'use database; create function
>> ...' doesn't work, because the create statements need to be in their own
>> batch. I cannot store the objects in Master, so I can't have them
>> automatically created with the new database.
>> Is there a way to copy the objects from an existing (i.e. template)
>> database to the new one using dynamic SQL? Any way to attach a copy of a
>> template database file to a new database dynamically?
>> Or any out-of-the-box ideas?
>
--
new|||Thanks, but this isn't the issue. Issue is that from a stored procedure (or
batch, for that matter) running in the context of database A, do:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'use ' + @.DBName + '; create function foo ...'
exec (@.SQL)
Doesn't work because 'create function' must be at the beginning of a batch.
set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
design.
Need to create functions, stored procs etc. in a different,
dynamically-determined database.
"beginthreadex" wrote:
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
> > From a stored procedure running in the context of one database, I would
> > like to create a set of objects (stored procedures, functions, views,
> > users) into a newly-created second database, where the name is dynamically
> > determined. Creating the new database and retrieving its name is no
> > problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> > in the context of the new database.
> >
> > As you know, executing dynamic SQL 'use database' won't change the context
> > of an executing procedure. And 'use database; create function ...' doesn't
> > work, because the create statements need to be in their own batch. I
> > cannot store the objects in Master, so I can't have them automatically
> > created with the new database.
> >
> > Is there a way to copy the objects from an existing (i.e. template)
> > database to the new one using dynamic SQL? Any way to attach a copy of a
> > template database file to a new database dynamically?
> >
> > Or any out-of-the-box ideas?
> --
> new
>|||LOL ... next it will be sp_cmdshell(osql ... ). :-)
"beginthreadex" wrote:
> here's a real hum-dinger: (this is all on one line)
> exec opendatasource('sqloledb', 'data
> source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
> N'create table mydatabase.dbo.newtable (myfield1 int)'
> You'll want to change the following areas:
> YourServer
> UserId
> Password
> YourDatabase
> ... and the statement of course
>
> > declare @.sql nvarchar(1000)
> > set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> > exec sp_executesql @.sql
> >
> >
> >> From a stored procedure running in the context of one database, I would
> >> like to create a set of objects (stored procedures, functions, views,
> >> users) into a newly-created second database, where the name is
> >> dynamically determined. Creating the new database and retrieving its name
> >> is no problem, the problem is executing CREATE FUNCTION, CREATE
> >> PROCEDURE, etc. in the context of the new database.
> >>
> >> As you know, executing dynamic SQL 'use database' won't change the
> >> context of an executing procedure. And 'use database; create function
> >> ...' doesn't work, because the create statements need to be in their own
> >> batch. I cannot store the objects in Master, so I can't have them
> >> automatically created with the new database.
> >>
> >> Is there a way to copy the objects from an existing (i.e. template)
> >> database to the new one using dynamic SQL? Any way to attach a copy of a
> >> template database file to a new database dynamically?
> >>
> >> Or any out-of-the-box ideas?
> >
> --
> new
>|||The code I provided does execute the code in the other database. Hence, the
"mydatabase" reference. So, here's your code mixed with mine:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
exec sp_executesql @.sql
If there is something else that is confusing please let me know. Because I'm
referencing the database name this will run for the context of the other
database.
;)
> Thanks, but this isn't the issue. Issue is that from a stored procedure
> (or batch, for that matter) running in the context of database A, do:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> exec (@.SQL)
> Doesn't work because 'create function' must be at the beginning of a
> batch.
> set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> design.
> Need to create functions, stored procs etc. in a different,
> dynamically-determined database.
>
>> declare @.sql nvarchar(1000)
>> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
>> exec sp_executesql @.sql|||If you tried it (in s2k), you would realize that you cannot use 3 part
naming for creating procedures or functions. These statements are limited
to accepting an owner name (optional) and an object name.
Try the following statement:
create procedure pubs.dbo.junk as select getdate()|||Thanks for the response, I really do appreciate it. But CREATE no longer
accepts a DB name reference for functions/procedures - at least in SQL Server
2000.
"beginthreadex" wrote:
> The code I provided does execute the code in the other database. Hence, the
> "mydatabase" reference. So, here's your code mixed with mine:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
> exec sp_executesql @.sql
> If there is something else that is confusing please let me know. Because I'm
> referencing the database name this will run for the context of the other
> database.
> ;)
> > Thanks, but this isn't the issue. Issue is that from a stored procedure
> > (or batch, for that matter) running in the context of database A, do:
> >
> > declare @.DBName varchar(20)
> > set @.DBName = 'dynamic'
> > declare @.SQL varchar(200)
> > set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> > exec (@.SQL)
> >
> > Doesn't work because 'create function' must be at the beginning of a
> > batch.
> >
> > set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> > design.
> >
> > Need to create functions, stored procs etc. in a different,
> > dynamically-determined database.
> >
> >
> >> declare @.sql nvarchar(1000)
> >> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> >> exec sp_executesql @.sql
>|||I deeply apologize! The "Create Table" code does allow for this.
However this DOES work as I have just tested:
exec opendatasource('sqloledb', 'data
source=MySource;uid=MyUID;pwd=MyPWD').pubs.dbo.sp_execsql N'create
procedure dbo.junk as select getdate()'
I know it's not the prettiest, but it DOES work.
> If you tried it (in s2k), you would realize that you cannot use 3 part
> naming for creating procedures or functions. These statements are limited
> to accepting an owner name (optional) and an object name.
> Try the following statement:
> create procedure pubs.dbo.junk as select getdate()
--
new|||Hello,
I suggest that you refer to the following web site:
http://www.databasejournal.com/features/mssql/article.php/3441031
You may try to use sp_MSforeachdb. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 19, 2012

Creating a Picture in CLR?

Thursday, March 8, 2012

Creating a large dynamic View

Wednesday, March 7, 2012

Creating a Dynamic YTD calculation in MDX

Creating a Dynamic Temporary Table

Creating a dynamic temporary table

Creating a dynamic excel file

Friday, February 24, 2012

Creating a cursor with a dynamic database name.

creating a chart/graph

Tuesday, February 14, 2012

CREATE via Dynamic SQL into new database?

CREATE via Dynamic SQL into new database?

CREATE via Dynamic SQL into new database?

Creating DB from scripts

Blog Archive

About Me