Losing it again - how to pass value to sp?

I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @.C_ID value.

PROCEDURE dbo.EditCompanyInfo
@.C_ID int,
@.CS_CompanyName nchar(100),
@.CS_City nchar(50)

AS
UPDATE tblCompanyInfo_Submit
SET CS_CompanyName = @.CS_CompanyName, CS_City = @.CS_City
WHERE C_ID = @.C_ID
RETURN

This is my aspx. page:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" >
<asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px"
Width="125px">
<Fields>
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted"
SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @.CompanyID "
UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</asp:Content>

CODE BEHIND:

public partial class aaatest : System.Web.UI.Page
{
int CompanyID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
TextBox1.Text = "insert";
TextBox3.Text = Convert.ToString(CompanyID);
}
}
protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
{
foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters)
{
string RValue = Server.HtmlEncode(param.Direction.ToString());
if ( RValue == "ReturnValue" && Page.IsPostBack)
{
TextBox1.Text = Server.HtmlEncode(param.Value.ToString());
TextBox2.Text = "Return";
CompanyID = Convert.ToInt16(TextBox1.Text);
TextBox3.Text = Convert.ToString(CompanyID);
}
}
}
}
}

This might help... although you'll need to adjust it to be UpdateParameters on an update etc.

SqlDataSource1.SelectParameters["ParamName"] =

newParameter("ParamName",TypeCode.String,"Value");sql

Losing double quotes?

Hi,

I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.

I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").

The vast majority of the records are formatted properly, and have the double quotes in the expected locations.

The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...

i apologize for being the worst ever at posting questions here, please let me know if i can add anything

Could you please post your code, its hard to uess what is goind wron without seeing your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

it is over 500 lines, but i could certainly post it once i get to work...

in the meantime, however, perhaps this might be useful:

I set a breakpoint at my function's final return statement and have observed the return value as follows:

Author of "Some random book." Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

This is how I'd like, and expect, the value to appear in my database, but after SELECTING the particular row, I get:

Author of Some random book. Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

I doubt this is much more revealing, and will be happy to post my code later, if thought to be any help.

|||

Another thing to add (probably useless):

The problem only occurs during an UPDATE. If I do:

SELECT dbo.EventBuilder([Title], [Author], [Published]) FROM MyTable

I get the correct output.

...getting desperate here

|||

Check your database hold the same result.

I can't able to understand where & what is your problem occurs..

looping thru a table and updating the contents

Hello, Its hard trying to explain this.

I have 3 tables

Table 1 is where the users are stored, each user has a username and a userrank

Table 2 is where the points that decides the userrank are stored

Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:
username nvarchar(20),
userrank int, -- Reference to Table3 id
... alot more fields

Table 2 (settings_profile) looks like this:
username nvarchar(20),
total_active_points int,
... some more fields

Table 3 (data_ranks) looks like this:
id int primary key auto inc,
rankname nvarchar(20),
min_pts int,
max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:

WHILE(SELECT @.username= username, @.id= id, @.temp1= ap_sentmails, @.temp2= ap_createdthreads, @.temp3= ap_createdanswers, @.temp4= ap_signguestbook, @.temp5= ap_blogcomment, @.temp6= ap_createblogentry, @.temp7= ap_profilefirsttime, @.temp8= ap_profilephoto, @.temp9= ap_activateguestbook, @.temp10= ap_addnewfriend, @.temp11= ap_superguruvote, @.temp12= ap_forumtopicvote, @.temp13= ap_labervote, @.temp14= ap_funstuffitemvote, @.temp15= ap_movievote, @.temp16= ap_actorvote, @.temp17= ap_money_newWHERE(created<Dateadd(dd,-7, @.todaysdate))
BEGIN
SET @.sum= 0
SET @.sum= @.temp1+ @.temp2+ @.temp3+ @.temp4+ @.temp5+ @.temp6+ @.temp7+ @.temp8+ @.temp9+ @.temp10+ @.temp11+ @.temp12+ @.temp13+ @.temp14+ @.temp15+ @.temp16+ @.temp17
UPDATE settings_profileSET total_active_points= total_active_points- @.sumWHERE(username= @.username)
DELETEFROM konto_daylistWHERE(id= @.id)
END

Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.
Something like this...

DECLARE @.username nvarchar(20)
DECLARE @.pts int, @.rank int

...something that starts a loop thru table A (user_list) and get the username into @.username...

SELECT @.pts =total_active_points FROM settings_profile WHERE (username = @.username)
-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranks
SELECT @.rank = id FROM data_ranks WHERE (pts_min => @.pts AND pts_max < @.pts)
UPDATE user_list SET rank = @.rank WHERE (username = @.username)

...next persion in the loop...

This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...

But how do i loop thru all the users? with a cursor?

I don't have time to write this for you, but here's the line of attack to use:

Create and test a query that computes the the correct score for each user.

then, issue an update in this format:

update settings_profile

set total_active_points = ( the query from above where settings_profile.username = other_table.username)

|||

Hi, i dont truly understand what you meant. I can compute the score for each user If i have their username, thats why i need to create a loop that loops thru every post in table A, so that i can update every single post with the rank they earned from the points. I need to find someway to loop thru all posts.

|||

No, you do not need to write a loop thru the users.

This is because the SQL Update statement provides an implied loop for you.

Example:

createtable xxx(idint, textvaluevarchar(20))

insertinto xxxvalues(1,'Nikki')

insertinto xxxvalues(2,'John')

update xxxset textvalue='Ginger'

select*from xxx

You will note that every xxx record now has Ginger in the textvalue column.

The update statement IS a loop!

And, while it is processing any given xxx record, it knows the value in each of that record's columns.

I do not have to hard-code the value that will be used to set textvalue with.

I can issue a query instead.

So, let's create a new table called nametable.

createtable nametable(idint,namevarchar(20))

insertinto nametablevalues(1,'Amber')

insertinto nametablevalues(2,'Tracy')

update xxx set textvalue = (select name from nametable where nametable.id = xxx.id)

select * from xxx

Note that the name for record 1 is now Amber and the name for record 2 is now Tracy

Hope that helps clarify things.

|||

Hi,

Its working like a clock now, thanks for the explanation.

I build this line with the example code you sent, and its brilliant.

UPDATE profile_publicinfoSET userrank=(SELECT data_userrank.idFROM settings_profileINNERJOIN data_userrankON(data_userrank.pointsmin<= settings_profile.total_active_pointsAND data_userrank.pointsmax> settings_profile.total_active_points)WHERE(settings_profile.username= profile_publicinfo.username))

|||

Great!

Now all you have to do is mark the thread as answered, so other folks don't spend their time trying to help you only to find out you don't need help anymore! :)

The key to being ultra-productive with SQL is to think in terms of "sets of data" instead of looping thru records of data one at a time.

|||

Then i just have to ask, is it possible to update several records with a IF statement?

Like this,

UPDATE clubsSET isactive=(IF(clubs_account.money-clubs_account.cost< 0SELECT 1) ELSE (SELECT 0)FROM clubs_account)

Thats money - expenses, if its less then 0, return a 1 otherwise return a 0

|||

The SQL equivalent of an IF statement is a CASE statement.

But don't try to write it the way your if statement was coded.

Try this instead:

update clubs
set isactive = (select case clubs_account.money - clubs_account.cost < 0
when true then 1
else 0
end case
from clubs_account
where clubs_account.? = clubs.?
)

|||

Looks very good, but i just keeps getting an error when i tried it (i also tried to put () around the math calculations, but i still get the same result),

The clubs.inactive is a bit field if that is of any importance.

UPDATE clubsSET isactive=(SELECTCASE club_konto.money- club_konto.cost< 0WHEN trueTHEN 1ELSE 0ENDCASEFROM club_kontoWHERE club_konto.clubid= clubs.id)

Incorrect syntax near '<'.

|||

bit fields are 1s and 0s, not true and false.

