Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)

dynamically delete data

Hi All,
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun

Dynamically configuring Fuzzy Grouping

Hello,

I have been struggling with this for quite awhile so any help would be appreciated.

I need to know if there is away to populate the fuzzy grouping control dynamically. I know you programmatically design a package and customize it in C# but for our purposes we would like to control the SSIS package via database settings. When the settings change the package would then act different. Its a simple a package consisting of an Input - fuzzy grouping - conditional split - output. The connections are setup dynamically using parameters, expressions and a script task. Is there anyway I could do a similar thing for Fuzzy Grouping?

No, the FG component needs to know its columns up-front. Either you'll have to use a script component, or code the package from scratch.|||

Thanks for getting back to me.

Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.

|||

Brat wrote:

Thanks for getting back to me.

Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.

No, none of the above. You'd have to perform the fuzzy grouping inside a script component. It is likely quite a bit of work.|||

One more question...

The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?

Thanks

|||

Brat wrote:

One more question...

The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?

Thanks

No. But see if these help you... (From the Transact-SQL forum)
http://forums.microsoft.com/MSDN/Search/Search.aspx?words=fuzzy&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=85|||thanks|||

I have tried with out success to modify the FG component using the Script Task. It seems that a package cannot modify itself at runtime (http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx). I haven't tested this theory yet, but as a work around I created a package that contains the source - FG - destination components and then created another package that contains a Script Task. The idea would be that the script task would reference and load the first package. I can do this like so:

Code Snippet

Dim application AsNew Application()

Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"

Dim pkg As Package = application.LoadPackage(pkgName, Nothing)

but how do I get a handle on the FG component from within the first package? Please help, this is getting extremely frustrating, thanks

|||Once the package is loaded, you need to iterate navigate from the package to the data flow that contains the FG. A package object implements the IDTSSequence interface, which has an Executables collection. Executables can contain other Executables (a sequence container, for example), so you may have to traverse multiple levels to get to your data flow.|||

Thank you for your response!

I have managed to get into the Data Flow Task by doing the following:

Code Snippet

PublicSub Main()

Dim application AsNew Application()

Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"

Dim pkg As Package = application.LoadPackage(pkgName, Nothing)

Dim pgkExecs As Executables = pkg.Executables

Dim exec As Executable

ForEach exec In pgkExecs

Dim th As TaskHost = CType(exec, TaskHost)

Dim i AsInteger = 0

Dim s AsString

While i < th.Properties.Count

s = th.Properties(i).Name

i = i + 1

EndWhile

Next

Dts.TaskResult = Dts.Results.Success

EndSub

The question I now wanted to know if you could help me with is this:

The th.Properties(0).Name is "[Dynamic Fuzzy Grouping].[MaxMemoryUsage]" which is my FG Control. Any idea how to access this control and now change the properties? I have tried several types of casts and I cant seem to get it right, any ideas would be appreciated. Thanks!

|||

You're close. The TaskHost object is the wrapper around the actual dataflow object. The properties you are seeing are the properties of the data flow's inner objects (but only the ones exposed). To get to the actual pipeline, you need to take one more step.

Code Snippet

Dim pipe as MainPipe = CType(taskHost.InnerObject, MainPipe)

Then you can use the ComponentMetaDataCollection property to access the components in the pipeline, which should include your Fuzzy Grouping.

|||

Thank you! Thank you! Thank you!

I finally got it working! I see my problem was also that I was working with the control flow and not the data flow script task, once I changed over I was able to cast my innerobject to MainPipe, thank you so much for your help, I have been battling with this for two weeks now and even though the code itself is quite simple I couldn't seem to find any help about what to do, so thank you so much!

Dynamic values in parameters

I have 3 parameters which I populate from 3 sprocs. However, I want the
value selected for sproc1 to determine the possible values in sproc2 and the
selected value in sproc2 to determine the value in sprocs3. How do I go
about this?
Thanks
Frank AshleyFrank,
Search for "Cascading Parameters" in Reporting Services books online.
There's an example for T-SQL which should apply to stored procs.
"Frank Ashley" wrote:
> I have 3 parameters which I populate from 3 sprocs. However, I want the
> value selected for sproc1 to determine the possible values in sproc2 and the
> selected value in sproc2 to determine the value in sprocs3. How do I go
> about this?
>
> Thanks
> Frank Ashley
>
>

Dynamic Subscriptions in SQL Server 2005

Is there a way to dynamically populate the To: email address from a
database when setting up a subscription?
We would like to search a database for user activity and only deliver
reports to the user that that activity pertains to.
If not in native Reporting Services is there an add-on that will do
this?On Apr 14, 11:59=A0am, DaveK <1027...@.gmail.com> wrote:
> Is there a way to dynamically populate the To: email address from a
> database when setting up a subscription?
> We would like to search a database for user activity and only deliver
> reports to the user that that activity pertains to.
> If not in native Reporting Services is there an add-on that will do
> this?
Yes, data driven subscriptions, available in the Enterprise version,
has the ability to set all delivery specific settings and report
parameters based on a query executed at run-time.

Dynamic SQL to populate a variable

Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.

declare @.TableName sysname

set @.TableName = 'Customers'

