create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

Create an automatic SUM ROW

Hello.
In a previous program i used to create reports, I had the option to enter a "SUM ROW".

I saw that in RS2005 I can check the "add total" when using the report wizard and it will create for me a row with SUM for my column.

Is there a way to create a row like this automatically with out the wizard (a sum row for all my columns instead of creating a textbox with "sum" for each column)?

Thanks in advance,
Roy.

Hi,

nom this behaviour is influenced by the normal summing / grouping operations. A sum in a row has nothing to do with this. I don′t think that the standard behaviour of the wizrad cab be changed in Reporting Services.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Hi Jens, Thanks for the replay.

I don't want the "standard behaviour" of the wizard to change.
I'll like the "SUM" je is makeing in the entire row.
what i whanted to know is:
if i'm making my own report, not with the wizard, is there a simple way to create a sum row like the wizard with out creating a textbox for every column manually?

Thanks in advance,
Roy.
|||

After making the report with the wizard you can simply use the Expression =SUM(Fields!SomeValueinQuery1.Value + Fields!SomeValueinQuery2.Value + Fields!SomeValueinQuery3.Value +Fields!SomeValueinQuery4.Value) OR use the appropiate ReportItems!Textbox1.Value, but there is no function like in Excel which automatically tries to gues what you want to summarize (even this function in Excel sometime guesses wrong what you want to achieve :-) )

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Create a trigger to update a row that's been inserted or updated

Hi

Apologies if this is a silly question

I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData

After I insert or Update a row in this table I need to update the SearchData column

with

UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)

only for that Id

I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.

Regards

David

Instead of using the trigger better you can go with Computed Columns...

here it is,

Code Snippet

Createtable Customer

(

IdintNOTNULL,

Address1varchar(100)NOTNULL,

Address2varchar(100)NOTNULL,

Address3varchar(100)NULL,

Townvarchar(100)NOTNULL,

Countyvarchar(100)NULL,

Postcodevarchar(100)NOTNULL,

SearchDataasUPPER(ADDRESS1)+UPPER(ADDRESS2)+UPPER(TOWN)+UPPER(POSTCODE)PERSISTED --Persisted only used on SQL Server 2005

)

|||I would not recommend using the 'PERSISTED' keyword unless you are attempting to solve a particular problem.|||

Hi

That was really helpful.

Thanks

David

Create a Rolling summary field

I need to make a report that takes a rolling 12 month period.
Each row needs to show the sum of the current months value and all the 11 months prior.

For example, I have a count of items in their respective Months

2 - Jan 06
5 - Feb 06
2 - Mar 06
4 - April 06
2 - May 06
1 - June 06
2 - July 06
5 - Aug 06
2 - Sep 06
2 - Oct 06
4 - Nov 06
2 - Dec 06
2 - Jan 07
3 - Feb 07
1 - Mar 07

What I need as an output is

Jan 07 = 35
Feb 07 = 33
Mar 07 = 32

So each row sums the current and previous 11 rows.

I am using Crystal Report v10 and SQL Database.

Thanks,
DavidThe quick solution in my mind is.

Create a subreport and store the whole data in an array.
Now come to the actual report and while showing the current record you can sum the previous 11 months records from the array and show it there.|||Great thanks for the Idea, I got it working with an array and then another formula summing the 12 in the array. I haven't played with the chart function yet, i have to get all these fields totaling first. Any suggestions getting all the fields in the details section ploted into a chart (the ultimate goal. Each displayed point is a date and the summed 12 month total.|||Keep one thing in mind while dealing with the charts.

You can show/draw more then one values on change of some value. e.g on change of date you can show the sum of previous 11 months.

Also you can get the accumulative .

You have to explore the charts for your project.|||Thanks so much for your help. Sometimes I just need a little bump into the right direction and I can figure it out. I got it working now.

Create a file using a SQL DB Trigger

Is there a way to create a text file (such as a Windows Notepad file)
by using a trigger on a table? What I want to do is to send a row of
information to a table where the table: tblFileData has only one
column: txtOutput
I want to use the DB front end (MS Access) to send the text string to
the SQL backend, then have the SQL Server create a file to a path,
such as F:/myfiledate.txt that holds the text in txtOutput, then the
trigger deletes the row in tblFileData.
Can this be done easily?
Any help is appreciatedHi

