CREATE TABLE rights problems

I cannot fix the rights depending on customers server...

when i create a table dynamicly with ASP.NET for MS SQL 2000, should I use :

CREATE TABLE [dbo] .[Component]

or

CREATE TABLE [Component]

-------

[id_Component] [int] IDENTITY (1, 1) NOT NULL ,

or

[id_Component] [int] IDENTITY (1, 1) COLLATE French_CI_AS NOT NULL ,

-------

shall I add COLLATE French_CI_AS to all columns ?

thank youIf you don't explicitly specify the collation, the system will use the database collation as the default.

It's a best practice to be explicit for DDL/DML. So, [dbo] and [COLLATE] should be specified.|||thank you !

create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

CREATE TABLE in wrong database (Master)

I am using ASP.NET 1.1 and MS SQL 2005

the folowing ODBC stringconnection
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written inMaster !! and not inMyBase

I am using windows Authentication

what can be wrong ?

thank youWhat is your reason for creating table in VB.NET instead of managment studio with SQL or the GUI and why are you using obsolete ODBC instead of ADO.NET? When you get the answer to that you will know why you are creating the table in the Master database. Hope this helps.|||Caddre if you donyt know the answer to that problem please give up !
i must do it in that way because the database allready exists in many intranets on > 10 000 PC|||the problem is why is it creating tables in Master when the connection string =
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Connection=;

?

|||But you still don't do it that way, you have two options get the DBA to create the table for you or you should just right click and register the SQL Server with the database at the top of management studio. The number of users on the network is not relevant the only requirement is on the same network and in an intranet you are. And you don't need new SQL Server with your SQL Server running your DBA can give you the personal SQL Server free it comes with the license. Here I have read only access to Oracle if I need to create something the DBA will create it. And yes I also use SQL Server I can help you with most problems. Post again if you still need more help. Hope this helps.|||thanks a lot Caddre but it works fine now

just forgottent database= in the connectionstring when i have copy-pasted the string

and that way of course works perfectly|||I am glad you got it resolved and I am sorry about my first post.

Create Table but how?

Hi!

I've searched the tutorial for the create table syntax in asp.net. How to handle the SQL-string. How is it going? Do I have to use a SQL-Adapter or just the Connection? I can't access the Database with the Enterprise-Manager, so I have to do it in the good old way by using SQL. Can somebody give me the statements or a short codesample?

Thanks a lot

tobi.Net Data Access Technology (ADO.NET) is'nt different in windows applications and web applications.
You can do it either using a Data Adapter or just a connection and sqlCommands
If you can't access your database in Enterprise Manager , perhaps your database is'nt installed in SQL Server.|||Well, my SQL Server at home for tests is installed and running. But I still got some problems with connecting to it. Now I'm using a providers SQL-Database I can't connect with the Ent.-Manager to.

Can you give me a codesample for a create table execution? I havent' worked with .net so close...sql

CREATE TABLE in wrong database (Master)

I am using ASP.NET and a normal ODBC stringconnection

Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBase

i am using windows authentication

what can be wrong ?

thank youHi

Your default db will be master. You need to name your params in the string. How about:

Driver={SQL Server};Server=(local);Database=MyBase;Uid=;Pwd=;T rusted_Connection=;
?

EDIT - BTW - do you not need to put True after trusted connection or does that work?|||And...

never need to ask a connection string question of anyone again:
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

HTH|||my connection string is perfect .. it is not a connectionstring problem|||No it's not. Have another look. MyBase is just floating there. And Trusted Connection, Uid and Pwd are parameters without values.

Check the links.|||my connection string is perfect .. it is not a connectionstring problem

So....

Why are you asking for help then?|||So....

Why are you asking for help then?It's perfect... That doesn't mean that it is working.

-PatP|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem too|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.

there is no UserId no password I am using windows authentication

what do you mean by Then the default would be MyBase instead of Master.

do you mean the user account ? I am using only one user account for all my databases

thank you|||Hi

Whatever account you use (SQL or NT) to connect to SQL Server will have a default database assigned to it. If you don't specify a database in your connection string you will connect to the default database. You have not specified a database in your connection string (however perfect it may be) so you are connecting to your default database, typically master (master is the default default database :) ). So - either specify a database in your connection string or change your default database in SQL Server.|||it works now !!

thank you|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem tooAh - just realised your problem - I bet in 2000 the default db was set up but not in 2005 eh? Seriously - the MyBase bit in your connection string is doing nothing - I'm surprised it didn't throw an exception to be honest.

Glad to have helped :D|||it works now !!

thank you

Must be the miracle connection string...

And I'm sure you didn't change a thing...

Create reports in ASP format?

We use an internal tool that can display ASP pages. I would like to use SQL Reporting Services to create reports in ASP format. The ASP page will access the database to get data. Is this possible possible with Reporting Services?

Ric