Also, I work in too many different languages. :( It's END, not END CASE.

createtable clubs(idint, isactivebit)

createtable club_konto( clubidint,moneyint, costint)

insertinto clubsvalues(1,1)

insertinto clubsvalues(2,1)

insertinto clubsvalues(3,0)

insertinto clubsvalues(4,0)

insertinto club_kontovalues(1,50,10)

insertinto club_kontovalues(2,10,50)

insertinto club_kontovalues(3,50,10)

insertinto club_kontovalues(4,10,50)

select*from clubs

select*from club_konto

UPDATE clubs

SET isactive=(SELECTCASEWHEN club_konto.money- club_konto.cost< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

select*from clubs

I have to caution you about this - the select statement used to populate the isactive column must NOT return more than one row of data.

If it can return more than one row, you need to change it so it only returns one row. So, if there can be two or more club_konto records per club, the query above will not work. Instead you would need it to be something like this:

UPDATE clubs

SET isactive=(SELECTCASEWHEN sum(club_konto.money)- sum(club_konto.cost)< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

|||

I see that i need to learn alot more about how data is handeled inside MS SQL 2005, its working perfect with your example,

Thanks alot

(I have afew more SPs with While loops that i have to go thru now and see if i can upgrade them according to the new update way.)

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
> >I have a temp table that is populated elsewhere in the stored proc:
> >@.table
> > (
> > userid,
> > spec1,
> > spec2
> > )
> >Spec1 = specialization 1, Spec2 = specialization2
> >Each user has two specializations, and a number of associated skills
> >for that specialization in a table.
> >now I need to loop through this table, pulling out only one userid at
> >a time, for my final query that displays a bunch of other things. I
> >have a UDF that concatenates the string of values from the different
> >rows from the skill table so that it displays the skills as one list:
> >Specialization: DBA
> >Skills: SQL, Data Migration, etc.
> >How do I loop through the @.table to pull out only one userid at a
> >time, then do the insert to another @.secondtable, then loop back
> >through to get another userid'
> >Stacy
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:
>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
>> Why would you want to loop?
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.
>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.
>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get! :)
--
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
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
--
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
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
>> I argue for application development based on science, relational.
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -:)
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
>> You have given some descriptions in English, but description
>> is real SQL code are much less prone to communication errors and
>> misunderstanding.
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -:)
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
>.
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
>.
'Naturally the new language would imply a new data model as well - the
relational data model instead of the SQL one - but I don't think it
would be difficult to build it on top of SQL Server's existing engine.'
Now I see where your coming from.
All your expertise, education and insight is secondary to your
allegience to MS. So much for your intellectual integrity, you
are, in the end, just another MS fanboy. MS see, David do.
Hypocrite.|||On 5 Jun, 21:01, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> 'Naturally the new language would imply a new data model as well - the
> relational data model instead of the SQL one - but I don't think it
> would be difficult to build it on top of SQL Server's existing engine.'
> Now I see where your coming from.
> All your expertise, education and insight is secondary to your
> allegience to MS. So much for your intellectual integrity, you
> are, in the end, just another MS fanboy. MS see, David do.
> Hypocrite.
Nothing of the kind. I'm suggesting that Microsoft improve their
product by remedying some of its defficiencies. The hypocracy is
committed by those who advocate another commercial product while
accusing others who propose a Microsoft-based solution of lacking
intellectual integrity. My opinion of you just hit rock bottom I'm
afraid.
--
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
--

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid?
Stacy
On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:

>I have a temp table that is populated elsewhere in the stored proc:
>@.table
>(
> userid,
>spec1,
>spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid?
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>
>
>
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =
candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =
candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
andcandidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...
|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:

>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)[vbcol=seagreen]
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.

>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
>inner join skill on skill.skillid = candidate_skillsmatrix.skillid
>inner join specialization on specialization.specializationid =
>candidate_skillsmatrix.specid
>inner join picklist on picklist.picklistid =
>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
>andcandidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.

>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get!
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
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve
|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
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
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegr oups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -
www.alphora.com
|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com.. .
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html
sql

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:

>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>
>
>
>
>
>
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted tex
t -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =
candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =
candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:

>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
(snip)[vbcol=seagreen]
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.

>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =
>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =
>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.

>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get!
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
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.
4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidpor...ibrary/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...[vbcol=seagreen]
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> Naturally I agree:
> http://blogs.conchango.com/davidpor...al-Futures.aspx[
/vbcol]
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.
4ax.com...
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.
4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -
[url]http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html[/u
rl]

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping through stored procedure inside another stored procedure and displaying the catego

I used to do this with classic asp but I'm not sure how to do it with .net.

Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category.

I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.

Just for an example..

Category Table:

ID Category
1 Saints
2 Falcons
3 Bucaneers
4 Chargers
5 Falcons

Player Table:

ID CategoryID Player News Player Last Updated
1 1 Reggie Bush Poetry in motion 9/21/2006
2 1 Drew Brees What shoulder injury? 9/18/2006
3 5 Michael Vick Break a leg, seriously. 9/20/2006