I am not sure why you should want to do this. If the transaction is rolled
back then the file will have different information to the database table.
You may want to look at the stored procedure xp_cmdshell, such as
http://tinyurl.com/64azq. Use of the echo command will write the information
into a file select @.sql = echo ' + @.v + ' >
\\Myserver\MyShare\myfiledate.txt'

Note: The example does not take into account multiple rows in the inserted
table.

John

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0407131443.33a873bb@.posting.google.c om...
> Is there a way to create a text file (such as a Windows Notepad file)
> by using a trigger on a table? What I want to do is to send a row of
> information to a table where the table: tblFileData has only one
> column: txtOutput
> I want to use the DB front end (MS Access) to send the text string to
> the SQL backend, then have the SQL Server create a file to a path,
> such as F:/myfiledate.txt that holds the text in txtOutput, then the
> trigger deletes the row in tblFileData.
> Can this be done easily?
> Any help is appreciated|||John,
Thanks for your reply.
In this application, the client needs to be able to make the data
"portable" to users who they do not want to have access to the
application itself. The idea is to create a small text document of a
small amount of data, then post the data onto a server where the users
can download this file onto a portable device. The file may be updated
repeadly, but at the time the user downloads it, it will be the latest
file. The file itself contains a small list of customer orders and
instructions for the user. I know this is not the ideal way to do this
but it makes sense for this customer within their business practices.
I have avoided using triggers at all in this rather large application,
but this seems like the point where I need to make the plunge.
What I have is a table tblFileData and it has the columns: txtOutput,
txtEmployeeName, txtOrderNumber, txtOrderDateTime.
What I need is to create a trigger so that when a record is inserted
into the table, the trigger fires and creates a .txt file into a
mapped path on the server
f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
txtOutput as the body of the file (example:
f:\20041225-987654-smithJohn.txt)
Then after creating the txt file, the trigger deletes the table row.

I have to admit I'm completely at ground zero when it comes to doing
this with a trigger so any help in constructing this is appreciated.
lq

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<GO7Jc.2822$Ec4.33496260@.news-text.cableinet.net>...
> Hi
> I am not sure why you should want to do this. If the transaction is rolled
> back then the file will have different information to the database table.
> You may want to look at the stored procedure xp_cmdshell, such as
> http://tinyurl.com/64azq. Use of the echo command will write the information
> into a file select @.sql = echo ' + @.v + ' >
> \\Myserver\MyShare\myfiledate.txt'
> Note: The example does not take into account multiple rows in the inserted
> table.
> John
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0407131443.33a873bb@.posting.google.c om...
> > Is there a way to create a text file (such as a Windows Notepad file)
> > by using a trigger on a table? What I want to do is to send a row of
> > information to a table where the table: tblFileData has only one
> > column: txtOutput
> > I want to use the DB front end (MS Access) to send the text string to
> > the SQL backend, then have the SQL Server create a file to a path,
> > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > trigger deletes the row in tblFileData.
> > Can this be done easily?
> > Any help is appreciated|||Hi

The example I posted would have given you just about everything you needed
to create these files although as I pointed out it is not necessarily going
to reflect the actual data! Usually this sort of task is carried out by a
scheduled (possibly DTS) job, although if you schedule it to run too often
it may be better to change it to an on demand process.

Books online will give you more details regarding DTS along with the
following site http://www.sqldts.com/default.aspx?200

Another alternative would be to provide a web interface.