Hmmm... from what I understand, an ASP page is very similar to an ASPX page (created by Visual Studio). Possibly you could embed a report viewer in your ASP page. However, I'm not really sure that this is possible.

I'm not so sure that you want to create reports in ASP format -- that isn't possible. I think you are saying you want to display reports within an ASP page.

|||Active Server Pages and C# are completely different. Similar only because they are both programming languages. ASP was never meant to work with SQL Reporting Services. ASP works through ODBC and other interfaces and can connect to SQL databases and display the data onto web pages that have been setup using HTML tables using such things as the for loop etc..., but was never meant to work in this enviorment.

Create Reporting Services on Windows Form

How can I create a report on windows form?
I can create in asp.net with report viewer but cannot do it on windows form!
Any reference that can tell me how to do that?
Thanks
JasonAdd a AxSHDocVw.AxWebBrowser to the form and call the report as follows
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason|||Add an AxSHDocVw.AxWebBrowser to you form and then call it using:
Object o = null;
axWebBrowser1.Navigate("path to report", ref o, ref o, ref o, ref o);
This is probably the easiest way.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason|||You need to use the Web Service to render reports.
Refer to the following link :
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/RSPROG/htm/rsp_prog_intro_7vqa.asp
>--Original Message--
>How can I create a report on windows form?
>I can create in asp.net with report viewer but cannot do
it on windows form!
>Any reference that can tell me how to do that?
>Thanks
>Jason
>.
>|||Jason,
The easiest way to to this is to shell out to the browser. For example, if
your app is .NET WinForm app, you can do:
Process.Start ("IExplore", <report url>)
If you need to embed the report inside the form, you can use the Microsoft
WebBrowser ActiveX control. You may have a look at the RSExplorer sample
which comes with RS to find out how this could be implemented.
The above two options generate reports via URL addressability.
Alternatively, you can render the report by SOAP by calling the Render SOAP
API. In general, I would recommend you evaluate the URL addressability
option first since it is the fastest, richest and simplest way to render
reports.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason

Create indexes for Query Optimization

I am using ASP.net application on SQL server 2005.
I am getting data from the DB using this SQL statement from 2 tables:
Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
Table1 has around 2,000,000 Records and table has 3 records.
The query is running very slow.
How do Optimize the query using Indexes ?
On Which fields should I create the indexes ?
Thanks
ra294
ra294@.hotmail.com
On Jan 7, 6:56Xpm, "ra294" <ra...@.hotmail.com> wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes X?
> Thanks
> ra294
> ra...@.hotmail.com
Do you have index on either columns a or b? Also, you might consider
remove the order by clause and do it later.

Create indexes for Query Optimization

I am using ASP.net application on SQL server 2005.
I am getting data from the DB using this SQL statement from 2 tables:
Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
Table1 has around 2,000,000 Records and table has 3 records.
The query is running very slow.
How do Optimize the query using Indexes ?
On Which fields should I create the indexes ?
Thanks
ra294
ra294@.hotmail.comra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>
try using database engine tuning advisor, find it in tools menu of the
management studio|||Without looking at the data Im just guessing but try doing this:
Table1 index: Key columns: a,b,e
Table2 index: key columns: e included: c
MC
"ra294" <ra294@.hotmail.com> wrote in message
news:uTLstxSUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>|||ra294,
You might want to recheck your posting. The query is not valid (because
of typos?), and the number of rows are suspect (really just 3 rows in
one table and 2 million rows in the other table?).
How many rows does the query return? 3? 6 million? How big is the table
with the 2 million rows (how many pages or how much MB)?
How slow is the query currently? 200 milliseconds, 5 seconds, 5 minutes?
In general, you should always define a Primary Key for each table. This
will automatically create a corresponding unique index. You should also
define any Foreign Key relations. In general, it is a good idea to index
Foreign Key relations.
If you would like more assistence, then please post simplified DDL (and
the answers to the questions above).
--
Gert-jan
ra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com|||On Jan 7, 6:56=A0pm, "ra294" <ra...@.hotmail.com> wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=3DTable.e Where Table1.a=3DXXX AND Table1.b>YYY Order Table1.a DE=SC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes =A0?
> Thanks
> ra294
> ra...@.hotmail.com
Do you have index on either columns a or b? Also, you might consider
remove the order by clause and do it later.

Create Database on a Virtual Drive (Created with subst) Failed

When I try to add a (SQL Server 2005 Express Edition) database to my project (I'v tried a windows application and an ASP.NET application) in Visual Studio 2005 Professional Edition; It fails with an error like this :
"create database failed. some file names listed could not be created (...)"
but when I open the project from the real path It works.
Is this a bug? Is there any solution? (In many situations there is a need for working with virtual drives. There must be some work around ...)

Thanks

SQL Server does not recognize OS level 'mapped' drives.

You must use a 'actual' drive, or a SAN/NAS lun(drive).