Basically I would need to display on a page:

Saints
Reggie Bush
Poetry in Motion

Falcons
Michael Vick
Break a leg, seriously.

So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.

I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

try building a query with sub-queries based on a join within the store procedure. test it in the query manager first then before making it a SP

looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

Looping through non system database objects using SMO and VB.net

Hi,

We are using SMO to compare objects in our SQL Server database with another instance of sql server. I'm able to loop thourgh the stored procedures with no problem and retreave the names of them however it loops through all of the SPs even the system ones. This makes the loop take a while since it has to cycle through all of the system stored procedures. Is there a way to loop through only the dbo sps? I'm using VB.net

For Each sp In theserver.Databases.Item(DBName).StoredProcedures

x = sp.ToString

If sp.IsSystemObject = False Then

'MsgBox(x)

End If

Next

Thanks

Hi,

see this here:

http://www.sqlteam.com/item.asp?ItemID=23185

The following can be used to test your code against your databases:

For one of my databases with 902 Procedures this was a huge difference.

using System;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

EvaluateSMOTime(".", "SQLSErver2005", true);

EvaluateSMOTime(".", "SQLSErver2005", false);

Console.ReadLine();

}

internal static void EvaluateSMOTime(string ServerName, string DatabaseBaseName, bool SetDefaultField)

{

DateTime Before = DateTime.Now;

Server theServer = new Server(ServerName);

if (SetDefaultField)

theServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

Database myDB = theServer.Databases[DatabaseBaseName];

foreach (StoredProcedure sp in myDB.StoredProcedures)

{

if (!sp.IsSystemObject)

{

Console.Write(".");

}

}

DateTime After = DateTime.Now;

TimeSpan Diff = After.Subtract(Before);

Console.WriteLine(string.Format("With{1} tweaking the DefaultInitFields : {2} ms", Diff.Milliseconds, SetDefaultField ? string.Empty : "on"));

}

}

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver20005.de

Looping through each row in an XML object sent to a Stored Procedure

I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure.

Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.

The XML File is layed out as:

<Values>
<value>
<value>1</value>
<key>My_Field</key>
</value>
<value>
<value>3523.2</value>
<key>My_other_Field</key>
</value>
</Values
I basically need to go through it row by row, find out what table I need to insert the value into using thekeyfield.

Any help with this would rock. I'm using SQL 2005.

Tried a bit more but couldn't figure it out.

Basically I need to do something like this (logic):

Foreach row in @.xml

switch(SELECT FieldType FROM fields WHERE Name = @.Xml[key])

case :TextBox:
INSERT INTO TextFields(Value) VALUES (@.Xml[Value])
break;

case: listBox
INSERT INTO ListFields(Values) VALUES)@.Xml[Value])

... etc.

I have to check each row in the XML object sent in to see what table I need to insert the corresponding value to. I know this is possible, i'm just not sure how.

|||

Hi,

Actually you can use XMLDocument to get the data from XML file and store it into your database. See the sample below.

XmlDocument xd =new XmlDocument();xd.Load(inputurl);/// inputurl is the path of the xml file. XmlNodeList xnl = xd.GetElementsByTagName("value");for (int i = 0; i < xnl.Count; i++) { XmlNode xn0 = xnl.Item(i).ChildNodes[1]; XmlNode xn1 = xnl.Item(i).ChildNodes[0];string xn0_str = xn0.InnerText;string xn1_str = xn1.InnerText;// xn0_str stands for the table name // xn1_str stands for the value you want to insert. }

Hope that helps. Thanks.

|||

That would work, however that means I will be hitting the Database with many calls. I'd rather execute 1 call to the DataBase. Sometimes that XML file might have 100 rows in it. I don't want to hit the DB 100 times. I would rather send everything in at once, and then have the Database do all the work. It's less expensive that way.

Thanks :) I found a way to work it though using temp tables :)

|||

Hi,

Well, I know your needs. The main idea is to loop the xml file first, and take down each node's value in an array. And then build your insert statement dynamically by looping the array value.

Thanks.

Looping through databases in stored proc

