Dear All,
Within this screen I am getting the same person in the
same databases duplicated.
Is this
1. The fault of the application thats creating the
connection
2. SQL Server doing something
3. Something that I shouldn't be worried about.
Thanks
PeterHi,
First one will happen if you are not closing the connection made. This you
need to
really worry and need to rectify inside your code by closing the connection
as soon as the task is completed.
2. SQL Server will create mutiple threads, those you do not want to worry.
It will be cleared automatically
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
> Dear All,
> Within this screen I am getting the same person in the
> same databases duplicated.
> Is this
> 1. The fault of the application thats creating the
> connection
> 2. SQL Server doing something
> 3. Something that I shouldn't be worried about.
> Thanks
> Peter|||Thanks Hari
Peter
>--Original Message--
>Hi,
>First one will happen if you are not closing the
connection made. This you
>need to
>really worry and need to rectify inside your code by
closing the connection
>as soon as the task is completed.
>
>2. SQL Server will create mutiple threads, those you do
not want to worry.
>It will be cleared automatically
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
>
>.
>
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Wednesday, March 21, 2012
Dynanic table creation
Hello friends
I want to have a trigger for creating table on dialy basis.
for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on.
please help for writing the trigger for the same.
thanks
This seems like a job for SQL Server Agent, not a trigger. I'dwrite a stored procedure to create the table, and schedule it via SQLServer Agent to run every day.
|||
Your dynamic CREATE TABLE statement would look something like this:
And you can use Enterprise Manager to access SQL Server Agent toschedule the job. Click on your server name, then chooseManagement. Under that, click on SQL Server Agent. Underthat, right-click on Jobs and choose New Job. Enter the nameyou'd like to call this job, enter a T-SQL step to EXECUTE your storedprocedure, and set the Schedule.
|||Thank you very much sir for the details reply and the solution.
I want to have a trigger for creating table on dialy basis.
for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on.
please help for writing the trigger for the same.
thanks
This seems like a job for SQL Server Agent, not a trigger. I'dwrite a stored procedure to create the table, and schedule it via SQLServer Agent to run every day.
|||
Thank you very much.....
Could you please tell me how should I create dynamic table in the query? like, I have to append the today's date to a fixed string and create the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?
Please reply back
swatib wrote:
Thank you very much.....
Couldyou please tell me how should I create dynamic table in the query?like, I have to append the today's date to a fixed string andcreate the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?
Please reply back
Your dynamic CREATE TABLE statement would look something like this:
DECLARE @.Sql varchar(200)
SELECT @.Sql = 'CREATE TABLE Data' + REPLACE(CONVERT(char(12),GETDATE(),113),' ','') + '(column1 varchar(10), column2 int)'
EXECUTE(@.Sql)
And you can use Enterprise Manager to access SQL Server Agent toschedule the job. Click on your server name, then chooseManagement. Under that, click on SQL Server Agent. Underthat, right-click on Jobs and choose New Job. Enter the nameyou'd like to call this job, enter a T-SQL step to EXECUTE your storedprocedure, and set the Schedule.
|||Thank you very much sir for the details reply and the solution.
Sunday, March 11, 2012
Dynamically determining the SQL statement?
Hello all,
I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.
Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.
With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).
Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).
BTW...currently using SSRS 2005 + ASP.NET 2.0.
Thanks for your help!!
-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>
Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...
I guess the *best* way to set the command text and datasource from the front end is the use parameters?
Are there any other possible solutions?
Thanks!
-Brian
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
>
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 tables
I have a dynamic sql which uses "select into" to create a temp table. It has
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )
dynamically creating temp table names
Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.
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 Creating Table Name and Copying To Linked Server Help
I have tables on Server A that are created on a monthly basis. A table name
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)
> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)
> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Dynamically creating SSIS package for each flat file
Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
Thanks,
Travis
Trav2003 wrote:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
Thanks,
Travis
1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.
Dynamically creating SQL Server Report
I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!
Holds.aspx
<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>
Holds.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["Season"] =null;
}if (Session["Season"] !=null)
{ConfigureCrystalReports();
}}
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
{
myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
myReport.Load(reportPath);
DataSet dataSet = DataSetConfiguration.Holds;
myReport.SetDataSource(dataSet);
CrystalReportViewer1.ReportSource = myReport;
}// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlSeason.SelectedIndex == 0)
{
Session["Season"] =null;
}
else
{
Session["Season"] = ddlSeason.SelectedValue;
ConfigureCrystalReports();
}
}
}
DataSetConfiguration.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
{
//
// TODO: Add constructor logic here
// }// Handles creating holds datasetpublic static DataSet Holds
{
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
SqlDataAdapter cmd =new SqlDataAdapter(query, conn);cmd.Fill(dataSet,"Holds");
return dataSet;
}
}
}
Thanks!
I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.
Holds.aspx
<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>
Holds.aspx.cs
using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } } DataSetConfiguration.cs is the same.
FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp
Dynamically creating report width
Has anyone found out how to dynamically create report width.
If I have 20 columns on a report all of 2cm, then report will be 40cm width.
If I display the columns based on specific criteria using the iif function and then setting the an expression for the Visibility Hidden property, the report could potentially reduce in width.
I cannot see how to build an expression for the report width or table width, to accommodate the potential change in the width of the report when columns are not shown, thus it pages incorrectly, leaving huge white space on the right of the report when columns are hidden... has anyone come across this requirement, and hopefully a solution for it?
Many Thanks people
Here is a link for dynamic columns.
http://www.codeproject.com/sqlrs/DynamicReport.asp
Dynamically creating new worksheets to an existing excel document
Hi All,
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
Thanks in advance
Regards
Raman Kohli
You have a few options.
You could write a VBA macro in your excel book that renders the report as excel and then use the VBA code to move the worksheet from the generated book to your master.xls
Your other option might be to use rs.exe. You will need to create a .rss file, which is basically a VB Script file with access to the RS object model to create the excel download. You could potentially add the code to move the worksheet to your master.xls here too. You then execute this from the command line.
Check this area of MSDN http://msdn2.microsoft.com/en-us/library/ms152908.aspx
Dynamically creating cube
Hi guys
I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.
I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.
Any help in pointing me to the right direction is appreciated.
Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.
any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically
Dynamically creating cube
Hi guys
I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.
I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.
Any help in pointing me to the right direction is appreciated.
Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.
any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically
Dynamically Creating Columns
I have an .aspx page where users can select columns from a table. I want to pass the Select statement to a SQL Reporting Services report, probably as a stored procedure, and open the report with the records for the columns selected as an Excel file. My problem is that I am not sure how to dynamically create the column headings and values in a table. Any thoughts on how to achieve this? Thanks!
HI,amiejoye:
When do you want to dynamically create the column heading? In report data retrieving or other period?
|||if you want to dynamically add headers then you can use code section in reporting service.
Else you can use drag and drop the fileds(clumn name ) that is comin from the dataset
|||I want to create the column headings and retrieve the records when I click Submit from the .aspx page. Basically, when I click Submit, that's how I want the age to open, with the data from the Select statement I created.
|||I think that should not be aproblem because the vb code section in the reporting service depends on the select query only
dynamically creating a select statement
I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:
ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END
Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.
There's two approaches that I can think of that might help you.
Firstly, it's entirely valid to include criteria such as
WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...
in the stored procedure.
However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:
declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)
set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''
if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end
if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end
... and so on for the other parameters ...
set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end
exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than
Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).
Let me know if you need any further assistance with this.
Iain
|||You can try this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchCurrency]
@.currencycode nchar(5) = null,
@.currencyname nchar(25) = null
as
begin
declare @.stmt nvarchar(max)
set @.stmt = 'SELECT currencycode, currencyname
FROM currencies where 1=1 '
IF @.currencycode IS NOT NULL
set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''
IF @.currencyname IS NOT NULL
set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''
exec(@.stmt)
end
|||I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.
See:
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Dynamically Creating "Features" Grid
I was given what at first seemed a simple task (and maybe it is, but with everything else on my plate, I can't seem to get my head wrapped around this)...create a grid or table something similar to below:
Plan A Plan B PlanC PlanD
Item1 yes yes no no
Item2 yes yes no no
Item3 yes no yes no
etc....
The "plans" are stored in one db table, the "items" in another. What I can't seem to get straight in my mind is how to relate the yess and nos and then generate a tabular layout similar to the above.
I don't need anyone to do it for me, but a push in the right direction would sure be nice (using SQL 2005) Thanks!
Try googling for SQL Server 2005's PIVOT and UNPIVOT functions.Here's onearticle I found that should point you in the right direction.|||Yes...a day away and a quick read of the link you provided cleared the cobwebs. Thanks!
Friday, March 9, 2012
Dynamically Changing the DataSource of the Report
hi to all,
This is SidRogers,
i was newer to the this Reporting Services..
my Pbm is....
Creating the Report with or with out having any dataSource with SqlServer BI Studio.
I have some dummy fields in the Report..after I deployed into the ReportServer...
After that my Task is... Getting that ReportDefinition and Assinging the DataSource at the Runtime..in .Net frameWork 2.0...
upto My R & D.. i got one ReportDefintion.VB file.... I saw other forums. it said that. By using classes in this Vb file we can change the DataSource..if so may i know how...
this is very Urgent Work..
pleaseeeeeee urgently let me knoww..if u know any body....
thax in Advance,
Hi Sid-
You can set data source properties of a report through VB script, using the rs.exe tool, or you can alter it via the SOAP API from any C#/VB application. For more information on using script, some samples can be found here: http://msdn2.microsoft.com/en-us/library/ms160854.aspx
-JonHP
dynamically changing default parameter?
I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.
Can this be done?
here is how i'm setting my "from date" (datetime) param to be the first of the current month=DateSerial(Year(Now()), Month(Now()),1)
for today, just use
=Today()
this is for a whole date, you can just pick the year part
Sunday, February 26, 2012
Dynamic views
Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>
Dynamic Transformations
Hi
I have a dts that is creating a table with not a fixed number of columns. The number of colums depend on a couple of factors based on the data that I'm pulling from other tables.
After some processing I need to dump all the data in the "dynamic" table into an excel doc. My problem is with the transformations within the transform data task. I don't know how many fields I will have in my table and this needs to be mapped to columns within the excel doc. Is it possible to programmatically define the transformations within an activeX script or what can I do.
Thanks
Johnnie
SSIS does not support dynamic metadata. The best that could be done is to have a package for each dynamic type (if this is feasible, i.e. there are a finite number of dynamic types) and then have a master package that picks which of these "dynamic" packages to execute.
Matt
Subscribe to:
Posts (Atom)