izzit got for loop in transact-sql stored procedure

Dear all,
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>

I've installed SQL express, now what?

This may be a stupid question, but I've installed SQL Express and I don't know what to do next. I went to my program files/Microsft SQL... and the only folder present is a configuration folder.

Next I went to my control panel/admin tools/ODBC and succussfully created a user data source. (With the help of the forum!)

Obviously, I've never used SQL before, but I've read a couple of books and other stuff online, and I want to teach myself. So I decided to install this. Basically, I'm looking for answers on how to access the database. I know it's installed, it's in the Add/Remove programs list. I just don't know how to connect/access it.

Sorry for the dumb question.

Stacey

I think I figured it out. You have to access the DB using another program, and connect using the ODBC that I previously created. Am I correct?

I thought it was an independent DB, meaning I could directly access it. Thanks!

|||

Hi

You'll need to download SQL Management Studio Express (here). It will allow you to create databases, run queries etc. That way you won't have to work "in the dark".

HTH

|||

Thank you so much for helping me. Now I just have to figure out how to use it, and what to do!!!

Stacey

|||Update - Thank you so much Andre! I am off and running thanks to you. I figured out how to use it, and now I'm learning SQL thanks to you!!

I've got a SQL database and I need to see what the data it looks like

Is there a program like Access that can open a SQL database and display the information?

You can link the tables and view data using Access.

You can also install the SQL Client tools and use Enterprise Manager or Management Studio (depending on version).

|||Hi,

If you have Access in place which supports ADP projects you can establish a connection to the database and will be able to view almost all user defined objects in the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

It takes a long time to insert the first record each time when the program start

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

However, when I give up the dataset and using the following code:

cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount

I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

Last example does not include looking for updated rows in the DataSet and generating appropriate commands. Since it does less, it's faster.

sql

Issues/Problems with SQL MSDE running on Windows XP

Can anyone tell me if there are known problems/issues with running SQL MSDE
on Windows XP? We have a wise install program that installs an Excel
application with msde and it runs fine on Windows 2000 but not on XP. The
install program completes fine but when we go to view the database we find
that the SQL Server instance is there with the msdb, model, and other default
system databases but none of the databases that the install script creates.
When the same installation is ran on 2000 everything works beautifully.
Any help greatly appreciated!
Thanks,
Ang
This is likely an issue with your installation program and not with MSDE.
MSDE works just fine with XP. Contact your application vendor.
-Andrew
"Angela" <Angela@.discussions.microsoft.com> wrote in message
news:E0E98FDB-4923-4CE0-9E1F-D2F8733B4B79@.microsoft.com...
> Can anyone tell me if there are known problems/issues with running SQL
> MSDE
> on Windows XP? We have a wise install program that installs an Excel
> application with msde and it runs fine on Windows 2000 but not on XP. The
> install program completes fine but when we go to view the database we find
> that the SQL Server instance is there with the msdb, model, and other
> default
> system databases but none of the databases that the install script
> creates.
> When the same installation is ran on 2000 everything works beautifully.
> Any help greatly appreciated!
> Thanks,
> Ang

Issues with too many audittrace .trc files

Hi
I am running MSD 2005 Express Sequel Server and am noticing that in
the data folder located under C:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
assuming it is some sort of log file) is being created. Here is a
sample title of what the file is labled:
audittrace20070618090810_328.trc. These files are big, at least
200,000KB, and are taking up way too much space in my hard drive.
Does any body here know the source for these files? What type of file
is it or neccessary for? I am assuming there is some stored process in
the sequel server that is creating these big files. I am just not sure
where to find that process or where the command is coming from.
Any help is much appreciated. Thanks in advance!
JonathanIt is the "default trace", a Profile trace. You can turn it off using sp_con
figure. If you want to
check what events and columns it traces, see:
http://sqlblog.com/blogs/tibor_kara...
ce.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ridingtime516@.gmail.com> wrote in message
news:1184615740.955056.40490@.o61g2000hsh.googlegroups.com...
> Hi
> I am running MSD 2005 Express Sequel Server and am noticing that in
> the data folder located under C:\Program Files\Microsoft SQL Server
> \MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
> assuming it is some sort of log file) is being created. Here is a
> sample title of what the file is labled:
> audittrace20070618090810_328.trc. These files are big, at least
> 200,000KB, and are taking up way too much space in my hard drive.
> Does any body here know the source for these files? What type of file
> is it or neccessary for? I am assuming there is some stored process in
> the sequel server that is creating these big files. I am just not sure
> where to find that process or where the command is coming from.
> Any help is much appreciated. Thanks in advance!
> Jonathan
>sql

Issues with too many audittrace .trc files