John

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0407170613.22ad2ee0@.posting.google.c om...
> John,
> Thanks for your reply.
> In this application, the client needs to be able to make the data
> "portable" to users who they do not want to have access to the
> application itself. The idea is to create a small text document of a
> small amount of data, then post the data onto a server where the users
> can download this file onto a portable device. The file may be updated
> repeadly, but at the time the user downloads it, it will be the latest
> file. The file itself contains a small list of customer orders and
> instructions for the user. I know this is not the ideal way to do this
> but it makes sense for this customer within their business practices.
> I have avoided using triggers at all in this rather large application,
> but this seems like the point where I need to make the plunge.
> What I have is a table tblFileData and it has the columns: txtOutput,
> txtEmployeeName, txtOrderNumber, txtOrderDateTime.
> What I need is to create a trigger so that when a record is inserted
> into the table, the trigger fires and creates a .txt file into a
> mapped path on the server
> f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
> txtOutput as the body of the file (example:
> f:\20041225-987654-smithJohn.txt)
> Then after creating the txt file, the trigger deletes the table row.
> I have to admit I'm completely at ground zero when it comes to doing
> this with a trigger so any help in constructing this is appreciated.
> lq
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<GO7Jc.2822$Ec4.33496260@.news-text.cableinet.net>...
> > Hi
> > I am not sure why you should want to do this. If the transaction is
rolled
> > back then the file will have different information to the database
table.
> > You may want to look at the stored procedure xp_cmdshell, such as
> > http://tinyurl.com/64azq. Use of the echo command will write the
information
> > into a file select @.sql = echo ' + @.v + ' >
> > \\Myserver\MyShare\myfiledate.txt'
> > Note: The example does not take into account multiple rows in the
inserted
> > table.
> > John
> > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > news:47e5bd72.0407131443.33a873bb@.posting.google.c om...
> > > Is there a way to create a text file (such as a Windows Notepad file)
> > > by using a trigger on a table? What I want to do is to send a row of
> > > information to a table where the table: tblFileData has only one
> > > column: txtOutput
> > > I want to use the DB front end (MS Access) to send the text string to
> > > the SQL backend, then have the SQL Server create a file to a path,
> > > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > > trigger deletes the row in tblFileData.
> > > Can this be done easily?
> > > Any help is appreciated|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> In this application, the client needs to be able to make the data
> "portable" to users who they do not want to have access to the
> application itself. The idea is to create a small text document of a
> small amount of data, then post the data onto a server where the users
> can download this file onto a portable device. The file may be updated
> repeadly, but at the time the user downloads it, it will be the latest
> file. The file itself contains a small list of customer orders and
> instructions for the user. I know this is not the ideal way to do this
> but it makes sense for this customer within their business practices.
> I have avoided using triggers at all in this rather large application,
> but this seems like the point where I need to make the plunge.
> What I have is a table tblFileData and it has the columns: txtOutput,
> txtEmployeeName, txtOrderNumber, txtOrderDateTime.
> What I need is to create a trigger so that when a record is inserted
> into the table, the trigger fires and creates a .txt file into a
> mapped path on the server
> f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
> txtOutput as the body of the file (example:
> f:\20041225-987654-smithJohn.txt)
> Then after creating the txt file, the trigger deletes the table row.
> I have to admit I'm completely at ground zero when it comes to doing
> this with a trigger so any help in constructing this is appreciated.

If you don't know how to write this trigger, this is a good thing,
because that means that you will not do something which is really bad.

It could make sense to write to a text filr from a trigger for debugging
purposes, but for what you describe above, I go as far to say that this
is an unacceptable solution. There are two major problems:

1) The data you write to the file is uncommitted. If an error occurs
in the transaction, and it is rolled back, the user goes out on the
field with bogus data.

2) Forking out to write to files is slow, particularly if you will do
it for many rows - and you will have to iterate over them. This
could be a major bottleneck in your application.