delcare @.Output bigint

declare @.SQL varchar(max)

set @.SQL = 'select top 1 RowID from ' + @.TableName

select @.Output =

EXEC (@.SQL)

create function udf_GetDatabaseFingerPrint(

@.DBID bigint

)

begin

returns bigint

as

declare @.dbname sysname

, @.iBig bigint

, @.tSQL varchar(2000)

select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID

set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'

set @.iBig = exec(@.tSQL)

return @.iBig

end

Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had

declare @.objectId int,

exec sp_executeSQL

N'select @.objectId = max(object_id) from sys.objects',

N'@.objectId int output', @.objectId=@.objectId output

select @.objectId

Dynamic Sql Parse - Please help!

okay I have found the table name that I want dropped using the dynamic sql
below. I'm not quite sure how to populate @.TableToDrop for the actual drop
statement though. Can you help? I hope the indentation is not too bad and yo
u
can read the Statement ok
Set @.lnvchCommand = ' IF EXISTS ' +
' ( SELECT *
FROM WH_BKUPS..SYSOBJECTS
WHERE NAME like ' + '''' + @.lvchTable_Name + '_%' + '''' + '
AND NAME < ' + '''' + @.lvchTable_Name + '_' + @.exc_date_id + '''' +
' ) ' +
' Select @.lchTable_Exists = ''Y''' +
' Else ' +
Select @.lchTable_Exists = ''N'''
Execute sp_executesql @.lnvchCommand, @.ParmDefinition, @.lchTable_Exists OUTPU
T
If @.lchTable_Exists = 'Y'
Begin
DROP TABLE @.TableToDrop
EndUse dynamic SQL for the drop, for instance:
If @.lchTable_Exists = 'Y'
Begin
EXEC('DROP TABLE ' + @.TableToDrop)
End
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:4131048F-69B2-4765-88DB-E940E0E1B4AB@.microsoft.com...
> okay I have found the table name that I want dropped using the dynamic sql
> below. I'm not quite sure how to populate @.TableToDrop for the actual drop
> statement though. Can you help? I hope the indentation is not too bad and
you
> can read the Statement ok
> Set @.lnvchCommand = ' IF EXISTS ' +
> ' ( SELECT *
> FROM WH_BKUPS..SYSOBJECTS
> WHERE NAME like ' + '''' + @.lvchTable_Name + '_%' + '''' + '
> AND NAME < ' + '''' + @.lvchTable_Name + '_' + @.exc_date_id + '''' +
> ' ) ' +
> ' Select @.lchTable_Exists = ''Y''' +
> ' Else ' +
> Select @.lchTable_Exists = ''N'''
> Execute sp_executesql @.lnvchCommand, @.ParmDefinition, @.lchTable_Exists OUT
PUT
> If @.lchTable_Exists = 'Y'
> Begin
> DROP TABLE @.TableToDrop
> End
>|||You'll have to assign the table name to a variable and use EXEC or
sp_executesql for the DROP statement as well.
Why don't you know what tables exist in your database? This might be a
reasonable thing to do in an ad-hoc admin script (not really sure why
though) but in a business process application your table structures
ought to be static at runtime so this should never be necessary.
David Portas
SQL Server MVP
--|||thanks guys for your help. Much appreciated but I have found a solution.
what I would love how to do though is drop a table as follows
declare @.TableToDrop char(50)
select @.TableToDrop = 'marc'
print @.TableToDrop
drop table DBName..+@.TableToDrop
tearing my hair out...|||declare @.TableToDrop char(50)
select @.TableToDrop = 'marc'
print @.TableToDrop
EXEC('drop table DBName..' + @.TableToDrop)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:CF46E496-BD1C-47A0-9053-CFFADC337609@.microsoft.com...
> thanks guys for your help. Much appreciated but I have found a solution.
> what I would love how to do though is drop a table as follows
> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> drop table DBName..+@.TableToDrop
> tearing my hair out...|||> what I would love how to do though is drop a table as follows

> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> drop table DBName..+@.TableToDrop
This isn't supported because the basic concept for database
applications is that tables are created at design time and do not
change at runtime. In other words you should know what your table names
are and not need to parameterize them. That's why I asked why you need
to do this.
David Portas
SQL Server MVP
--|||Perhaps I should add that I fully agree with David, you should know the name
s of the tables and
there should be no need to, from an application, keep dropping tables. This
is most often a sign of
a mistake in the data modeling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:eVD8i2aRFHA.2976@.TK2MSFTNGP10.phx.gbl...
> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> EXEC('drop table DBName..' + @.TableToDrop)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:CF46E496-BD1C-47A0-9053-CFFADC337609@.microsoft.com...
>|||This is not an RDBMS but a 1950's mag tape file system in a realllllly
bad disguise. The IBM convention for tape labels was "yyddd" , just
like you are using almost 60 years later!
SQL programmers have a schema that models the entities in their reality
in tables. Dropping and creating tables on the fly is an insanely poor
practice. It leads to dynamic SQL and other kludges. It says that
your model is in constant flux, never quite dependable. It says that
you live in a Phillip K. Dick world.
Get your boss to pay for some basic data modeling classes.