Hi
I am running MSD 2005 Express Sequel Server and am noticing that in
the data folder located under C:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
assuming it is some sort of log file) is being created. Here is a
sample title of what the file is labled:
audittrace20070618090810_328.trc. These files are big, at least
200,000KB, and are taking up way too much space in my hard drive.
Does any body here know the source for these files? What type of file
is it or neccessary for? I am assuming there is some stored process in
the sequel server that is creating these big files. I am just not sure
where to find that process or where the command is coming from.
Any help is much appreciated. Thanks in advance!
Jonathan
It is the "default trace", a Profile trace. You can turn it off using sp_configure. If you want to
check what events and columns it traces, see:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/12/whats-in-the-default-trace.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ridingtime516@.gmail.com> wrote in message
news:1184615740.955056.40490@.o61g2000hsh.googlegro ups.com...
> Hi
> I am running MSD 2005 Express Sequel Server and am noticing that in
> the data folder located under C:\Program Files\Microsoft SQL Server
> \MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
> assuming it is some sort of log file) is being created. Here is a
> sample title of what the file is labled:
> audittrace20070618090810_328.trc. These files are big, at least
> 200,000KB, and are taking up way too much space in my hard drive.
> Does any body here know the source for these files? What type of file
> is it or neccessary for? I am assuming there is some stored process in
> the sequel server that is creating these big files. I am just not sure
> where to find that process or where the command is coming from.
> Any help is much appreciated. Thanks in advance!
> Jonathan
>

Issues with too many audittrace .trc files

Hi
I am running MSD 2005 Express Sequel Server and am noticing that in
the data folder located under C:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
assuming it is some sort of log file) is being created. Here is a
sample title of what the file is labled:
audittrace20070618090810_328.trc. These files are big, at least
200,000KB, and are taking up way too much space in my hard drive.
Does any body here know the source for these files? What type of file
is it or neccessary for? I am assuming there is some stored process in
the sequel server that is creating these big files. I am just not sure
where to find that process or where the command is coming from.
Any help is much appreciated. Thanks in advance!
JonathanIt is the "default trace", a Profile trace. You can turn it off using sp_configure. If you want to
check what events and columns it traces, see:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/12/whats-in-the-default-trace.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ridingtime516@.gmail.com> wrote in message
news:1184615740.955056.40490@.o61g2000hsh.googlegroups.com...
> Hi
> I am running MSD 2005 Express Sequel Server and am noticing that in
> the data folder located under C:\Program Files\Microsoft SQL Server
> \MSSQL.1\MSSQL\Data, that every 3 hours or so a .trc file (I am
> assuming it is some sort of log file) is being created. Here is a
> sample title of what the file is labled:
> audittrace20070618090810_328.trc. These files are big, at least
> 200,000KB, and are taking up way too much space in my hard drive.
> Does any body here know the source for these files? What type of file
> is it or neccessary for? I am assuming there is some stored process in
> the sequel server that is creating these big files. I am just not sure
> where to find that process or where the command is coming from.
> Any help is much appreciated. Thanks in advance!
> Jonathan
>

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>

isqlw.exe

Hello,

I notice that the name of the Query Analyzer program (formerly
known as ISQL/W) is still isqlw.exe in SQL Server 2000. I am
curious as to where the name ISQL/W came from.

Feedback welcome,

Best Regards,

Neil<nzanella@.cs.mun.ca> wrote:

> Hello,
> I notice that the name of the Query Analyzer program (formerly
> known as ISQL/W) is still isqlw.exe in SQL Server 2000. I am
> curious as to where the name ISQL/W came from.
> Feedback welcome,
> Best Regards,
> Neil

Neil,

It's always been my understanding that the "w" in isqlw stood for Windows:
isql was the original command line interface to SQL Server and isqlw was the
GUI "version" of the same concept.

Craig|||(nzanella@.cs.mun.ca) writes:
> I notice that the name of the Query Analyzer program (formerly
> known as ISQL/W) is still isqlw.exe in SQL Server 2000. I am
> curious as to where the name ISQL/W came from.

In the begiinning there was Sybase, and their interactive interface to
SQL Server was ISQL - Interacticve SQL.

Microsoft teamed up with Sybase and supplied the Windows version of
SQL Server.

Later Microsoft cancelled the partnership with Sybase and the two
went on to develop SQL Server each on their own. Microsoft augmented
the toolset with a graphical interface and this was named ISQL/W,
where W certainly was for Windows. ISQL/W hung around until 6.5.

For SQL 7, Microsoft developed a new tool, the Query Analyzer with
richer set of functionality and which used ODBC to connect and not
DB-Library. For reasons unknown to me, the executable retained the
name isqlw.exe.

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

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