I am trying to loop through the databases on a server (SQL 2000) and
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.
When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":
use @.dbname
go
exec sp_helpfile
When I use the following code I am told "Incorrect syntax near '.'"
exec @.dbname..sp_helpfile
Any suggestions?
Message posted via http://www.webservertalk.comLook at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com|||You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Just curious... What are you using this info for? Some sort of SQL admin.
application?
Paul
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Try,
use northwind
go
create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)
declare @.sql nvarchar(4000)
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'use [' + @.db + N'] execute sp_helpfile'
insert into #t
execute sp_executesql @.sql
end
close databases_cursor
deallocate databases_cursor
select
*
from
#t
order by
dbn, fileid
drop table #t
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of
> course that means I need to store the name of the database as a
> variable or parameter.
> When I use the following code I am told "a USE database statement is
> not allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
>
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):
declare @.str varchar(255)
set @.str = 'exec ' + @.dbname + '..sp_helpfile'
--optional
select @.str AS TheStringToExecute
exec (@.str)
or you can use the undocumented stored procedure that is shown below:
EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'
Keith
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com

Looping through a recordser in a stored procedure

I have a table [myOrders] with three columns. One of the columns
contains text output data [myText] nvarchar(500), one of them contains
a filename [myFileName] nvarchar(50), one of the columns is a bit to
record if it has been output yet[isOutput] bit default value = 0.

I am creating a SQL Agent job that needs to look at a recordset of
[myOrders] where [isOutput] = 0 and create a seperate text file for
each row using [myFileName] as the filename.

Then I need to mark [isOutput] of each record in [myOrders] as 1.

Ok, so that's the task...

What I'm thinking is I construct a stored procedure that starts with a
select statement:

Create PROCEDURE JustDoIt
AS
set nocount on

SELECT
myText, myFileName
FROM
myOrders
WHERE
(isOutput = 0)

THEN I USE BCP to create the file looping through the recordset above.
THIS IS THE PART I AM CLUELESS ABOUT!

/* NEED TO LOOP HERE */

DECLARE @.ReturnCode int
DECLARE @.ExportCommand varchar(255)
DECLARE @.FileName nvarchar(50)
DECLARE @.FileText nvarchar (500)

SELECT @.FileName = myFileName

/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK:)

SET @.ExportCommand =
'BCP @.FileText queryout "c:\' +
@.FileName +
'" -T -c -S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

/* NEED TO EXIT LOOP HERE */

Then I update all records in [myOrders] to 1

BEGIN TRANSACTION

UPDATE
myOrders
SET isOutput = 1
WHERE
(isOutput = 0)

/* err checking here */

COMMIT TRANSACTION

I'm hoping someone can help me construct this.
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table [myOrders] with three columns. One of the columns
> contains text output data [myText] nvarchar(500), one of them contains
> a filename [myFileName] nvarchar(50), one of the columns is a bit to
> record if it has been output yet[isOutput] bit default value = 0.
> I am creating a SQL Agent job that needs to look at a recordset of
> [myOrders] where [isOutput] = 0 and create a seperate text file for
> each row using [myFileName] as the filename.

I'm glad to see that you are exploring Agent!

Did you ever consider of making it an Active-X job step? You could then
use VBscript for the task, and it may be easier to write files from
VBscript. (Then again, I have never used VB-script myself.) You could
also write a command-line program in whatever language you fancy, and
run the step as as CmdExec.

You could do this in T-SQL, by setting up a cursor, but since you
would have to fork out with xp_cmdshell for BCP for each file, there
may be a performance cost. VBscript (or whatever language) would be
more effective.

The cusror solution is fairly straightforward:

DECLARE your_cur INSENSITIVE CURSOR FOR
> SELECT
> myText, myFileName
, OrderID
> FROM
> myOrders
> WHERE
> (isOutput = 0)

OPEN your_cur

WHILE 1 = 1
BEGIN
FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
IF @.@.fetch_status <> 0
BREAK

> SET @.ExportCommand =
> 'BCP @.FileText queryout "c:\' +
> @.FileName +
> '" -T -c -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

IF @.ReturnCode = 0
UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

END

DEALLOCATE your_cur

Now, as it written above, it assumes that @.FileText is a query, but
from your narrative, I believe it is just a file. You could make it
a query by

SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''

--
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,
Thanks very much for this code. Hopefully I can construct this from
this foundation.
Question though, can I avoid using cursors by taking advantage of BCP
parameters firstrow, lastrow and batchsize parameters so that I output
one row at a time of a variable row recordset? This would be my
first option.
Thanks,
LQ

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||Erland, Thanks again for your time.
I want to do this without using a cursor, instead use select top 1 of
the recordset and loop through BCP until there are no more records.