The best would be to write a mini-app that accesses the databases and
gets data to the portable devices. If you absolutely must have files,
set up a job in SQL Agent which runs once a minute and gets rows and
updates the file with the most recent data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
As always, you've been very helpful and I appreciate your thoughts on
this.
In this installation, the design of the output is dictated by the
client because of other apps that will use this text file. It's a
first generation attempt on their part to deal with data that they
don't want the users to link to in any way.
So I'm stuck with it.
Also, they don't want users to have access to the path f:\ otherwise
I'd just do the whole thing in VBA with the Access front end.
My concept of this is that the trigger will fire on the inserting of a
new record in my table tblFileData - the trigger will create the file
and then the trigger will delete the row. It will not have to loop
through rows and there are only four columns in the table: txtOutput,
txtEmployeeName, txtOrderNumber, txtOrderDateTime.
The output needs to be:
f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
txtOutput as the body of the file (example:
f:\20041225-987654-smithJohn.txt)
The front end application handles the inserting of a row in
thbFileData every time a change is made to one form, another app (on I
don't have control over) overwrites any previous files, so the output
file will contain the most recent.

I am totally under the gun on this one. Is this trigger easy to
construct? I have never contsructed one and I'm at zero hour. Ouch.

lq

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952B28401363Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > In this application, the client needs to be able to make the data
> > "portable" to users who they do not want to have access to the
> > application itself. The idea is to create a small text document of a
> > small amount of data, then post the data onto a server where the users
> > can download this file onto a portable device. The file may be updated
> > repeadly, but at the time the user downloads it, it will be the latest
> > file. The file itself contains a small list of customer orders and
> > instructions for the user. I know this is not the ideal way to do this
> > but it makes sense for this customer within their business practices.
> > I have avoided using triggers at all in this rather large application,
> > but this seems like the point where I need to make the plunge.
> > What I have is a table tblFileData and it has the columns: txtOutput,
> > txtEmployeeName, txtOrderNumber, txtOrderDateTime.
> > What I need is to create a trigger so that when a record is inserted
> > into the table, the trigger fires and creates a .txt file into a
> > mapped path on the server
> > f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
> > txtOutput as the body of the file (example:
> > f:\20041225-987654-smithJohn.txt)
> > Then after creating the txt file, the trigger deletes the table row.
> > I have to admit I'm completely at ground zero when it comes to doing
> > this with a trigger so any help in constructing this is appreciated.
> If you don't know how to write this trigger, this is a good thing,
> because that means that you will not do something which is really bad.
> It could make sense to write to a text filr from a trigger for debugging
> purposes, but for what you describe above, I go as far to say that this
> is an unacceptable solution. There are two major problems:
> 1) The data you write to the file is uncommitted. If an error occurs
> in the transaction, and it is rolled back, the user goes out on the
> field with bogus data.
> 2) Forking out to write to files is slow, particularly if you will do
> it for many rows - and you will have to iterate over them. This
> could be a major bottleneck in your application.
> The best would be to write a mini-app that accesses the databases and
> gets data to the portable devices. If you absolutely must have files,
> set up a job in SQL Agent which runs once a minute and gets rows and
> updates the file with the most recent data.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> In this installation, the design of the output is dictated by the
> client because of other apps that will use this text file. It's a
> first generation attempt on their part to deal with data that they
> don't want the users to link to in any way.
> So I'm stuck with it.
> Also, they don't want users to have access to the path f:\ otherwise
> I'd just do the whole thing in VBA with the Access front end.
> My concept of this is that the trigger will fire on the inserting of a
> new record in my table tblFileData - the trigger will create the file
> and then the trigger will delete the row.

Huh? So the row is not to be persisted? In that case, you could use
an INSTEAD OF trigger. An INSTEAD OF triggers sets instead of the command,
which means that if you don't redo the command in the trigger, the command
will not be carried out. The main target for INSTEAD OF triggers are views,
so a trigger in that case would divert data to the appropriate rows. But
you could use it for anything.

> It will not have to loop through rows

Since a trigger fires once per statement, and thus can cover many rows
your code must be able to handle multi-row inserts.

> I am totally under the gun on this one. Is this trigger easy to
> construct? I have never contsructed one and I'm at zero hour. Ouch.

It's too easy to construct. In a trigger you have the virtual tables
"inserted" and "deleted" to play with. (In an INSERT trigger, only
"inserted".) These tables holds the before- ("deleted") and
after-image ("inserted") of the rows affected rows. Then you can use
xp_cmdshell to execute DOS commands, including writing to a file.

The catch here, is that you need to grant users access to xp_cmdshell,
which is in master. But once you have done this, a user that manage to
find a query tool can log in and run whatever commands he like with
xp_cmdshell. That's why I say it too easy.

One way to address this is write a wrapper stored procedure that you
place in master which accepts the arguments. This wrapper would then
call xp_cmdshell, and you would grant access to the wrapper. You would
have to enable cross-database ownership chaining for this to work.
Which again is a security consideration to think twice over.

Yet another alternative is to write your own extended stored procedure
that writes to the file, but this requires C programming skills.

Maybe you should rethink and find another solution, after all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> The only problem is, I need to create a file that only contains text
> from one of the columns, a column names OutputText.
> DO you know how I can do this?

You will need to use a format file with your BCP command. The format
file for this particular case would look like this:

8.0
1 SQLCHAR 0 0 "\r\n" ? "" ""

For the question mark replace the number of the column you want to export
to the file. (Numbers start at 1.)

The last two columns that I've just set as "" are the column name
(informational only to BCP) and the collation for the column in the
file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John,
OK, I trashed the isea of a trigger and have adopted your suggection
of using xp_cmdshell from a stored procedure. Whenever the data is
inserted into the table in the SP, the same SP calls xp_cmdshell.

This is what I have done...
I have created a view named vOutput that shows one column in a table.
I insert a row into the table and then I'm using this code to create a
file with the text in the single row.

This code works fine when I'm signed on with an account that has
server admin rights, however it fails when I signin with an account
that does not have server admin rights.

Can you shed some light on solving this?

Alter PROCEDURE OutputOrders
@.FileName nvarchar(50)

AS
set nocount on

DECLARE @.ReturnCode int
DECLARE @.ExportCommand varchar(255)

SET @.ExportCommand =
'BCP myServerName.dbo.vOutput out "c:\output\order files\' +
@.TemplateFileName +
'" -T -c -S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master.dbo.xp_cmdshell @.ExportCommand

I have granted execute permission to xp_cmdshell for both users and on
the view.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<GO7Jc.2822$Ec4.33496260@.news-text.cableinet.net>...
> Hi
> I am not sure why you should want to do this. If the transaction is rolled
> back then the file will have different information to the database table.
> You may want to look at the stored procedure xp_cmdshell, such as
> http://tinyurl.com/64azq. Use of the echo command will write the information
> into a file select @.sql = echo ' + @.v + ' >
> \\Myserver\MyShare\myfiledate.txt'
> Note: The example does not take into account multiple rows in the inserted
> table.
> John
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0407131443.33a873bb@.posting.google.c om...
> > Is there a way to create a text file (such as a Windows Notepad file)
> > by using a trigger on a table? What I want to do is to send a row of
> > information to a table where the table: tblFileData has only one
> > column: txtOutput
> > I want to use the DB front end (MS Access) to send the text string to
> > the SQL backend, then have the SQL Server create a file to a path,
> > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > trigger deletes the row in tblFileData.
> > Can this be done easily?
> > Any help is appreciated|||Hi

This is direct from books online. I assume that it is a permissions problem
on xp_cmdshell and not on the directory itself:
"By default, only members of the sysadmin fixed server role can execute this
extended stored procedure. You may, however, grant other users permission to
execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server."

If this is not a xp_cmdshell permission problem then it could be that access
to the directory for the SQL Server Agent proxy account is not valid or the
user account that started SQL Server for windows 9.x.

As debug option you may want to execute a "dir C:\*" command

John

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0407230525.2fc6209b@.posting.google.c om...
> John,
> OK, I trashed the isea of a trigger and have adopted your suggection
> of using xp_cmdshell from a stored procedure. Whenever the data is
> inserted into the table in the SP, the same SP calls xp_cmdshell.
> This is what I have done...
> I have created a view named vOutput that shows one column in a table.
> I insert a row into the table and then I'm using this code to create a
> file with the text in the single row.
> This code works fine when I'm signed on with an account that has
> server admin rights, however it fails when I signin with an account
> that does not have server admin rights.
> Can you shed some light on solving this?
>
> Alter PROCEDURE OutputOrders
> @.FileName nvarchar(50)
> AS
> set nocount on
> DECLARE @.ReturnCode int
> DECLARE @.ExportCommand varchar(255)
> SET @.ExportCommand =
> 'BCP myServerName.dbo.vOutput out "c:\output\order files\' +
> @.TemplateFileName +
> '" -T -c -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master.dbo.xp_cmdshell @.ExportCommand
>
> I have granted execute permission to xp_cmdshell for both users and on
> the view.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<GO7Jc.2822$Ec4.33496260@.news-text.cableinet.net>...
> > Hi
> > I am not sure why you should want to do this. If the transaction is
rolled
> > back then the file will have different information to the database
table.
> > You may want to look at the stored procedure xp_cmdshell, such as
> > http://tinyurl.com/64azq. Use of the echo command will write the
information
> > into a file select @.sql = echo ' + @.v + ' >
> > \\Myserver\MyShare\myfiledate.txt'
> > Note: The example does not take into account multiple rows in the
inserted
> > table.
> > John
> > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > news:47e5bd72.0407131443.33a873bb@.posting.google.c om...
> > > Is there a way to create a text file (such as a Windows Notepad file)
> > > by using a trigger on a table? What I want to do is to send a row of
> > > information to a table where the table: tblFileData has only one
> > > column: txtOutput
> > > I want to use the DB front end (MS Access) to send the text string to
> > > the SQL backend, then have the SQL Server create a file to a path,
> > > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > > trigger deletes the row in tblFileData.
> > > Can this be done easily?
> > > Any help is appreciated

Create a column in RecordSet with the Record Count...

Hello

I'm reading a XML file and the next operation need a column with the row count.

I don't want to know how many rows there is in the recordset, but, the row count of each record.

How can I implement this?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

That issue was discussed here:

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

Crazy Row Numbering Poblem

I'm using ms sql 2000.
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
The Results are as follows for tblTest3:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:

>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> INSERT INTO #dup
> SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>The Results are as follows for tblTest3:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:

> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:

> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 ws and was pretty pleased I got as far as
I did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:

> You should be able to use your ID column to resolve duplicates.
> Try changing
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>

Counting the number of fields that are populated in row

I have 2 large tables that I'm selecting off of. part of my select is
to get the number of stores visited. The table that has this
information has the store names as columns. If a store wasn't visited
there will be a null value. I need to be able to count the number of
fields that don't have the null value in order to get the desired
result. I can't use the Count() function as the select statement is
very large. and I don't want to group by all of the fields that I'm
selecting.
Does anyone know of another way to get this?what data type are the store name columns?
numeric (of some sort) indicating number of visits?
bit indicating visited?
are you returning the store columns as well?
dmagoo22 wrote:
> I have 2 large tables that I'm selecting off of. part of my select is
> to get the number of stores visited. The table that has this
> information has the store names as columns. If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result. I can't use the Count() function as the select statement is
> very large. and I don't want to group by all of the fields that I'm
> selecting.
>
> Does anyone know of another way to get this?
>|||> to get the number of stores visited. The table that has this
> information has the store names as columns.
Egads. So if you add a store, you change your schema? That's really not
how it should work. The store names are *data* not *metadata*...

> If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result.
If this returns exactly one row, you can try this:
SELECT
col1,
col2,
NumberOfStoresVisited =
CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
+ CASE WHEN Store2 = NULL THEN 0 ELSE 1 END
+ ...
+ CASE WHEN StoreN = NULL THEN 0 ELSE 1 END
FROM
table
...
Without better specs, that's about as good as I can do. Please see
http://www.aspfaq.com/5006|||> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
WHOA! I meant WHEN Store1 IS NULL
*smack*|||try the @.@.rowcount Function
run the select statement to select where your column is not null and then
get the @.@.rowcount
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHOA! I meant WHEN Store1 IS NULL
> *smack*
>
>|||> try the @.@.rowcount Function
> run the select statement to select where your column is not null and then
> get the @.@.rowcount
Unfortunately, I think the OP's table looks like this:
Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1
So @.@.ROWCOUNT will always be 1...|||Oops!!!! Sorry thot the data was in multiple rows
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unfortunately, I think the OP's table looks like this:
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
> So @.@.ROWCOUNT will always be 1...
>
>

Counting rows

Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?that should work.

I think we're both missing something really obvious...

I don't start my temp table names with the _ symbol...

I usually use the # sign.

Originally posted by oneleg_theone
Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?|||My version of mssql does not allow table aliasses on updates.|||The tables names are example only, they are named something else. Too bad i named them "temp" in this example...

I already have some sql in a sp that updates tables using aliases so it's not that either... I thought. The first table must be without alias, then it works! But delivers the wrong result... It gives me total rowcount not the number of rows with the same serial..

Working query delivering wrong result:

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE SERIAL = T2.SERIAL)|||Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D|||dang.

like I said, something obvious - can't use alias name on update.

Originally posted by oneleg_theone
Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D

Counting rows

hi all

quick question

is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks

tiborPlease restate your requirement in a clearer way. Do you need the row no for each row ? If that is the case, what happens if a row is deleted ?

Counting items and returning values

I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB

Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION

I'd like the data to be returned as

XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1

How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name

HTH,
-Cliff

"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?