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)?
>

IUSR_Machine account permissions in Report Builder

We have an ASP.NET application using Forms authentication which
launches the Report Builder via an HTML link with the href set to:
ReportServer/ReportBuilder/ReportBuilder.application
This will launch Report Builder and allow users to create, modify, and
save reports. But when the user tries to run the report, the above
error message comes up. Just for testing purposes, I tried adding the
IUSR account to the Administrators group but still with the same
result. It seems that everything we try still leads us to the
following error message:
The permissions granted to user '<domain>\IUSR_<machine>' are
insufficient for performing this operation. (rsAccessDenied)
After days of searching for a resolution to this I'm still unsure
about how to fix it. If anyone could give some advice or maybe provide
a link that explains how to resolve the situation we would be sincerely
grateful.
Thanks in advance!I'm encountering the same problem. Launching Report Builder from the
URL and authenticating the forms user, I can create a report and
save/deploy to the server but can't view it through Report Builder.
Also, Report Builder icon doesn't display in Report Manager when using
forms authentication for any user other than the default admin user.
I'll be writing an ASP.NET app to launch and deploy Report Builder but
it would be nice to have the icon in Report Manager
Chris wrote:
> We have an ASP.NET application using Forms authentication which
> launches the Report Builder via an HTML link with the href set to:
> ReportServer/ReportBuilder/ReportBuilder.application
> This will launch Report Builder and allow users to create, modify, and
> save reports. But when the user tries to run the report, the above
> error message comes up. Just for testing purposes, I tried adding the
> IUSR account to the Administrators group but still with the same
> result. It seems that everything we try still leads us to the
> following error message:
> The permissions granted to user '<domain>\IUSR_<machine>' are
> insufficient for performing this operation. (rsAccessDenied)
> After days of searching for a resolution to this I'm still unsure
> about how to fix it. If anyone could give some advice or maybe provide
> a link that explains how to resolve the situation we would be sincerely
> grateful.
> Thanks in advance!|||So have you had any luck with any kind of work-around? It's driving me
crazy and I have a client very anxious to start using it.
Chris
si.downes@.gmail.com wrote:
> I'm encountering the same problem. Launching Report Builder from the
> URL and authenticating the forms user, I can create a report and
> save/deploy to the server but can't view it through Report Builder.
> Also, Report Builder icon doesn't display in Report Manager when using
> forms authentication for any user other than the default admin user.
> I'll be writing an ASP.NET app to launch and deploy Report Builder but
> it would be nice to have the icon in Report Manager
> Chris wrote:
> > We have an ASP.NET application using Forms authentication which
> > launches the Report Builder via an HTML link with the href set to:
> >
> > ReportServer/ReportBuilder/ReportBuilder.application
> >
> > This will launch Report Builder and allow users to create, modify, and
> > save reports. But when the user tries to run the report, the above
> > error message comes up. Just for testing purposes, I tried adding the
> > IUSR account to the Administrators group but still with the same
> > result. It seems that everything we try still leads us to the
> > following error message:
> >
> > The permissions granted to user '<domain>\IUSR_<machine>' are
> > insufficient for performing this operation. (rsAccessDenied)
> >
> > After days of searching for a resolution to this I'm still unsure
> > about how to fix it. If anyone could give some advice or maybe provide
> > a link that explains how to resolve the situation we would be sincerely
> > grateful.
> >
> > Thanks in advance!|||Still no solution!!
Noticed in your error message the failure is for a domain user
<domain>\IUSR_.. mine is for the name of the forms user I have
created in the UserAccounts database. Under IIS I have reportBuilder
running with Anonymous Authentication as user IUSR_
It's starting to drive me mad too, this needs to be integrated into a
product for the end of the month.
Si
Chris wrote:
> So have you had any luck with any kind of work-around? It's driving me
> crazy and I have a client very anxious to start using it.
> Chris
>
> si.downes@.gmail.com wrote:
> > I'm encountering the same problem. Launching Report Builder from the
> > URL and authenticating the forms user, I can create a report and
> > save/deploy to the server but can't view it through Report Builder.
> >
> > Also, Report Builder icon doesn't display in Report Manager when using
> > forms authentication for any user other than the default admin user.
> > I'll be writing an ASP.NET app to launch and deploy Report Builder but
> > it would be nice to have the icon in Report Manager
> >
> > Chris wrote:
> > > We have an ASP.NET application using Forms authentication which
> > > launches the Report Builder via an HTML link with the href set to:
> > >
> > > ReportServer/ReportBuilder/ReportBuilder.application
> > >
> > > This will launch Report Builder and allow users to create, modify, and
> > > save reports. But when the user tries to run the report, the above
> > > error message comes up. Just for testing purposes, I tried adding the
> > > IUSR account to the Administrators group but still with the same
> > > result. It seems that everything we try still leads us to the
> > > following error message:
> > >
> > > The permissions granted to user '<domain>\IUSR_<machine>' are
> > > insufficient for performing this operation. (rsAccessDenied)
> > >
> > > After days of searching for a resolution to this I'm still unsure
> > > about how to fix it. If anyone could give some advice or maybe provide
> > > a link that explains how to resolve the situation we would be sincerely
> > > grateful.
> > >
> > > Thanks in advance!|||To create a user/group that can log into Report Manager and do
everything, create the user/group, then do the following:
1. Log into Report Manager as the admin user (or whatever name you gave
to the admin user).
2. Click "Site Settings" in the top-right corner
3. Under Security, click "Configure Item-level role definitions"
4. Name the Role and assign it to all of the tasks (click OK when done)
5. Click on "Site Settings" again
6. Under Security, click "Configure system-level role definitions"
7. Click "New Role"
8. Name the Role and assign it to all of the tasks (click OK when done)
9. Click "Site Settings"
10. Click "Configure Site-wide security"
11. If your user/group is already listed, click "Edit" next to it,
otherwise click "New Role Assignment" then type in your user/group name
on the next screen
12. Select the checkbox next to the system role you created in steps
6-8 (click Apply when done)
13. Click "Home" in the top-right corner
14. Click on the blue "Properties" tab
15. If your user/group is listed, click "Edit" next to it, otherwise
click "New Role Assignment" and type in your user/group name on the
next screen
16. Select the checkbox next to the role you created in steps 3-4
(click Apply when done).
17. Click Home. The end.
There are two different types of roles: item-level roles and
system-level roles. My guess would be that something was missing from
your list of 'tasks' in the item-level roles assigned to the
group/user. If you follow these instructions, you should end up with a
user/group that can do everything. This should help you determine if
it's a matter of permissions settings or something wonky with your
server setup. FYI, I would recommend against giving your users full
control over everything as described here.
I hope this helps.
Tim Larson

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

sql

It's slow to use ServerXMLHTTP to submit a query to SQLXML virtual directory

I'm using ServerXMLHTTP object in an ASP page (web server) to submit a query to our database server (SQL Server 2000) via SQLXML virtual directory. The ServerXMLHTTP object will return me more than 5000 rows in XML format which is about 15M in size.

The problem is, it takes 1 minute for the ServerXMLHTTP object to get the response from SQLXML web service. That makes our web application not workable because it's really slow.

However, if I use XMLHTTP object instead of ServerXMLHTTP object, it only takes seconds to finish the same query. I know these 2 objects are implemented in different ways. XMLHTTP is designed for client applications and relies on URLMon, which is built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications and relies on a new HTTP client stack, WinHTTP. ServerXMLHTTP offers reliability and security and is server-safe. So I'd better use ServerXMLHTTP in my web application if I know how to solve the speed issue.

Can somebody help me out? Thank you very much in advance. This problem happens recently. The program had been working for 3 years.

The url opened by ServerXMLHTTP object is like http://myserver/myvd?sql=select * from staff where gender='M' and staff_id<5000 for xml auto&root=Root

Here are some things to look at.

ServerXMLHTTP can have proxy issues. Check if the time is spent making the connection. [more]

It can take 15+ seconds to negotiate certificates. Are certificates involved? If so, are you reusing the ServerXMLHTTP object? If so does the delay occurs only on the first use of the ServerXMLHTTP object?

If it's possible in your scenario (not enough info for me to tell), you can use ServerXMLHTTP in asynchronous mode to increase concurrency in your app (work while you wait).

HTH!

Tim

sql

IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...

http://www.testyourabilities.com/it/
IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...
Nice Link.................

IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...

http://www.testyourabilities.com/it/
IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...I started the SQL test. My question #9 had department mispelled several
times. I quit. If you know the authors, please let them know.

<marboti@.gmail.com> wrote in message
news:1140112993.857039.254320@.g47g2000cwa.googlegr oups.com...
> http://www.testyourabilities.com/it/
> IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...|||I think it's a horrible test:
questions from very different flavors of SQL were mixed
#11 - serial datatype is not mainstream, is it?
#12 - apparently ambiguos, 2 correct answers
#14 - on which platform will INSERT ... SET work?
#15, #19 - apparently all possible answers are wrong

IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...

http://www.testyourabilities.com/it/
IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...Nice Link.................

IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...

http://www.testyourabilities.com/it/
IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...Nice Link.................

Issues getting ASP.NET and SQL to talk

Hi! This is my first time messing with SQL and ASP.NET. I'm having issues getting the SQL to work with ASP.NET. I am using win XP Pro running IIS. I have all patches and upgrades installed on the computer. All files are served off localhost.
When I try to visit the ASP.NET page I get the following error message.

Server Error in '/PortalVBSDK' Application.

SQL Server does not exist or access denied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:

Line 28: Dim links As New ASPNET.StarterKit.Portal.LinkDB()Line 29:Line 30: myDataList.DataSource = links.GetLinks(ModuleId)Line 31: myDataList.DataBind()Line 32:

Source File:C:\Program Files\ASP.NET Starter Kits\ASP.NET Portal (VBSDK)\PortalVBSDK\DesktopModules\QuickLinks.ascx Line:30
with the stack trace of