Looks something like

SELECT top1 OrderID, myText, myFileName from tblOrders where isOutput
= 0

>>WHatI need here is to figure out how to extract the value of
myFileName and myText and pass it to the BCP Utility<<

Do until there's no more records in select statement above:

SET @.ExportCommand = 'BCP myText queryout "c:\' + myFileName+ '" -T -c
-S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

Loop

Sorry for being so dumb about this. I have never used this sort of
construction before.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||I realize there is a cost to using cursors, but since you're going
to launch the command shell for BCP on every record, the cost of the
cursor is probably insignificant. But if you insist...

> Erland, Thanks again for your time.
> I want to do this without using a cursor, instead use select top 1 of
> the recordset and loop through BCP until there are no more records.
> Looks something like

WHILE 1=1 BEGIN
SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
from tblOrders where isOutput = 0

IF @.@.ROWCOUNT = 0 BREAK
> SET @.ExportCommand = 'BCP '+@.myText+' queryout "c:\' + @.myFileName+ '" -T -c
> -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
END -- end of loop|||[I'm answering to Jim's post, since Lauren's has not made it here yet. My
ISP reconfigured the news server and it took them two days to realize
that it was no longer working.]

Jim Geissman (jim_geissman@.countrywide.com) writes:
> I realize there is a cost to using cursors, but since you're going
> to launch the command shell for BCP on every record, the cost of the
> cursor is probably insignificant. But if you insist...

Why Laruen does not want to use a cursor I don't know, but since he
has to iterate anyway, cursor is the best solution for iteration anyway.

Say that you instead do:

> SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> from tblOrders where isOutput = 0

If there is no index on isOutput (and one would not expect that),
and the table is huge, this can be very expensive. I have no benchmarks,
but I would suggest that for an iteration a cursor is the best way to
go, although it depends on the cursor type. FAST_FORWARD may be the
fastest, but I always use INSENSITIVE.

Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
or similar as I suggested in my previous post. It will be easier to
program and execute faster.

Also, it occurred to me that if tblOrders.myText is the text that is
to be written to the file, the QueryOut thing will not work, since the
newlines in myText causes problem. Then again if the query for queryout is

'SELECT myText FROM tblOrders = ' + str(@.orderid)

that will work.

--
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 (and Jim)
You guys have gone above and beyond the call of duty in your response
to my problem and because of your repsonses I have been able to roll
this out. Thanks a million, and I have only one correction for
Erland's post - substitute "she" for "he" and it's 100% correct!
Thanks,
lq

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9534EF744A28AYazorman@.127.0.0.1>...
> [I'm answering to Jim's post, since Lauren's has not made it here yet. My
> ISP reconfigured the news server and it took them two days to realize
> that it was no longer working.]
> Jim Geissman (jim_geissman@.countrywide.com) writes:
> > I realize there is a cost to using cursors, but since you're going
> > to launch the command shell for BCP on every record, the cost of the
> > cursor is probably insignificant. But if you insist...
> Why Laruen does not want to use a cursor I don't know, but since he
> has to iterate anyway, cursor is the best solution for iteration anyway.
> Say that you instead do:
> > SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> > from tblOrders where isOutput = 0
> If there is no index on isOutput (and one would not expect that),
> and the table is huge, this can be very expensive. I have no benchmarks,
> but I would suggest that for an iteration a cursor is the best way to
> go, although it depends on the cursor type. FAST_FORWARD may be the
> fastest, but I always use INSENSITIVE.
> Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
> or similar as I suggested in my previous post. It will be easier to
> program and execute faster.
> Also, it occurred to me that if tblOrders.myText is the text that is
> to be written to the file, the QueryOut thing will not work, since the
> newlines in myText causes problem. Then again if the query for queryout is
> 'SELECT myText FROM tblOrders = ' + str(@.orderid)
> that will work.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> You guys have gone above and beyond the call of duty in your response
> to my problem and because of your repsonses I have been able to roll
> this out. Thanks a million, and I have only one correction for
> Erland's post - substitute "she" for "he" and it's 100% correct!

Glad to hear that you got it working! And my cheeks blossom in embarrassment
for calling you a man. I remember it to next time.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql