collecting values into a variable in a loop

Dear All,

Im trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = @.myString + , + (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop

END

For some reason though the @.myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop
END

Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!Hi

Your variable is null initially -
NULL +'Something' = NULL

HTH|||Also - you don't need a loop:

DECLARE @.myString AS VarChar(1000)

SELECT @.myString = ''

SELECT @.myString = @.myString + ', ' + companyName
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 3, LEN(@.myString)-2)

SELECT @.myString|||This code will handle if any of the companyName is null.

DECLARE @.myString AS VarChar(1000)
SELECT @.myString = ''

SELECT @.myString = @.myString + coalesce(companyName+',','')
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 1, LEN(@.myString)-1)

SELECT @.myString|||Nice one thanks a lot! A final obsticle and I'm there!

As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

Char: CR, Oct:15, Dec:13, Hex:d

Any ideas anyone?|||Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!|||add char(13) with ur string.
eg:

PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'|||Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks much
I would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>
|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>
|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master
>

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks muchI would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master[vbcol=seagreen]
>

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks muchI would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> > Hi;
> >
> > I came across this article in SQL Server Magazine (March2003),under
> Letters
> > section, pg7, but I don't understand how to go abt implement or use the
> tip:
> >
> > " schedule a wrapper stored procedure that executs a procedure in master
> for
> > each application database, such as;
> >
> > EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> >
> > This SP will apparently get info on DB growth, data-file, table stats,
> which
> > is what I want to get.
> >
> > er....can someone show me the way what I need to do ?
> >
> >
> > tks much
> >
> >
>|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> > I would suggest that you contact ed.parr@.royalmail.com to obtain the
> source
> > code for this procedure. Basically what you found was a letter
explaining
> > how if my store procedure, which is described in SQL Server magazine
> article
> > Avoiding the Redzone December 2002 was place in master it would simplify
> my
> > code. I don't think Ed Parr has ever shared the code for his sp he was
> > referring to in his letter to SQL Server Magazine.
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "pohkeat" <pohkeat@.hotmail.com> wrote in message
> > news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> > > Hi;
> > >
> > > I came across this article in SQL Server Magazine (March2003),under
> > Letters
> > > section, pg7, but I don't understand how to go abt implement or use
the
> > tip:
> > >
> > > " schedule a wrapper stored procedure that executs a procedure in
master
> > for
> > > each application database, such as;
> > >
> > > EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> > >
> > > This SP will apparently get info on DB growth, data-file, table stats,
> > which
> > > is what I want to get.
> > >
> > > er....can someone show me the way what I need to do ?
> > >
> > >
> > > tks much
> > >
> > >
> >
> >
>

Collecting data with profiler

I'm running profiler and collect data which I'll use later for index tuning
wizard.
I'm collecting eventClass,SPID and text Data in profiler. Because
application use procedures, text data in profiler looks like:
exec e_prikazNarIzdelka 'I0202','HRK'
exec e_prikazIzdPoNar 'I0202','EEK',NULL
and so on.
Is it usefull for index tuning wizard?
Or text data should be actual select, insert, or update statements which are
inside procedures?
If so, how can I collect that statements in profiler instead of executing
stored procedures statements?
Thank you,
SimonTheres is an option where you can tell that SQL Server will use the traces
for further use, should should use that, because additional metadata is
stored then. Furtheron you should trace the STMT Event, Transaction, Scans
and further on. A list of useful data can be found here:
http://blog.transactsql.com/2005_01_01_archive.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"simon" wrote:

> I'm running profiler and collect data which I'll use later for index tunin
g
> wizard.
> I'm collecting eventClass,SPID and text Data in profiler. Because
> application use procedures, text data in profiler looks like:
> exec e_prikazNarIzdelka 'I0202','HRK'
> exec e_prikazIzdPoNar 'I0202','EEK',NULL
> and so on.
> Is it usefull for index tuning wizard?
> Or text data should be actual select, insert, or update statements which a
re
> inside procedures?
> If so, how can I collect that statements in profiler instead of executing
> stored procedures statements?
> Thank you,
> Simon
>
>|||Hi Jens,
thank you for your answer.
So, that means that "exec e_prikazNarIzdelka 'I0202','HRK'" is not usefull
for index tuning wizard?
It will not go into procedure e_prikazNarIzdelka and look, which
select,update and insert statements are inside that procedure?
I read somewhere that eventClass and text Data should be enough for index
tuning wizard.
Statement was:" Don't capture more in your profiler trace than you need. The
only events and data columns required by the index tuning wizard include the
SQL:BatchCompleted and the RPC:completed events in the TSQL category and
the eventClass and Text data columns."
So I put only that into my profiler trace.
Regards,
Simon
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:4A526DCC-3167-4150-A349-CA8D3AE59367@.microsoft.com...
> Theres is an option where you can tell that SQL Server will use the traces
> for further use, should should use that, because additional metadata is
> stored then. Furtheron you should trace the STMT Event, Transaction, Scans
> and further on. A list of useful data can be found here:
> http://blog.transactsql.com/2005_01_01_archive.html
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "simon" wrote:
>

