Creating a VIEW with multiple DATATABLES

I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.

Hi johncosmas

check this link you will get what you want, with code

http://www.java2s.com/Code/CSharp/Database-ADO.net/PopulateaDataSetobjectwithmultipleDataTableobjects.htm

hope it helps

Creating a View from ASP.Net page (Problems)

Hello,

I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work.

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

Here is an example (or as close as I can remember at this point) of some code I tried:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1, 2"
myCommand.ExecuteNonQuery()
myCommand.Close()

Any help would be appreciated.

Thanks,
StuporMan

I dont think that is a valid SQL statement.|||

You are correct, but it can be made correct by changing the statement to:

"Create View TestView as Select 1 as test1, 2 as test2"

(you can verify this by running in SQL Server management studio).

Andthis does not change the ASP.net error message.

You can do thefollowing test to show what I mean.

Create an ASP.net page with 2buttons.

One button click runs this code:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

(I have left the definitions and SQL connection string up to you)

On the other button click run this:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

And you should produce the same error message as I am getting.

Thanks,

StuporMan

|||

StuporMan:

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

I've tried to run your command in SQL Server Management Studio:

Create View TestView as Select 1, 2

and it generates this error message:

Msg 4511, Level 16, State 1, Procedure TestView, Line 1
Create View or Function failed because no column name was specified for column 1.

|||

Ok, I had mistyped the SQL I was using as I had to do it from memoryat the time. Here is a valid Create View statement that produces thesame error in ASP.net:

"Create View TestView as Select 1 as test1, 2 as test2"

This should run in SQL Management studio. If it does not try"Create View TestView as Select * from <insert table name here>",which should also be valid. In either case, ASP.net should produce thesame error message.

Thanks,

StuporMan

|||

I am still having problems even after correcting the SQL string.Tryany valid CREATE VIEW statement from ASP.net and see if it will work. Ihave not been able to get any to work to date.

|||Presumably whatever context your web application is under doesn't have the needed security. Are you sure you elevated the permissions of the correct account? (Just guessing here, as I've never tried creating views from a web application myself.)|||

Thanks for the response, you prompted me to again check mypermissions. The user I was logging into the SQL server with DIDhave the correct permission set. The ASPNET user did not. Giving ASPNETthe permission did the trick.

Thanks,

StuporMan

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

sql

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a SQL statement

Hello all - newbie post forthcoming...

I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:

sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"

The response I get is... "Data Type Mismatch in Criteria Expression"

The post data does not have quotes around the data - I am thinking this is the problem.

Any help would be appreciated!Welcome to the ASP.NET Forums, mr_atlanta2kx.

Before you proceed any further, you must read the following:
Please, please, please, learn about injection attacks!|||Thank you for the heads up. This particular function is only available via a password and IP address protected page that is going to be used on an Intranet.|||Assuming subcatid is numeric -> don't use the 'quotes' function on this field.|||Use params, apart from lots of other goodies they take care of irratating things like SQL within a value (including single quotes).

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Creating a Page Index and table of contents

This is the problem I am facing with the SQL server reporting services,
I am trying to create a report where in we have to
display Page index and the table of contents Along with the Page
Number. This report contains the list of products under a subcategory
which in turn are under particular Categories. The Page Index should
display the products names in the alphabetical order with the page
number where It falls, this is similar to the appendix at the end of
any textbook and the table of contents display the category and
its subcategories with page numbers Now, the problem is reading the
report dynamically to find out the page numbers where this product
falls and the categories falls . I want a solution for displaying the
Page index and the table of contents in SQL server reporting services
2005 version.
Waiting for quick sujjestions or help in this regardAre you using web service approach?
If so,you can always get page content before displaying it and by analyzing
the underlying HTML get all information you need -
page number, total number of pages, any internal error occurred, etc. Based
on that information you can build your own page header with a custom page
index.
"Aparna" <aparna.cirigiri@.gmail.com> wrote in message
news:1135255357.206805.159890@.g44g2000cwa.googlegroups.com...
> This is the problem I am facing with the SQL server reporting services,
>
> I am trying to create a report where in we have to
> display Page index and the table of contents Along with the Page
> Number. This report contains the list of products under a subcategory
> which in turn are under particular Categories. The Page Index should
> display the products names in the alphabetical order with the page
> number where It falls, this is similar to the appendix at the end of
> any textbook and the table of contents display the category and
> its subcategories with page numbers Now, the problem is reading the
> report dynamically to find out the page numbers where this product
> falls and the categories falls . I want a solution for displaying the
> Page index and the table of contents in SQL server reporting services
> 2005 version.
> Waiting for quick sujjestions or help in this regard
>

creating a folder on the server

Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
You can use xp_cmdshell to run internal commands such as MD, bat files and exe files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"robert" <rob@.ms.com> wrote in message news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how to
> call that.
> Any help would be appreciated
> Thanx
>
|||Another alternative is to do this as an ActiveX script task inside a DTS
package, which can be kicked off from your ASP page.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"robert" <rob@.ms.com> wrote in message
news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
|||Thanks will try it out.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ekf8gz6rFHA.464@.TK2MSFTNGP15.phx.gbl...
> Another alternative is to do this as an ActiveX script task inside a DTS
> package, which can be kicked off from your ASP page.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robert" <rob@.ms.com> wrote in message
> news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how
> to
> call that.
> Any help would be appreciated
> Thanx
>

creating / changing owner problem

I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.

//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();

//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;geoff (cakewalkr7@.hotmail.com) writes:
> I'm trying to create a new database and new login to allow a client
> (through my asp.net page) to create their own database, username and
> password. I've tried using the stored procedures I've found in BOL,
> but I can't get it to work right. It keeps saying that I can't assign
> this username as the db owner since it's already a user for the
> database. I then read other posts about reassigning the db owner to
> another dummy account and then trying to reassign it to the new one,
> but that isn't working either. Can anyone look at my code and tell me
> what I'm doing wrong? Thanks.

If you intend to make the login owner of the database, there is no
reason to create a user for him in the database, but just skip that
step. Once the login owns the database, he also has a user in it.

Note: A login in SQL Server is server-wide. A user is something in a
database, which can be connected to a login. The username and loginname
may be the same, but they don't have to.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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