Create table from web application

i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table.

this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page.

How can i permit my sql server to allow creation of tables from my web application.

Thanks

It all depends on the type of authentication that you are using for the app. The users of your web app will have to have an account capable of creating tables or whatever else you want them to do. What ever it is make an user account just for them. Please do not give the the sa account or you may be sorry later.|||

i want to know how i can authenticate the user to create the table from the server. I know i have to authenticate the user but what it is procedure to do that. I want to know how i can achieve that in Sql 2000

Thank you

|||Ok, What you can do now is in your connection string use the user and pass that the end user supplies. This will need to be a username and pass that you have supplied to them that is valid for the database. When the user logs in just add their user and pass to session so that you can call it back whenever you need it. Then when you create a connection for that user in your connection string use (string)Session["UserName"] and (string)Session["Password"] for the user and pass. To add their user and pass to the Session just use Session.Add("UserName",txtThetextboxthattheyentertheirusernamein.Text); and Session.Add("Password",txtThetextboxthattheyenteredtheirpasswordin.Text); I would put my connection properties in a class so that you can call them whenever and make your method so that it will accept the user and pass that you need to through it. Like this:
[pre]Public bool DBConnection(string Username,String Password)
{
bool connected = false;
Try
{
your connection params using the strings UserName and Password
connected = true;
}
Catch
{
connected = false;
}
Return connected;
}[/pre]
Then you can check the return value to make sure that they connected and use the connection for whatever you want to process. To call it up just type DBConnection((string)Session["UserName"],(string)Session["Password"])
if you need to check it set the return value to a bool and check for true or false
[pre]
bool didconnect = DBConnection((string)Session["UserName"],(string)Session["Password"])
if (didconnect != false)
{
Whatever stuff you need to run against the db;
}
else
{
Errormessage to the user;
}
[/pre]sql

Create new database based on a template using SMO

Hi All,

I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.

The relevant code (so far) is:

Dim sqlServerAs New Server()With sqlServer.ConnectionContext .ServerInstance ="(local)" .Connect() .Disconnect()End WithFor Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)NextDim newDatabaseAs New Database(sqlServer, DbName.Text.ToString)newDatabase.Create()

This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?

Thanks & regards,

Paul

One general question first, will the server be running nothing but these databases? If so you may well be able to simplify the process by creating a template database within the model database. When a new database is created, it will be populated using objects in model.

The second issue is one of security as effectively sa permissions are required to create a new database. Your security concerns may be insufficient for this to be an issue, however you would be well advised to employ a level of indrection. Instead of letting the users directly trigger the create process, set up a queue table in a suitable location and have a windows service monitor this queue and create a database as required.

To find out what is required in the way of TSQL, just generate a database create script for an existing database inside (Enterprise Manager for SQL2000 and SQL Server Management Studio for SQL2005).

|||

Hi,

Thank you for your reply. I appreciate any help as I've struggled on this whole problem for a couple of days and making very little progress...

Anyway, at the moment the SQL Server is only being used for the client databases in this application, but I don't know how long that will continue to be the case.

As for the security issue, only Administrators on the Active Directory account will ultimately be able to access the page for creating databases. At the moment I'm just using site security, but will be changing this later to Active Directory.

I had already created a script file, but as this was several thousand lines, I'm rather hoping for a more manageable solution!

Thanks again,

Paul

|||Are you able to use multiple SQL Instances on that server? If so create an instance just for this application and you could use the model approach. I am glad that you have already considered security - for many applications, secuirity is an afterthought if it is thought of at all.|||

Hi,

I know that I should know, but I have no idea if I can create multiple instances of the server or not. Assuming that I can, what exactly is the model approach? How do I make fresh copies of the amended 'Model' database?

Thanks again,

Paul

Create mdf file?

Scenario:
I have a fully licensed SQL server 2005 database that my production
application uses. I have several contract sales people who take my web
application, visit prospective clients and perform a demonstration of our
product (by connecting to the internet to access the database).
I would like to have the application run completely on the laptops my sales
team uses, so I am wondering how to move my existing SQL 2005 database to a
.mdf file that can be places in the app_data folder so the application will
not have to access the Internet for the demonstration).
I don't know what the steps are to do this, or how to create the conection
string to point to a local database in the app_data folder.
Thanks in advance!
BrianBrian,
Of course, the laptop will need to have SQL Server installed on it. If your
database is not too big (<4 GB) you can use SQL Server Express. BACKUP your
database and RESTORE the backup to the laptops. (This will place an MDF and
an LDF on the laptop. You need both.)
Since logins will not come over with the restore, you will also need to set
up a login for the person demonstrating your product. But that should not
be a problem to do.
I would suggest that you do this once on a single laptop. Once you have it
just the way you want it, take backups of all the databases on the laptop.
Then for the other laptops, install SQL Server Express and then restore all
the backups to each traveling laptop.
RLF
.
"AutoTrackerPlus" <brian.cesafsky@.autotrackerplus.com> wrote in message
news:Oz5QIkTzHHA.4652@.TK2MSFTNGP05.phx.gbl...
> Scenario:
> I have a fully licensed SQL server 2005 database that my production
> application uses. I have several contract sales people who take my web
> application, visit prospective clients and perform a demonstration of our
> product (by connecting to the internet to access the database).
> I would like to have the application run completely on the laptops my
> sales team uses, so I am wondering how to move my existing SQL 2005
> database to a .mdf file that can be places in the app_data folder so the
> application will not have to access the Internet for the demonstration).
> I don't know what the steps are to do this, or how to create the conection
> string to point to a local database in the app_data folder.
> Thanks in advance!
>
> Brian
>|||It is not as simple as including the data files. Your application
does not touch the files, and would not know what to do with them if
it did. Your application talks to the SQL Server service running on
the server, and SQL Server deals with the database files.
To run the application on the laptops you will need SQL Server
installed and running on the laptops. In this case you can probably
get away with SQL Express, which is free. That assumes the database
is no more than 4GB, the SQL Express limit. You will probably want
reasonably robust laptops, particularly when it comes to memory where
1GB is probably a minimum. I suggest thorough testing to confirm that
performance is good enough to show customers
Roy Harvey
Beacon Falls, CT
On Mon, 23 Jul 2007 09:51:57 -0500, "AutoTrackerPlus"
<brian.cesafsky@.autotrackerplus.com> wrote:
>Scenario:
>I have a fully licensed SQL server 2005 database that my production
>application uses. I have several contract sales people who take my web
>application, visit prospective clients and perform a demonstration of our
>product (by connecting to the internet to access the database).
>I would like to have the application run completely on the laptops my sales
>team uses, so I am wondering how to move my existing SQL 2005 database to a
>.mdf file that can be places in the app_data folder so the application will
>not have to access the Internet for the demonstration).
>I don't know what the steps are to do this, or how to create the conection
>string to point to a local database in the app_data folder.
>Thanks in advance!
>
>Brian
>

Create Excel Output

I successfully use the code below to output a report to a web page or to a
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>

CREATE DATABASE from Template?

Hello,
I need to be able toCREATE DATABASE by copying an existing database.
I would be doing this inside of a web app during an event.
How do I set this up on SQL 2005 ?

Thanks!

Here's an article on how to do it in PostgreSQL http://www.enterprisedb.com/documentation/manage-ag-templatedbs.htmlSQL Server 2005 has 'Copy Database Wizard' in Management Studio; you can also copy databases with Backup and Restore. But both methods seems not so easier to be done inside of web app during an event. Anyways you can take a look at 'Copying Databases to Other Servers' topic in SQL2005 Books Online.|||What if i made a backup of my "template" db
then had a stored proc like:

create procedure restoredb
@.dbname sysname
as
restore database @.dbname from disk='c:\backup.bak'
with move 'file_data' to 'd:\mssql\mssql\data\' + @.dbname + '_data.mdf',
move 'file_log' to 'd:\mssql\mssql\data\' + @.dbname + '_log.ldf',
replace

that created the new db

Create Data Driven Subscription via C# code? SSRS 2005 EE (or Batch Reports?)

Hello,

Does anyone know if you can create a Data Driven Subscription via code (C#)? The web based Report Manager does it via the Subscriptions.aspx (I assume). Seems like they are using theMicrosoft.ReportingServices.UI.Pages.Subscriptions namespace.

I can't find any info on anybody doing it. So, here's my need, please tell me if I am on the right track.

In my ASP.NET 2.0 C# app, we use the SSRS Web Service (reportservice2005) to generate PDF reports in a new window. If the User requests a large amount of pages (say 1000), the request is timing out, as well as tying up the web server.

So, the thought was, why not submit these reports to a "Batch". It seems that Data Driven Subscriptions could do it but what I'd want is to create the Subscription on the fly and clean them up after they run (or weekly).

Should I go this approach,OR should I write a Windows Service (or Console App) that just uses the same SSRS Web Service (reportservice2005) to create my reports in batch mode?

Any thoughts are helpful.

Nothing like answering your own question. I guess after a good night's sleep I Googled the right phrase to see this link:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005_methods.aspx

There is a CreateDataDrivenSubscription() method.

However, given my original question on proper approach,should I be doing it this way or write the windows service or console app to do it?

Thoughts?

|||We are looking into the same concept. We are trying to create a single data-driven subscription that runs nightly and all the "requests" for the day are processed. We're achieving this by inserting the "request" into the database. The only problem we are having now is how to do this for multi-value parameters since each row creates a different subscription and you can only reference one column per parameter. Good luck!