Collecting data from remote DBs

Got to start planning for a project that requires our system to collect data from different types of DB platforms remotely from our customers and store them in our SQL DB. Anybody know of any references I could read or where to start with this? I have a couple of ideas, but need to look at all the aspects of this to ensure that it's done correctly.

Thanks.What kind of database? Locating on what platform? How to remotely to access it? Through HTTP or something else?

In general, web service could be a possible solution.|||A couple of our clients are using SQL server, and others are using proprietary DB systems that have XML export capabilities. All are MS based systems and the data collection preference would be via http requests.

Thanks.

collecting data from different mdbs

Dear netters,

I'm trying to set up a db architecture for a community school environment with the following restrictions:
- each school uses a simple application, storing data in an an .mdb file
- school data must asynchronously update a central server (SQL server) that stores data from all the school files and publishes them on the www
- no permanent interner connection is guaranteed (schools work offline and update the server periodically)

I'm checking MS solutions for the above requirements. Is "replication" the right place to start? Should I look into some other capability of Ms products (wharehousing?)?

I would appreciate some guidelines on how to organise my search for solutions.

Thanx in advance,

Vas.Mail the req's and I'd be glad to consult...cheap rates!

No replication is not the place to start..

A entity relationship model is...

Actually a process model diagram is...|||come on, we're talking about school system here! they can't afford to pay teachers the rate that they deserve! ...and what are your rates? :)

but you're right, - process model combined with the erd will give us an idea here on how to tackle this.

Collecting Active Directory dates

Hi,

I've just started using ADSI to pull in info from our Active Directory
tree into SQL Server 2000. I've made the link ok, and can pull in most
fields (cn whenCreated etc) fine. However, there are some date fields
(the one I'm interested in is pwdLastSet) that are represented as a
long numeric string, which throws up an error when SQL tries to pull it
in. Is there an easy way to parse these fields into a standard
datetime field, or if not how do I force SQL to pull the numeric field
in, and convert it later?

TIA,
Ross"Ross Luker" <ross_luker@.hotmail.com> wrote in message
news:1104940631.745926.294600@.c13g2000cwb.googlegr oups.com...
> Hi,
> I've just started using ADSI to pull in info from our Active Directory
> tree into SQL Server 2000. I've made the link ok, and can pull in most
> fields (cn whenCreated etc) fine. However, there are some date fields
> (the one I'm interested in is pwdLastSet) that are represented as a
> long numeric string, which throws up an error when SQL tries to pull it
> in. Is there an easy way to parse these fields into a standard
> datetime field, or if not how do I force SQL to pull the numeric field
> in, and convert it later?
> TIA,
> Ross

It would probably be a good idea to give an example of one of the numeric
strings, and the date it represents - personally, I'm not at all familiar
with ADSI, although others here may be. I'm also not sure how you're pulling
the data - if you're using DTS, you could use a custom ActiveX
transformation, if the existing date transformation won't handle it; if
you're using a linked server, then a UDF might be one solution.

Simon|||Hi Simon,

If I look at AD data using the windows LDIFDE tool, there are some
fields such as the one below, which are retrieved ok:
whenChanged: 20041202105508.0Z - MSSQL formats this fine to 02/12/2004,
10:55

However, most of the date/time fields are in the format:
pwdLastSet: 127463655814071600
which I'm guessing is a counter in (maybe) seconds from some date, but
I can't find any info to prove this! Trying to pull this field in (I'm
using a linked server) results in an error "Could not convert the data
value due to reasons other than sign mismatch or overflow". As I said,
if I knew more about what the data in the fields are, I might be able
to work on transforming it!

Ross|||"Ross Luker" <ross_luker@.hotmail.com> wrote in message
news:1104943553.231483.173640@.c13g2000cwb.googlegr oups.com...
> Hi Simon,
> If I look at AD data using the windows LDIFDE tool, there are some
> fields such as the one below, which are retrieved ok:
> whenChanged: 20041202105508.0Z - MSSQL formats this fine to 02/12/2004,
> 10:55
> However, most of the date/time fields are in the format:
> pwdLastSet: 127463655814071600
> which I'm guessing is a counter in (maybe) seconds from some date, but
> I can't find any info to prove this! Trying to pull this field in (I'm
> using a linked server) results in an error "Could not convert the data
> value due to reasons other than sign mismatch or overflow". As I said,
> if I knew more about what the data in the fields are, I might be able
> to work on transforming it!
> Ross

It looks unlikely to be seconds since an epoch, since the number above would
be more than 4 billion years (I think - very quick calculation). You should
probably follow up on the ADSI side - in an AD newsgroup, perhaps - to find
out what the number represents.

Until you find out more details, you could use ISDATE() to put in a null (or
something else) for your import - it's not always reliable, but in this case
it should be OK:

select cast(case when isdate(pwdLastSet) = 0 then null else pwdLastSet end
as datetime) as pwdLastSet
from ADSI..LinkedTable

Simon