Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?
Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

ASPNETDB.MDF?

Using VS2005, VB backend and javascript,

I have developed a relatively simple site - its got a few (12) simple aspx pages but its mostly client side javascript. Keeping disk storage costs down is a big concern with this my site. The disk usage for the site is ~24M. Since this was larger than I expected I started inspecting the files comprising my site and found that the "ASPNETDB.MDF" in my "App_data" folder is consuming 10.2M by itself. The thing is that site only has a few pages with calls to SQL Server - but I never did anything (that I know of) with ASPNETDB.MDF. Through VS2005, I opened up the MDF file and poked around, everything that I looked at was empty (NULL).

So my questions are:

What is causing the ASPNETDB.MDF to consume 10.2M even thought I can't see any data stored in it?Is there anyway for my to reduce the size of this file? If so, how?

Your input appreciated.

Should I be posting this in a different forum?|||

Hi,

The ASPNETDB contains a lot of information about table structure and membership information about your site.

To check what is occupying the storage, you can run DBCC CHECKDB on your local machine to see the allocation, structural, and logical integrity of all the objects in the specified database.

For more information, please check the following link.

http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

aspnetdb.mdf on Hosting Server ?

I have established roles to restrict access to the dataentry pages. The database is SQL Server which I've established on the hosting server. Security was previously maintained through~/app_data/aspnetdb.mdb (MS Access). It works, but isn't too stable, so I have modified the security to useSQL Server with an aspnetdb.mdf file, also located in the app_datafolder. Is this appropriate? Since it doesn't work, I assume not.

It's now my assumption that I need to setup a SQL Serverdatabase to support the security/roles. I have seen some reference tousing a script to build the aspnet.mdf on the hosting server. IfI do create such a database, is there anything special I need to do tohave my application read the aspnetdb? Do I need a connection stringin the membership and/or roleManager sections of my web.config.

A little guidance would be appreciated.

Thanks

http://www.eggheadcafe.com/articles/20060529.asp

|||

Thank you Dr. Bromberg for the extensive material you've pointed me to. I have downloaded the code and created a web site on my local Visual Studio server. I was able to run the SQL to create the two databases, Articles and aspnetdb (I think that's what I was suppose to do?). I have modifed the web.config to reflect the localhost information. When I run the Default.aspx I receive the following error:

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

OK, something's not setup properly. I attempted to run the SetupASPNetDatabase.aspx page and get absolutely nowhere, most likely because I have no idea what values to submit in the query boxes.

Bottom line is, I am not clear on what I need to do to get started. I am sure there is considerable information to glean from your article and sample web site. I would genuinely like to learn from it. The problem I seem to be having through is getting the security configured, which takes me back to square one with my initial question. I feel as if I am in the proverbial Catch 22. I need to get my own hosted security working and/or get your sample working before I can get either of them working.

|||

To try and answer your question about setting up security with SQL Server...

1. Install the security schema (tables, procedures etc) on the SQL Server database. You can do this on your local machine by running aspnetregsql.exe (it's an installation wizard which comes with the ASP.Net framwork). If you have full control on your hosted server, then install the same way, otherwise your hosting provider will have to assist (some offer the installation as an option via their Control Panel). Note that the database doesn't have to be called ASPNETDB, you can install the security schema into an existing database of a different name.

2. Set up a connection string that points explicitly at the SQL Server database i.e. server name, database name, user id and password.

3. Point the Membership provider at this connection string.

I've put a specific example from one of my own web.config files using SQL Server - this is the connection string from mydevelopment environment (so uses localhost for the server name). Can you see that the Membership provider points at the "MainDB" connection string - which is pointing at the SQL Server?

The live web.config settings are very similar, except that instead of localhost I have the name of the server. Hope this helps!

<connectionStrings>

<addname="MainDB"connectionString="Server=localhost;Database=DB_138621;User ID=*****;Password=*****"providerName="System.Data.SqlClient" />

</connectionStrings>

--- snip -----

<membershipdefaultProvider="AspNetSqlMembershipProvider">

<providers>

<clear/>

<addname="AspNetSqlMembershipProvider"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MainDB"minRequiredPasswordLength="5"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"enablePasswordRetrieval="true"enablePasswordReset="false" applicationName="/"/>

</providers>

|||

Thank you Salmon. Using your example and wading through my web.config with fat fingers, I was finally able to make this work.

ASPNET user Where are others

Hello.

When pages on domain controler I faced problem: ASPNET user does not have access to MS SQL server? WHY does it has to have? My site uses integrated windows authentication.

Thanks in advanceAll you have to do is to to add the ASPNET user (windows user account) to the Users list of your database. To do that in Enterprise Manager, expand your database's tree, click on Users and select New from the toolbar. In the window that will come up select the ASPNET user from the "Login name" dropdownlist.|||I ran into same problem, only that I'm using MSDE and don't know where to get Enterprise Manager. Could you point out which MSDN package (or CD) contains that application? Or is there a way to do it without EM? Thanks a lot!|||Enterprise manager comes with SQL Server. If you don't have SQL Server try to find a replacement of Enterpise Manager. I remember seeing one in www.sourceforge.net. It is web based, built with ASP.NET. I am not sure if it will let you assogn a new user to your DB. But, you can give it a try|||Thanks!|||Well, bun in ASP 3 all I had to do is to grant access to a database for a user who is ACTUALLY browsing my page. Now, in ASP.NET, all the users will have access to a database with the same priveledges, same as ASPNET user. Is there any workaroud?

Thanks

ASP.Net, Microsoft SQL Server 2005 and XML

Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
oAs the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
oBut After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
oThe problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.
Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.

> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> oAs the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> oBut After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> oThe problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Also instead of trying to construct your XML using string concatenations,
why don't you use FOR XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad747edd8c8b87741135600@.news.microsoft.com ...
> Hello djamilabouzid@.gmail.com,
> Its not all that clear as what is cause a problem here because we don't
> know what reade1.GetString(47) is actually returning.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

ASP.Net, Microsoft SQL Server 2005 and XML

Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
o As the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
o But After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
o The problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.

> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> o As the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> o But After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> o The problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

ASP.NET 2.0 and SQL2000

Hi,

I have made a web application using SQL Server 2005Express with a few admin pages that require login. I used the SQLServer Managment Studio Express to setup users and groups and it worksfine.

But the web server has SQL Server 2000 and all I get is auser id and password to access the database. It seems I cannot use theLogin control I put on my "login.aspx" page becuase it uses theintegrated authentication which chokes on 2000. I am afraid I wonlt beable to use some of the 2.0 features such as profiles, ...

Isthis the way 2.0 works with SQL Server 2000? Should I ditch the ISP ifthey only give me a single access to database with no provisions tocreate users. (I was told I had to create a "Users" table and manuallyassign ids and passwords and that I can only use the user id andpassword they supplied to setup the connection string in the script.)

I appreciate your help.

Single login connectionstrings are the more natural way to do something like this - the 'users' you are referring to, are users of your application, and don't need to be users in the database - you wouldn't want them to have access to your database, in fact.

And you can easily setup a membership/roles scenario, in the database (2000 or 2005) with the connectionstring they give you.

|||

Thanks for the info.

When is it appropriate to add users and groups using the SQL Server Management Studio?

Canyou please point me to a document or article that describes how tosetup and use membership/roles using only the connection string?

Thank you.