[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 ASPNET.StarterKit.Portal.LinkDB.GetLinks(Int32 moduleId) ASP.QuickLinks_ascx.Page_Load(Object sender, EventArgs e) in C:\Program Files\ASP.NET Starter Kits\ASP.NET Portal (VBSDK)\PortalVBSDK\DesktopModules\QuickLinks.ascx:30 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Control.LoadRecursive() +98 System.Web.UI.Control.LoadRecursive() +98 System.Web.UI.Control.LoadRecursive() +98 System.Web.UI.Page.ProcessRequestMain() +750

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
Any help would be greatly appriciated!
Joshua
seems like your connectionstring is not correct|||Are you sure you have the right connection string in web.config?

Issue on .asp pages when using DSN for connection

Hello,
We have an asp site with content mamagement that retrieves HTML stored in
SQL Server 2000. The site works fine when a "DSN-less" connection string is
used. Due to MIS policy we need to use a system DSN, but when we connect
that way some of the pages display HTML code instead of rendering the HTML
properly.
Our DSN-less conn string which works fine:
connection="PROVIDER=SQLOLEDB;DATA
SOURCE=SQLSERVERBOX,1433;DATABASE=SQLDB123;USER
ID=sqluser123;PASSWORD=password123;"
Our DSN conn string which does not work properly:
connection="DSN=OUR_ASP_SITE_DSN; UID=sqluser123; PWD=password123;"
I have a feeling that the answer may be that we need to use DSN-less since
that uses the SQLOLEDB driver, instead of a DSN which uses SQL Server ODBC
(version 2000.86.1830.00). Maybe there is a way to use a DSN and the
SQLOLEDB driver?
No more hair left to pull out, any ideas out there? TIA,
~Rich
The only thing I can think of is that the DSN is a user dsn not a system dsn.
Russel Loski, MCSD.Net
"Richnet" wrote:

> Hello,
> We have an asp site with content mamagement that retrieves HTML stored in
> SQL Server 2000. The site works fine when a "DSN-less" connection string is
> used. Due to MIS policy we need to use a system DSN, but when we connect
> that way some of the pages display HTML code instead of rendering the HTML
> properly.
> Our DSN-less conn string which works fine:
> connection="PROVIDER=SQLOLEDB;DATA
> SOURCE=SQLSERVERBOX,1433;DATABASE=SQLDB123;USER
> ID=sqluser123;PASSWORD=password123;"
> Our DSN conn string which does not work properly:
> connection="DSN=OUR_ASP_SITE_DSN; UID=sqluser123; PWD=password123;"
> I have a feeling that the answer may be that we need to use DSN-less since
> that uses the SQLOLEDB driver, instead of a DSN which uses SQL Server ODBC
> (version 2000.86.1830.00). Maybe there is a way to use a DSN and the
> SQLOLEDB driver?
> No more hair left to pull out, any ideas out there? TIA,
> ~Rich

Issue on .asp pages when using DSN for connection

Hello,
We have an asp site with content mamagement that retrieves HTML stored in
SQL Server 2000. The site works fine when a "DSN-less" connection string is
used. Due to MIS policy we need to use a system DSN, but when we connect
that way some of the pages display HTML code instead of rendering the HTML
properly.
Our DSN-less conn string which works fine:
connection="PROVIDER=SQLOLEDB;DATA
SOURCE=SQLSERVERBOX,1433;DATABASE=SQLDB1
23;USER
ID=sqluser123;PASSWORD=password123;"
Our DSN conn string which does not work properly:
connection="DSN=OUR_ASP_SITE_DSN; UID=sqluser123; PWD=password123;"
I have a feeling that the answer may be that we need to use DSN-less since
that uses the SQLOLEDB driver, instead of a DSN which uses SQL Server ODBC
(version 2000.86.1830.00). Maybe there is a way to use a DSN and the
SQLOLEDB driver?
No more hair left to pull out, any ideas out there? TIA,
~RichThe only thing I can think of is that the DSN is a user dsn not a system dsn
.
--
Russel Loski, MCSD.Net
"Richnet" wrote:

> Hello,
> We have an asp site with content mamagement that retrieves HTML stored in
> SQL Server 2000. The site works fine when a "DSN-less" connection string
is
> used. Due to MIS policy we need to use a system DSN, but when we connect
> that way some of the pages display HTML code instead of rendering the HTML
> properly.
> Our DSN-less conn string which works fine:
> connection="PROVIDER=SQLOLEDB;DATA
> SOURCE=SQLSERVERBOX,1433;DATABASE=SQLDB1
23;USER
> ID=sqluser123;PASSWORD=password123;"
> Our DSN conn string which does not work properly:
> connection="DSN=OUR_ASP_SITE_DSN; UID=sqluser123; PWD=password123;"
> I have a feeling that the answer may be that we need to use DSN-less since
> that uses the SQLOLEDB driver, instead of a DSN which uses SQL Server ODBC
> (version 2000.86.1830.00). Maybe there is a way to use a DSN and the
> SQLOLEDB driver?
> No more hair left to pull out, any ideas out there? TIA,
> ~Rich

Issue connecting to a remote SQL Server 2000

I get the following error message in quotes. I have a web application written in ASP.NET 2.0 through which I am trying to connect to a remote SQL Server 2000. My operating system is Windows Vista .

I looked at many different sites to find a solution for this but without any luck. I am sure someone would have seen this issue here and I expect them to shed some light.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

The connection string is located in my web.config file which looks like

<add name="AMSConn" connectionString="Data Source=xxxx;
Initial Catalog=testdb;User ID=sa;Password=xxxx" providerName="System.Data.SqlClient" />

I want to get this issue resolved and I need you guys help.

Please let me know if you need any more information.

Thanks

Bharat

Hello my friend,

First try to access the database from the actual database server using the username and password within your connection string. If this fails, you are using the wrong username and password.

If this works, then it is a remote issue. Try and ping the database server from the computer running your application and see if you get a response or request timed out.

Kind regards

Scotty

|||

Hi Scotty,

First, thanks for the reply. I already tried your first suggestion. It's working fine in the database server with the same credentials. So, not a problem with username and password.

But, when I tried Pinging the server as you indicated I am getting the request timed out error. I don't know what is the reason. I don't have any problems logging into the machine using remote desktop.

Any more suggestions.

Thanks

Bharat

|||

hi Scotty,I have also same problem.

I can ping to remote sql server and can connect with sql query analyser with same credentials.

But can't connect from my aspx application.