easy sql help please

can someone help me please... i'm a newbie to sql...

dbo.tbl_client
field names are q1_1, q1_2, q1_3, q1_4
where relat_to='manager

i need to find the average for these

i tried:
select sum(q1_1+q1_2+q1_3+q1_4/4) as average_q1
FROM dbo.tbl_client
WHERE relat_to='manager'

gave me a strange number!

also... how can i find the MAX for these answers? across multiple columns?

select MAX(??what goes here?) as q1_max
from dbo.tbl_client
where relat_to='manager'

thank you!!!!

michaelClassic order of operations, Michael. Division comes before addition, so your code is operating like this:
sum(q1_1+q1_2+q1_3+(q1_4/4))

You want this:
sum(q1_1+q1_2+q1_3+q1_4)/4

or maybe this
sum((q1_1+q1_2+q1_3+q1_4)/4)

(They may be equivalent. My brain is too fried to think about it this late in the day!)

You may get strange results if some of your columns contain nulls, or if your columns are integer values.

Not sure what you want by MAX. MAX value? MAX column value? Max average?

blindman|||um... okay... i REALLY did pass math... honest!

i was thinking that with all the damn time i put in front of this computer monitor that it would read my thoughts and KNOW what i wanted...

thanks for the syntax!

as to the Max value... i had multiple questions Q1_1, Q1_2, Q1_3,Q1_4

the answers for each can range from 0 to 7

the WHERE Clause is relat_to='manager' (next SQL will be relat_to='self' etc)

what i need is to know what the MAX score is for relat_to='manager' for all of the Q1 questions... so if a person is a 'manager' and their results for the questions were Q1_1=4, Q1_2=0,Q1_3=7, Q1_4=0 then the MAX should be "7" and (i'll swap out the MAX after and do the MIN too) the MIN will be "0"

please let me know if this makes sense and thank you for helping someone that you dont even know!!|||First create a new function:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

Then use this select statement:
Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman|||i'm obviously way over my head here.... i have no idea whatsoever how to create a function... i'm using Dreamweaver and its recordset interface and the database was an Access that was upsized.... i am committed to finsihing it this week and just may end up giving all this computer stuff up to becme a rodeo clown....

i'm searching now for how to create a function etc... i really do appreciate all you help!!! i'm just sorry that i'm not smart enough to implement it!!!!

michael|||You can just run this code against your database using Query Analyzer:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

That will create your function. You can call your function and pass parameters just like you would call any other function, except that you must specify the owner of the function, "dbo". The code I gave should work if you cut and paste:

Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman

...by the way, switching from computer professional to rodeo clown is considered at best a lateral move. Aim for Ringmaster, or some other management role to further your career.sql

Dynamics in SSIS?

I have a Table say

Table1

Col 1 Col2 Col 3

A X1 1

A X2 2

B Y1 3

C Z1 4

C Z2 5

( Col1 represents Entity names, Col2 represents there respective field name and Col3 represents the values of those field name)

How can i use the above Table1 and update table A, B, C

Ex:

Table A

X1 X2

1 2

Similarly Table B

Y1

3

and Table C

Z1 Z2

4 5

2 Question:

I was trying to use for each loop container. I am getting this error:

Error: Variable "User::ADOVar" does not contain a valid data object

I followed all the steps in the links

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

and check out other sources but unable to track where the problem is?

Can some body help me out please

Question 1:

This should work...

update TableA
set
x1=(select col3 from Table1 where col1='A' and col2='x1')
x2=(select col3 from Table1 where col1='A' and col2='x2')

Although I'm not sure why you would want to do something like this. Do you only plan on having one record each in TableA,B,C?

Question 2:

What exactly are you trying to do? Are you trying to add Table1 to a result set and then loop through each record and execute a dynamic update statement?

|||

Anthony

Question 1:

can I do this in SSIS using task?

Question 2:

Yes I was trying to loop thru the results set from Execute Task and process each row.

My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.

As in my previos example:

I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3

|||

AWM_dB wrote:

Question 1:

can I do this in SSIS using task?

Take a look at the Pivot / Unpivot components in the data flow.

AWM_dB wrote:

Question 2:

Yes I was trying to loop thru the results set from Execute Task and process each row.

My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.

As in my previos example:

I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3

The error makes it sound like the resultset isn't being set into the variable. Can you confirm that the variable is populated from the first Execute SQL Task by adding a Script Task between the Execute SQL and the For Each, and checking that the variable is not null? Also, verify that the variable is defined at package scope and not on each task.

Dynamicly load tables for DataSources/DataDestinations

Hi everybody,

The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.

I hope somebody can help me. Thanks.

Is the metadata of these tables the same? If so you could loop over the list of tables using a ForEach loop and execute the same data-flow for each one of them. Reply here, search this forum or read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx if you need help in doing that.

If the metadata is NOT the same then you'll need a seperate data-flow for each in which case there isn't much point in storing the name of the source tables somewhere.

If you are intent on dynamically setting the name of the table to extract from then this should help also: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically creating variable names

Ok, here it goes. I'll try and explain my situation as best as I can.
I would like to dynamically create variable names. We store our data in
monthly partitioned tables.......table_200501, table_200502...
I am creating a view that is a union of all of the partitioned tables. So
in order to do this I am creating a cursor that selects all tables from
information_schema where table_name like 'table_%'
Then once I have the table name I am creating a varchar that does my select
from the table that is passed into the cursor variable. In the end I will
have a varchar that looks like this
select....................from table_200501
union all
select...................from table_200502
Over time the varchar that I have created will run out of space since it can
only hold 8000 characters. So once the size of the varchar gets near 8000 I
would like to create a new variable named sqlstringn.........with n being
the next number available.
So just for sample I tried doing this but it wont work
declare @.counter int
set @.counter = 5
declare @.SQLString + @.counter as varchar(8000)
Obviously this doesn't work, but is there any other way to dynamically
create variable names?
Any help or suggestions are very much appreciated.
ThanksI did that once (dynamically creating variables), and it required creating
dynamic SQL within dynamic SQL.
You probably don't want that in a production application.
What you can do is to allocate, say three, variables and concatenate them on
execution. (ie: Execute (@.Var1 + @.Var2 + @.Var3) I know you are doing this
in a cursor, but the techinque should be the same.
I don't mean to stray, but is it possible to modify your design from
horizontal to vertical so you don't have to deal with so many tables?
"Andy" wrote:

> Ok, here it goes. I'll try and explain my situation as best as I can.
> I would like to dynamically create variable names. We store our data in
> monthly partitioned tables.......table_200501, table_200502...
> I am creating a view that is a union of all of the partitioned tables. So
> in order to do this I am creating a cursor that selects all tables from
> information_schema where table_name like 'table_%'
> Then once I have the table name I am creating a varchar that does my selec
t
> from the table that is passed into the cursor variable. In the end I will
> have a varchar that looks like this
> select....................from table_200501
> union all
> select...................from table_200502
> Over time the varchar that I have created will run out of space since it c
an
> only hold 8000 characters. So once the size of the varchar gets near 8000
I
> would like to create a new variable named sqlstringn.........with n bei
ng
> the next number available.
> So just for sample I tried doing this but it wont work
> declare @.counter int
> set @.counter = 5
> declare @.SQLString + @.counter as varchar(8000)
> Obviously this doesn't work, but is there any other way to dynamically
> create variable names?
> Any help or suggestions are very much appreciated.
> Thanks
>

dynamically creating temp table names

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @.l_personsUID int

select @.l_personsUID = 9842

create table ##Test1table /*then the @.l_personsUID */
(
resultset1 int

)

The key to the problem is that I want to use the variable
@.l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

> Thanks for you help.
> Billy|||billy_cormic@.hotmail.com (Billy Cormic) wrote in message news:<dd2f7565.0311251937.cf18cf9@.posting.google.com>...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
> Thanks for you help.
> Billy

You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynam...html#Sales_yymm

Simon|||I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<2jWwb.144035$ji3.17559@.twister.nyroc.rr.com>...
> "Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
> news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> > For example:
> > DECLARE @.l_personsUID int
> > select @.l_personsUID = 9842
> > create table ##Test1table /*then the @.l_personsUID */
> > (
> > resultset1 int
> > )
> > The key to the problem is that I want to use the variable
> > @.l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table9842 not ##Test1table.
> May I ask why?
> You can probably do this by dynamically building the string.
> But it's going to be messy.
>
> > Thanks for you help.
> > Billy|||>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.|||OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311261052.12098cb6@.posting.google.com>...
> >> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000. <<
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.

dynamically change db servers

hi all,

I am confronted with multiple database servers with database names DB-1, DB-2, etc., table names are all the same.
I'm passing the DBServerName and DBname as variables in my stored procedure.
Is there any better ways to choose different server names other than using EXEC as shown in the sp below?

Any comments are greatly appreciated ;)

CREATE PROCEDURE sp_MyTest
@.SERVERNAME varchar(50),
@.DBNAME varchar(50),
@.CUST_ID varchar(10),
AS
Begin
DECLARE @.strsql VARCHAR(800)

SET @.strsql='Select Cust_ID From ['+@.SERVERNAME+'].['+@.DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@.CUST_ID+'''
EXEC (@.strsql)
Endthats pretty much like i would go with|||Originally posted by Enigma
thats pretty much like i would go with

Hi,
One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

If in same database u can use databasename.username.objectname

irrespective of procedure where ever it is.|||Hi,
One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

If in same database u can use databasename.username.objectname

irrespective of procedure where ever it is.

i believe thats what he is doing when he says

SET @.strsql='Select Cust_ID From ['+@.SERVERNAME+'].['+@.DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@.CUST_ID+'''

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?
|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Um, by not changing your table names?
>
|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> I have created three trigger on three table. But the table names change
>> every year. Instead of changing 3 trigger every year, how can I change
>> it
>> more efficient? Thanks.
> Um, by not changing your table names?
>|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
--
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> I have created three trigger on three table. But the table names change
> >> every year. Instead of changing 3 trigger every year, how can I change
> >> it
> >> more efficient? Thanks.
> >
> > Um, by not changing your table names?
> >
> >
>|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>> Yes, I mean by not changing the table name in the triggers. Like a global
>> constant. Say I set up currentyear = 2004 and next year is I set up
>> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> >> I have created three trigger on three table. But the table names
>> >> change
>> >> every year. Instead of changing 3 trigger every year, how can I
>> >> change
>> >> it
>> >> more efficient? Thanks.
>> >
>> > Um, by not changing your table names?
>> >
>> >
>>
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Um, by not changing your table names?
>|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
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
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>

Dynamic transformation mapping in DTS

Hi,

I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.

Any ideas or possible workaround would be greatly appreciated.
Thank you very much.http://www.sqlservercentral.com/columnists/bknight/convertdb2.asp

HTH|||Hello,

Thank you for your reply, however I read the article for the link you supplied
and it doesn't solve my problem. My fault. I will elaborate and try to be clearer.

As you know, I need to export data from SQL tables to AS400 files(the SQL table has the same structure and column names as the file on the AS400).
I created a DTS that has 2 global variables for the source file and the destination table. I'm supplying a different source and destination file name every time I execute the DTS package(executed from a stored procedure).
In other words, on the first execution of the DTS, the global variables SQL server table A(source) and AS400 file B(destination) will be passed. On the second run, the variables will contain table name C and file name D. So on and so forth.
I need to re-create mapping every time the package is executed because I don't know the column names ahead of time.

In a nutshell, the data transformation task allows you to dynamically specify
a source and destination table name, but consequently, doesn't automatically generate the column mappings.

I don't think the problem lies in the fact that it's an SQL server to AS400 data transfer, but rather, how to create auto mappings based on a given source and destination.

Sorry if I'm being repetitive and I hope all this clarifies my issue.

Still hoping for a solution or workaround!
Thanks once again.|||In my experience Dynamic Mapping is not possible.

Can you link the databases and dynamically write the insert statement?

Can you create a vbsript to create a text file that always has the same layout but different sql source?

Then do your load on AS400 the same way?

Allow each system to control process on itself.

Just some possible suggestions.|||I'm afraid you're right about dynamic mapping not being possible.

Also, you seem to have some good suggestions to a workaround, however
you would need to elaborate because I don't know how to implement them.

Can you provide one or more examples?

Appreciate the response.

Dynamic Tables Names and Temporary Tables Options

Firstly I consider myself quite an experienced SQL Server user, and
am
now using SQL Server 2005 Express for the main backend of my
software.

My problem is thus: The boss needs to run reports; I have designed
these reports as SQL procedures, to be executed through an ASP
application. Basic, and even medium sized (10,000+ records) reporting
run at an acceptable speed, but for anything larger, IIS timeouts and
query timeouts often cause problems.

I subsequently came up with the idea that I could reduce processing
times by up to two-thirds by writing information from each
calculation
stage to a number of tables as the reporting procedure runs..
ie. stage 1, write to table xxx1,
stage 2 reads table xxx1 and writes to table xxx2,
stage 3 reads table xxx2 and writes to table xxx3,
etc, etc, etc
procedure read final table, and outputs information.

This works wonderfully, EXCEPT that two people can't run the same
report at the same time, because as one procedure creates and writes
to table xxx2, the other procedure tries to drop the table, or read a
table that has already been dropped...

Does anyone have any suggestions about how to get around this
problem?
I have thought about generating the table names dynamically using
'sp_execute', but the statement I need to run is far too long
(apparently there is a maximum length you can pass to it), and even
breaking it down into sub-procedures is soooooooooooooooo time
consuming and inefficient having to format statements as strings
(replacing quotes and so on)

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

All answers/suggestions/questions gratefully received.

Thanksbrstowe wrote:

Quote:

Originally Posted by

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

Isn't this your answer right here? Just CREATE TABLE #foo instead
of CREATE TABLE foo, etc. and each process will get their own local
version of the #foo table.|||On Oct 5, 7:07 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

brstowe wrote:

Quote:

Originally Posted by

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

>
Isn't this your answer right here? Just CREATE TABLE #foo instead
of CREATE TABLE foo, etc. and each process will get their own local
version of the #foo table.

please be honest and tell me if I have completely misunderstood...?
the temporary tables are session/process/user specific...

two users could essentially create/drop tables, and populate/delete
information from tables with exactly the name e.g. #temptable1 at
exactly the same time?

thanks

Dynamic table query for DBF files

Hi. I am using DBF files as sources for some tables in SQL Server. The problem is that the table names in the DBF files are not all the same (e.g. frame061, frame949). I want to know the names of the tables inside the DBF files. Is there a way to query the table names, something like "select table_name from information_schema.tables" in SQL Server? By the way, those DBF files came from FoxPro. Thanks!
Edit: I completely misread your question...|||

If you are working with individual DBF files (as opposed to the later DBC database containers, with which I'm not familiar), then it's my understanding that file = table, so you would need a list of *.DBF files in the file system folder that constitutes the database.

You could use the Foreach Loop container with the Foreach File enumerator to do something with each matching file in the folder. Or you could use the simple methods of System.IO within a Script task to get your list.

-Doug

|||I think the question has been misinterpreted. sorry. A single DBF contains 4 tables inside the single file. That means 1 file = 4 tables. What I would like to do is to get the names of the 4 tables INSIDE the single DBF file, using SSIS. Is there a possible solution to this? Thanks in advance!

Dynamic table query for DBF files

Hi. I am using DBF files as sources for some tables in SQL Server. The problem is that the table names in the DBF files are not all the same (e.g. frame061, frame949). I want to know the names of the tables inside the DBF files. Is there a way to query the table names, something like "select table_name from information_schema.tables" in SQL Server? By the way, those DBF files came from FoxPro. Thanks!
Edit: I completely misread your question...|||

If you are working with individual DBF files (as opposed to the later DBC database containers, with which I'm not familiar), then it's my understanding that file = table, so you would need a list of *.DBF files in the file system folder that constitutes the database.

You could use the Foreach Loop container with the Foreach File enumerator to do something with each matching file in the folder. Or you could use the simple methods of System.IO within a Script task to get your list.

-Doug

|||I think the question has been misinterpreted. sorry. A single DBF contains 4 tables inside the single file. That means 1 file = 4 tables. What I would like to do is to get the names of the 4 tables INSIDE the single DBF file, using SSIS. Is there a possible solution to this? Thanks in advance!

dynamic table names in stored procedure...

Hello all,

Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name?

Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

Cheers,
Justinyou will have to create a dynamic query something on these lines

declare @.table nvarchar(20)
set @.table = 'Customers'
declare @.sql nvarchar(100)
set @.sql = 'select * from ' + @.table
exec sp_executesql @.sql

i used the northwind database as an example for this and this example works on the customers table...

so if your column names will not make a difference then you will need to create a dynamic query on these lines and execute it|||http://www.sommarskog.se/dynamic_sql.html|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.

Paranoia ... a man after my own heart.

Dynamic table names

Hi all,
how can I generate a temporary table name dynamically? I am selecting
and idnumber from another table. and I need to insert bunch of data to
a #Temptable but it must have that idnumber at the end.
Any ideas?
t.Uh, why not put the id number in a column instead of making it part of the
name?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1169244127.681152.279510@.a75g2000cwd.googlegroups.com...
> Hi all,
> how can I generate a temporary table name dynamically? I am selecting
> and idnumber from another table. and I need to insert bunch of data to
> a #Temptable but it must have that idnumber at the end.
> Any ideas?
> t.
>

Dynamic table names

Hi all,
how can I generate a temporary table name dynamically? I am selecting
and idnumber from another table. and I need to insert bunch of data to
a #Temptable but it must have that idnumber at the end.
Any ideas?
t.
Uh, why not put the id number in a column instead of making it part of the
name?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1169244127.681152.279510@.a75g2000cwd.googlegr oups.com...
> Hi all,
> how can I generate a temporary table name dynamically? I am selecting
> and idnumber from another table. and I need to insert bunch of data to
> a #Temptable but it must have that idnumber at the end.
> Any ideas?
> t.
>