Norman Yuan helpe me very much with changing colors on alternate rows, but
now I need help with a couple of other reports that use matrices.
Is there an ""on group change" event that I can test so that I can determine
when the group changes so that I can change the background color for all the
rows in that group?
Please let me know.
Thanks in advacne.I guess that you used ROWNUMBER(Nothing) now, simply use the
ROWNUMBER(Groupname) for putting the same logic in place for groups.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Changing a Primary Key to Foreign Key
I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Changing a Primary Key to Foreign Key
I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Changing a Primary Key to Foreign Key
I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Tuesday, March 20, 2012
changing "header rows to skip" property in flat file connection during runtime
Hi all
I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.
My expression is as follows:
DataRowsToSkip : @.[user:: Var]
where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.
I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.
Its not working....
Can anyone help me out in solving this?
Thanks in advance
Regards
Suganya
Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.
Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...
|||I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?
And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.
Thanks in advance.
Regards,
Suganya
|||
I've just had a go at this and it works fine.
Where is the rowcount component? Is it in the same data-flow?
-Jamie
|||
No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.
My package has the following tasks and components
1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination
2.Dataflow task(2) containing a flat file and an OLE DB destination
Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).
am i doing anything wrong?
Regards,
Suganya
|||
All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.
-Jamie
|||
Suganya Mahadevan wrote:
I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?
And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.
Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.
But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)
|||Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
But the same scenario is working when i set the value during design time and i have specified this in my previous post.
|||
No its 2 different files.
My package has the following tasks and components
1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination
2.Dataflow task(2) containing a flat file and an OLE DB destination
Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
What is the problem with my package?
Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
But the same scenario is working when i set the value during design time and i have specified this in my previous post.
Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?
Let's make sure first the variable is correctly populated...
|||Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)
By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.
Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?
|||
Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)
By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.
Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?
Put the breakpoint on the OnPreExecute event of the second data-flow.
When it breaks, drag the variable into a Watch window and you will be able to see the value.
-Jamie
|||
Thanks Jamie.The value is getting set.
But flat file is not skipping the number of rows.
Any solution for this?
Regards
Suganya
|||
Can you share the package and the source files? Paste them up here if so.
-Jamie
|||
It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?
-Suganya
changing "header rows to skip" property in flat file connection during runtime
Hi all
I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.
My expression is as follows:
DataRowsToSkip : @.[user:: Var]
where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.
I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.
Its not working....
Can anyone help me out in solving this?
Thanks in advance
Regards
Suganya
Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.
Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...
|||I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?
And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.
Thanks in advance.
Regards,
Suganya
|||
I've just had a go at this and it works fine.
Where is the rowcount component? Is it in the same data-flow?
-Jamie
|||
No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.
My package has the following tasks and components
1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination
2.Dataflow task(2) containing a flat file and an OLE DB destination
Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).
am i doing anything wrong?
Regards,
Suganya
|||
All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.
-Jamie
|||
I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?
And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.
Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.
But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)
|||Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
But the same scenario is working when i set the value during design time and i have specified this in my previous post.
|||
No its 2 different files.
My package has the following tasks and components
1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination
2.Dataflow task(2) containing a flat file and an OLE DB destination
Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
What is the problem with my package?
Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).
My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.
But the same scenario is working when i set the value during design time and i have specified this in my previous post.
Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?
Let's make sure first the variable is correctly populated...
|||Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)
By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.
Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?
|||
Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)
By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.
Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?
Put the breakpoint on the OnPreExecute event of the second data-flow.
When it breaks, drag the variable into a Watch window and you will be able to see the value.
-Jamie
|||
Thanks Jamie.The value is getting set.
But flat file is not skipping the number of rows.
Any solution for this?
Regards
Suganya
|||
Can you share the package and the source files? Paste them up here if so.
-Jamie
|||
It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?
-Suganya
sqlSaturday, February 25, 2012
change table orientation
I'm looking for a stored procedure what will change the orientation of a
table, so the columns will become the rows and the rows will become the
columns, for example:
Original Table:
StateName StateLongNames StateNumber
========= ============== ===========
CA California 10
FL Florida 20
TX Texas 30
The resulting table should be:
Col1 Col2 Col3 Col4
============== ========== =========== ==========
StateName CA FL TX
StateLongNames California Florida Texas
StateNumber 10 20 30
Note 1: This is not the same as pivot or crosstab tables.
Note 2: No agregated functions are necessary.
Note 3: It has to work on SQL Server 2000.
Thank you,Check this link
http://www.sqlteam.com/item.asp?ItemID=2955
Hope this helps.
--
"wheresaldo" wrote:
> I'm looking for a stored procedure what will change the orientation of a
> table, so the columns will become the rows and the rows will become the
> columns, for example:
> Original Table:
> StateName StateLongNames StateNumber
> ========= ============== ===========
> CA California 10
> FL Florida 20
> TX Texas 30
> The resulting table should be:
> Col1 Col2 Col3 Col4
> ============== ========== =========== ==========
> StateName CA FL TX
> StateLongNames California Florida Texas
> StateNumber 10 20 30
> Note 1: This is not the same as pivot or crosstab tables.
> Note 2: No agregated functions are necessary.
> Note 3: It has to work on SQL Server 2000.
> Thank you,
>|||hi... I saw this article a couple of days ago, it does not what at need, as
it creates a pivot table. what I need is much more simpler, just flit the
orientation of the table and all the data. This is NOT the same as Pivot,
Crosstab, or Transpond procedures, but easily
.
Thank you
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||A friend told me to do this, but it's outside my SQL knowledge:
1. Select count(*) for old_table. This will tell you how many columns to
create. Put this count into a variable (or user a cursor)
2. Build the string: "Create table new_table (col" + i + " varchar(50),
col" + ..... And execute it to create your table
3. Select the table structure to get the column names, use a cursor
3.a for each $col_name, Select $col_name, key from old_table order by key,
use cursor2
3.b build the string: "Insert into new_table values (" + $col_name + ", "
+ cursor2_value + ", " + cursor2_value, .... and execute.
3.c repeat loops
Does this make any sense to anyone?
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||You can do this with CASE expressions. See the following article for an
example. A query always returns a fixed number of columns so you will
always need to construct a query to match the appropriate number of
rows, either by hand or using dynamic SQL.
http://support.microsoft.com/defaul...b;EN-US;q175574
Hopefully you realize that this is generally a very silly to even
attempt in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I tried to apply the code you mentioned for the table below and it does not
seem to give the right result. Here is an example table:
CREATE TABLE #States
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO #States VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO #States VALUES ('OH','Ohio',2, 2.25)
INSERT INTO #States VALUES ('CA','California',3, 4.5)
INSERT INTO #States VALUES ('FL','Florida',1, 13.40)
INSERT INTO #States VALUES ('FL','Florida',3, 4.50)
As you can see, there is no unique id to use.
Here is how the table looks like:
StateCode StateName StateNumber StateCost
WI Wisconsin 1 45.11
OH Ohio 2 2.25
CA California 3 4.50
FL Florida 1 13.40
FL Florida 3 4.50
and I need it to look rotated or flipped:
Col1 Col2 Col3 Col3 Col4
WI OH CA FL FL
Wisconsin Ohio California Florida Florida
1 2 3 1 3
45.11 2.25 4.50 13.40 4.50
as you can see, the columns of the resulting table will have to receive
different data types, so I was thinking they can be all vchar(50). Also, as
the number of rows from the original table might vary, the resulting table
might have different amount of columns.
I could do the rotation or flip on the UI or front-end, but the reporting
tool I'm using expects a table... this is why I'm trying to do this on a
Stored Procedure.
I'm surpriced that I have not been able to find this on the web, I think
many people might have the same need.
"David Portas" wrote:
> You can do this with CASE expressions. See the following article for an
> example. A query always returns a fixed number of columns so you will
> always need to construct a query to match the appropriate number of
> rows, either by hand or using dynamic SQL.
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Hopefully you realize that this is generally a very silly to even
> attempt in SQL.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||wheresaldo wrote:
> I tried to apply the code you mentioned for the table below and it does no
t
> seem to give the right result. Here is an example table:
>
I suspect that the reason you find this difficult is that your existing
data doesn't have enough information to solvet he problem. What is the
key of your table? Every table needs a key but you haven't specified
one.
Now, in your rotated output you have put "WI" in column 1 and "OH" in
column 2. But where did you get the idea that "WI" came first and "OH"
came second? That information (1st, 2nd, etc) isn't in your table at
all! So you can't possibly write a generic query to return that result.
Lastly, what is the key in the result you want to ouput? You originally
asked for the column names in col1 but those don't exist in the data
either so you'll have to supply them in your query. Without a key you
may have a hard time constructing any useful information from the
output.
The first example in the link I posted shows how you can do it with
subqueries. By implication you need to supply the missing information
as part of those subqueries.
> I could do the rotation or flip on the UI or front-end, but the reporting
> tool I'm using expects a table... this is why I'm trying to do this on a
> Stored Procedure.
For sure you'll need to supply a tabular result set to the reporting
tool. But every reporting tool I know of will construct this kind of
pivot table from the base data - provided of course that you supply
enough information to complete the task.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you input. Here is the code to rotate (or change the orientation
)
of a table. I'm not sure if it's the most effective and well-written code,
but it does exactly what I need it to do. Hope it can help someone else.
Feedback is welcome:
-- ########################################
######
-- Drop tables if already created.
-- Cannot use temp tables, becuase cannot
-- read structure.
-- ########################################
######
DROP TABLE source
DROP TABLE result
-- ########################################
######
-- create fake source table
-- ########################################
######
CREATE TABLE source
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO source VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO source VALUES ('OH','Ohio',2, 2.25)
INSERT INTO source VALUES ('CA','California',3, 4.5)
INSERT INTO source VALUES ('FL','Florida',1, 13.40)
INSERT INTO source VALUES ('FL','Florida',3, 4.50)
-- ########################################
######
-- Create result table.
-- The result table needs to have the same
-- number of columns as the source table
-- has rows, we do this dynamically.
-- Because a columns in the result table might
-- be pf different data types, each
-- column in the result table is a nvarchar(50).
-- ########################################
######
declare @.counter int
declare @.sql_create_table nvarchar(4000)
declare @.source_total_rows int
select @.source_total_rows=count(*) from source
set @.sql_create_table = 'CREATE TABLE result (columnheader varchar(100)'
set @.counter = 0
while @.counter < @.source_total_rows
begin
set @.counter = @.counter + 1
set @.sql_create_table = @.sql_create_table + ',col' + cast(@.counter as
char(1)) + ' varchar(50)'
end
set @.sql_create_table = @.sql_create_table + ')'
exec (@.sql_create_table)
-- ########################################
######
-- First Loop through the number of column from
-- the source table, we need this to get the
-- column names.
-- Second Loop reads each row from the source,
-- for one column, and then create an insert
-- statement to insert in the result table.
-- ########################################
######
declare @.sql_create_inserts nvarchar(4000)
declare @.source_column_name nvarchar(50)
declare @.source_number_of_columns int
declare @.temp_column_hold nvarchar(50)
SELECT @.source_number_of_columns = count(*)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'source'
set @.counter = 0
while @.counter < @.source_number_of_columns
begin
set @.counter = @.counter + 1
--#####################################
set @.source_column_name = COL_NAME(OBJECT_ID('source'), @.counter)
exec('declare cur cursor for select '+@.source_column_name+' from source')
open cur
fetch next from cur into @.temp_column_hold
set @.sql_create_inserts = 'insert into result values (''' +
@.source_column_name + ''''
while @.@.FETCH_STATUS = 0
begin
set @.sql_create_inserts = @.sql_create_inserts + ',''' +
cast(@.temp_column_hold As varchar(50)) + ''''
fetch next from cur into @.temp_column_hold
end
set @.sql_create_inserts = @.sql_create_inserts + ')'
exec(@.sql_create_inserts)
close cur
deallocate cur
--#####################################
end
select * from result
"David Portas" wrote:
> wheresaldo wrote:
> I suspect that the reason you find this difficult is that your existing
> data doesn't have enough information to solvet he problem. What is the
> key of your table? Every table needs a key but you haven't specified
> one.
> Now, in your rotated output you have put "WI" in column 1 and "OH" in
> column 2. But where did you get the idea that "WI" came first and "OH"
> came second? That information (1st, 2nd, etc) isn't in your table at
> all! So you can't possibly write a generic query to return that result.
> Lastly, what is the key in the result you want to ouput? You originally
> asked for the column names in col1 but those don't exist in the data
> either so you'll have to supply them in your query. Without a key you
> may have a hard time constructing any useful information from the
> output.
> The first example in the link I posted shows how you can do it with
> subqueries. By implication you need to supply the missing information
> as part of those subqueries.
>
> For sure you'll need to supply a tabular result set to the reporting
> tool. But every reporting tool I know of will construct this kind of
> pivot table from the base data - provided of course that you supply
> enough information to complete the task.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I would also take care of the limitations:
- number of columns cannot exceed 1000
- row must fit on a page|||wheresaldo wrote:
> Thanks for you input. Here is the code to rotate (or change the orientati
on)
> of a table. I'm not sure if it's the most effective and well-written code
,
> but it does exactly what I need it to do. Hope it can help someone else.
> Feedback is welcome:
>
This bit is dangerous:
> exec('declare cur cursor for select '+@.source_column_name+' from source')
>
It retrieves the values in an undefined order. In itself that wouldn't
be too bad but because you go on to do the same for each column there
is absolutely no guarantee that the data for columns in the result will
line up with what was in each row in the source. What you should do is
add ORDER BY <some key>... but you still don't have a key in the source
table.
If you did have a key then you wouldn't need nested loops and cursors.
You could use a single query instead, which is generally preferable to
a cursor solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
table, so the columns will become the rows and the rows will become the
columns, for example:
Original Table:
StateName StateLongNames StateNumber
========= ============== ===========
CA California 10
FL Florida 20
TX Texas 30
The resulting table should be:
Col1 Col2 Col3 Col4
============== ========== =========== ==========
StateName CA FL TX
StateLongNames California Florida Texas
StateNumber 10 20 30
Note 1: This is not the same as pivot or crosstab tables.
Note 2: No agregated functions are necessary.
Note 3: It has to work on SQL Server 2000.
Thank you,Check this link
http://www.sqlteam.com/item.asp?ItemID=2955
Hope this helps.
--
"wheresaldo" wrote:
> I'm looking for a stored procedure what will change the orientation of a
> table, so the columns will become the rows and the rows will become the
> columns, for example:
> Original Table:
> StateName StateLongNames StateNumber
> ========= ============== ===========
> CA California 10
> FL Florida 20
> TX Texas 30
> The resulting table should be:
> Col1 Col2 Col3 Col4
> ============== ========== =========== ==========
> StateName CA FL TX
> StateLongNames California Florida Texas
> StateNumber 10 20 30
> Note 1: This is not the same as pivot or crosstab tables.
> Note 2: No agregated functions are necessary.
> Note 3: It has to work on SQL Server 2000.
> Thank you,
>|||hi... I saw this article a couple of days ago, it does not what at need, as
it creates a pivot table. what I need is much more simpler, just flit the
orientation of the table and all the data. This is NOT the same as Pivot,
Crosstab, or Transpond procedures, but easily
.Thank you
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||A friend told me to do this, but it's outside my SQL knowledge:
1. Select count(*) for old_table. This will tell you how many columns to
create. Put this count into a variable (or user a cursor)
2. Build the string: "Create table new_table (col" + i + " varchar(50),
col" + ..... And execute it to create your table
3. Select the table structure to get the column names, use a cursor
3.a for each $col_name, Select $col_name, key from old_table order by key,
use cursor2
3.b build the string: "Insert into new_table values (" + $col_name + ", "
+ cursor2_value + ", " + cursor2_value, .... and execute.
3.c repeat loops
Does this make any sense to anyone?
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||You can do this with CASE expressions. See the following article for an
example. A query always returns a fixed number of columns so you will
always need to construct a query to match the appropriate number of
rows, either by hand or using dynamic SQL.
http://support.microsoft.com/defaul...b;EN-US;q175574
Hopefully you realize that this is generally a very silly to even
attempt in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I tried to apply the code you mentioned for the table below and it does not
seem to give the right result. Here is an example table:
CREATE TABLE #States
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO #States VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO #States VALUES ('OH','Ohio',2, 2.25)
INSERT INTO #States VALUES ('CA','California',3, 4.5)
INSERT INTO #States VALUES ('FL','Florida',1, 13.40)
INSERT INTO #States VALUES ('FL','Florida',3, 4.50)
As you can see, there is no unique id to use.
Here is how the table looks like:
StateCode StateName StateNumber StateCost
WI Wisconsin 1 45.11
OH Ohio 2 2.25
CA California 3 4.50
FL Florida 1 13.40
FL Florida 3 4.50
and I need it to look rotated or flipped:
Col1 Col2 Col3 Col3 Col4
WI OH CA FL FL
Wisconsin Ohio California Florida Florida
1 2 3 1 3
45.11 2.25 4.50 13.40 4.50
as you can see, the columns of the resulting table will have to receive
different data types, so I was thinking they can be all vchar(50). Also, as
the number of rows from the original table might vary, the resulting table
might have different amount of columns.
I could do the rotation or flip on the UI or front-end, but the reporting
tool I'm using expects a table... this is why I'm trying to do this on a
Stored Procedure.
I'm surpriced that I have not been able to find this on the web, I think
many people might have the same need.
"David Portas" wrote:
> You can do this with CASE expressions. See the following article for an
> example. A query always returns a fixed number of columns so you will
> always need to construct a query to match the appropriate number of
> rows, either by hand or using dynamic SQL.
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Hopefully you realize that this is generally a very silly to even
> attempt in SQL.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||wheresaldo wrote:
> I tried to apply the code you mentioned for the table below and it does no
t
> seem to give the right result. Here is an example table:
>
I suspect that the reason you find this difficult is that your existing
data doesn't have enough information to solvet he problem. What is the
key of your table? Every table needs a key but you haven't specified
one.
Now, in your rotated output you have put "WI" in column 1 and "OH" in
column 2. But where did you get the idea that "WI" came first and "OH"
came second? That information (1st, 2nd, etc) isn't in your table at
all! So you can't possibly write a generic query to return that result.
Lastly, what is the key in the result you want to ouput? You originally
asked for the column names in col1 but those don't exist in the data
either so you'll have to supply them in your query. Without a key you
may have a hard time constructing any useful information from the
output.
The first example in the link I posted shows how you can do it with
subqueries. By implication you need to supply the missing information
as part of those subqueries.
> I could do the rotation or flip on the UI or front-end, but the reporting
> tool I'm using expects a table... this is why I'm trying to do this on a
> Stored Procedure.
For sure you'll need to supply a tabular result set to the reporting
tool. But every reporting tool I know of will construct this kind of
pivot table from the base data - provided of course that you supply
enough information to complete the task.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you input. Here is the code to rotate (or change the orientation
)
of a table. I'm not sure if it's the most effective and well-written code,
but it does exactly what I need it to do. Hope it can help someone else.
Feedback is welcome:
-- ########################################
######
-- Drop tables if already created.
-- Cannot use temp tables, becuase cannot
-- read structure.
-- ########################################
######
DROP TABLE source
DROP TABLE result
-- ########################################
######
-- create fake source table
-- ########################################
######
CREATE TABLE source
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO source VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO source VALUES ('OH','Ohio',2, 2.25)
INSERT INTO source VALUES ('CA','California',3, 4.5)
INSERT INTO source VALUES ('FL','Florida',1, 13.40)
INSERT INTO source VALUES ('FL','Florida',3, 4.50)
-- ########################################
######
-- Create result table.
-- The result table needs to have the same
-- number of columns as the source table
-- has rows, we do this dynamically.
-- Because a columns in the result table might
-- be pf different data types, each
-- column in the result table is a nvarchar(50).
-- ########################################
######
declare @.counter int
declare @.sql_create_table nvarchar(4000)
declare @.source_total_rows int
select @.source_total_rows=count(*) from source
set @.sql_create_table = 'CREATE TABLE result (columnheader varchar(100)'
set @.counter = 0
while @.counter < @.source_total_rows
begin
set @.counter = @.counter + 1
set @.sql_create_table = @.sql_create_table + ',col' + cast(@.counter as
char(1)) + ' varchar(50)'
end
set @.sql_create_table = @.sql_create_table + ')'
exec (@.sql_create_table)
-- ########################################
######
-- First Loop through the number of column from
-- the source table, we need this to get the
-- column names.
-- Second Loop reads each row from the source,
-- for one column, and then create an insert
-- statement to insert in the result table.
-- ########################################
######
declare @.sql_create_inserts nvarchar(4000)
declare @.source_column_name nvarchar(50)
declare @.source_number_of_columns int
declare @.temp_column_hold nvarchar(50)
SELECT @.source_number_of_columns = count(*)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'source'
set @.counter = 0
while @.counter < @.source_number_of_columns
begin
set @.counter = @.counter + 1
--#####################################
set @.source_column_name = COL_NAME(OBJECT_ID('source'), @.counter)
exec('declare cur cursor for select '+@.source_column_name+' from source')
open cur
fetch next from cur into @.temp_column_hold
set @.sql_create_inserts = 'insert into result values (''' +
@.source_column_name + ''''
while @.@.FETCH_STATUS = 0
begin
set @.sql_create_inserts = @.sql_create_inserts + ',''' +
cast(@.temp_column_hold As varchar(50)) + ''''
fetch next from cur into @.temp_column_hold
end
set @.sql_create_inserts = @.sql_create_inserts + ')'
exec(@.sql_create_inserts)
close cur
deallocate cur
--#####################################
end
select * from result
"David Portas" wrote:
> wheresaldo wrote:
> I suspect that the reason you find this difficult is that your existing
> data doesn't have enough information to solvet he problem. What is the
> key of your table? Every table needs a key but you haven't specified
> one.
> Now, in your rotated output you have put "WI" in column 1 and "OH" in
> column 2. But where did you get the idea that "WI" came first and "OH"
> came second? That information (1st, 2nd, etc) isn't in your table at
> all! So you can't possibly write a generic query to return that result.
> Lastly, what is the key in the result you want to ouput? You originally
> asked for the column names in col1 but those don't exist in the data
> either so you'll have to supply them in your query. Without a key you
> may have a hard time constructing any useful information from the
> output.
> The first example in the link I posted shows how you can do it with
> subqueries. By implication you need to supply the missing information
> as part of those subqueries.
>
> For sure you'll need to supply a tabular result set to the reporting
> tool. But every reporting tool I know of will construct this kind of
> pivot table from the base data - provided of course that you supply
> enough information to complete the task.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I would also take care of the limitations:
- number of columns cannot exceed 1000
- row must fit on a page|||wheresaldo wrote:
> Thanks for you input. Here is the code to rotate (or change the orientati
on)
> of a table. I'm not sure if it's the most effective and well-written code
,
> but it does exactly what I need it to do. Hope it can help someone else.
> Feedback is welcome:
>
This bit is dangerous:
> exec('declare cur cursor for select '+@.source_column_name+' from source')
>
It retrieves the values in an undefined order. In itself that wouldn't
be too bad but because you go on to do the same for each column there
is absolutely no guarantee that the data for columns in the result will
line up with what was in each row in the source. What you should do is
add ORDER BY <some key>... but you still don't have a key in the source
table.
If you did have a key then you wouldn't need nested loops and cursors.
You could use a single query instead, which is generally preferable to
a cursor solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Friday, February 10, 2012
Change Notification needed in .NET
Hi,
Think I want an application (say in C#) that would say "Hello" when a
particular table in the database get 100 rows. For this I could use a
thread that would check the rowcount in every say 1 minute. But the
problem is 1 minute is too much for an interactive application. I can
use lower time interval but it would increase database activity.
Suppose rowcount cross 100 mark once or twice in a month but need to be
responded immediately. Worst 10000 pepole want "Hello" in their
application. Then all client application would be checking it and
hence increasing network traffic and database activity.
Is there any way in SQL Server 2005 that would enable us to get events
in .NET applications? In older SQL Server versions?
Thanks
MukeshYou might want to look into the "Query Notification" concept in 2005. You'd
get notified when a
modification in the table has been don in a way that the result of a query h
as changed, and it is
without polling. Not sure it would be the best thing here, but worth looking
into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>|||In older versions of SQL Server you could have a trigger that calls
XP_LOGEVENT. Then you can use an NT Event Log hook to get your
notification. I know Microsoft has some examples of hooking the Event Log
on their web site. I had some code lying around in java script that did
this, but can't find it at the moment.
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>
Think I want an application (say in C#) that would say "Hello" when a
particular table in the database get 100 rows. For this I could use a
thread that would check the rowcount in every say 1 minute. But the
problem is 1 minute is too much for an interactive application. I can
use lower time interval but it would increase database activity.
Suppose rowcount cross 100 mark once or twice in a month but need to be
responded immediately. Worst 10000 pepole want "Hello" in their
application. Then all client application would be checking it and
hence increasing network traffic and database activity.
Is there any way in SQL Server 2005 that would enable us to get events
in .NET applications? In older SQL Server versions?
Thanks
MukeshYou might want to look into the "Query Notification" concept in 2005. You'd
get notified when a
modification in the table has been don in a way that the result of a query h
as changed, and it is
without polling. Not sure it would be the best thing here, but worth looking
into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>|||In older versions of SQL Server you could have a trigger that calls
XP_LOGEVENT. Then you can use an NT Event Log hook to get your
notification. I know Microsoft has some examples of hooking the Event Log
on their web site. I had some code lying around in java script that did
this, but can't find it at the moment.
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>
Subscribe to:
Posts (Atom)