Create Tables Dynamically

Hi! I am using VS 2003 (v 1.1)

I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.

How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.

Give me the complete code with can example (if possible)

Thanks & Regrads

Jai Shankar

Here's anexample of how to create dynamic controls and add events. You can apply this to any type of control you need (in you case a Table).

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 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 full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

Create full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

Create DTS PACKAGE programmatic

Hi guys..!!

i am working on Dynamic creation of DTS-packages in C#.NET 2005(sql server 200)

but i not must create package in Sql Server.
but i cant...

can i do ?
any ideas ?

thx...

DTS has a Save as VB option. This is a great way of getting sample code, so try that on a demo package. VB to VB.Net has some differences, see this link for some tips.

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

VB.Net to C# should not be hard, plenty of tools that do this.

Hopefully when you view the VB sample code you will get a feel for the object model and how it is used, and going forward you can just write the C# directly.

There is a DTS specific newsgroup (microsoft.public.sqlserver.dts) which may be worth search and posting on, as it is DTS not SSIS focused.

|||

but this source not contain code for vb or vb.net
no problem is writing vb or vb.net

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

thx...

|||

You appear to be repeating yourself, so can we try again...

Mehmet Metin Altuntas wrote:

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

To create a DTS package dynamically in dotnet you need to write some code. DTS uses an object model, have you added a reference?

Mehmet Metin Altuntas wrote:

but this source not contain code for vb or vb.net

What is not source code? I suggested using the Save as VB option to generate some sample code. If you already know how to use the DTS object model in code, what are you asking? If you do not know how to use the DTS object model in code, try generating some sample code as a guide.

Mehmet Metin Altuntas wrote:

no problem is writing vb or vb.net

Sorry, that does not make sense.

The Save as VB option wil give you VB code. The link told you how to convert this to VB.net. There are tools that convert VB.net to C#. It may be a three stage process but it will give you C# code. Once you are familiar with this you can write c# directly, but to HELP you LEARN how to use it in code, Save as VB is a start point.

Surely some code, even VB is easier to learn from than no code at all. There is no Save As C# option, so learn from the VB.

Any help?

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?
Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>|||Thanks for the reply, John. The code creates a db for each chapter of the
book and brings in the data and log files for each chapter. It would seem
that the code from the book should run or else I could find lots of compaints
around the web. Since I'm new to the game of SQL Server, I wanted to
discover what missteps that I am taking. Could you look at the code for me
to see if there is any unmentioned setup that I need to do before running
this? Here's the chapter 2 sample:
--CreateSampleDB
--Execute statements from the master database.
USE master
GO
--Drop any prior version of Chapter02 database.
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = N'Chapter02')
DROP DATABASE Chapter02
GO
--Create new version of Chapter02 database.
CREATE DATABASE Chapter02
ON
(NAME = Chapter02_dat,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
SIZE = 1)
LOG ON
(NAME = Chapter02_log,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
SIZE = 1,
MAXSIZE = 5)
GO
"John Bell" wrote:
> Hi
> It sounds like you should be attaching the files rather than creating them?
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> > I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> > now,
> > I'm doing lots of crying.) When I try to create the database required for
> > exercises, I receive the error:
> >
> > "Server: Msg 5105, Level 16, State 2, Line 3
> > Device activation error. The physical file name
> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >
> > I copied the physical file name location from Windows Explorer so it
> > should
> > be ok.
> > I checked the MSKB for any bugs and worked around all that are listed
> > including the default directory malady.
> >
> > Any ideas on how to install the database?
> >
>
>|||The create database statement looks fine, provided of course that the file
named in the create db does NOT exist at the time you are creating the db.
Create database will try and create the underlying database files, so like
John if the book comes with example database files I would expect to copy
them off the cd from the book and then attach them using sp_attachdb. Have
you a file of the name used in the create database statement already?
Mike John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Hi
This version of the create database statement would error if the file
existed, but your error seems to imply
that the directory does not exist or that your permissions are incorrect.
Even if the file is read only (which you should change if you copied it from
CD!) the file existing would give the same error.
Check out sp_attach_db in Books online.
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Thanks all for the thoughts. I discovered script below that attaches the
files (unfortunately, I still receive the same Msg 5105 that started this
thread).
I double-checked the mdf and ldf files and they are not set to read only;
however, the file folder is read-only and will not change. Ideas?
New script:--
--AttachSampleDB
--Run the script from the master database.
USE master
--Update the paths for the data and log files so they
--are appropriate for your computer.
EXEC sp_attach_db @.dbname = N'Chapter02',
@.filename1 = N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
@.filename2 = N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
"John Bell" wrote:
> Hi
> This version of the create database statement would error if the file
> existed, but your error seems to imply
> that the directory does not exist or that your permissions are incorrect.
> Even if the file is read only (which you should change if you copied it from
> CD!) the file existing would give the same error.
> Check out sp_attach_db in Books online.
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> > Thanks for the reply, John. The code creates a db for each chapter of the
> > book and brings in the data and log files for each chapter. It would seem
> > that the code from the book should run or else I could find lots of
> > compaints
> > around the web. Since I'm new to the game of SQL Server, I wanted to
> > discover what missteps that I am taking. Could you look at the code for
> > me
> > to see if there is any unmentioned setup that I need to do before running
> > this? Here's the chapter 2 sample:
> >
> > --CreateSampleDB
> > --Execute statements from the master database.
> > USE master
> > GO
> >
> > --Drop any prior version of Chapter02 database.
> > IF EXISTS (SELECT *
> > FROM INFORMATION_SCHEMA.SCHEMATA
> > WHERE CATALOG_NAME = N'Chapter02')
> > DROP DATABASE Chapter02
> > GO
> >
> > --Create new version of Chapter02 database.
> > CREATE DATABASE Chapter02
> > ON
> > (NAME = Chapter02_dat,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > SIZE = 1)
> > LOG ON
> > (NAME = Chapter02_log,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> > SIZE = 1,
> > MAXSIZE = 5)
> > GO
> >
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> It sounds like you should be attaching the files rather than creating
> >> them?
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> > Microsoft
> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> >> > now,
> >> > I'm doing lots of crying.) When I try to create the database required
> >> > for
> >> > exercises, I receive the error:
> >> >
> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> > Device activation error. The physical file name
> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >
> >> > I copied the physical file name location from Windows Explorer so it
> >> > should
> >> > be ok.
> >> > I checked the MSKB for any bugs and worked around all that are listed
> >> > including the default directory malady.
> >> >
> >> > Any ideas on how to install the database?
> >> >
> >>
> >>
> >>
>
>|||Hi
Both the create and the attach scripts assume you have a directory called
C:\SQLVBNET\Chapter_02, in which the new database files will be put.
Do you have this directory already?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> Thanks all for the thoughts. I discovered script below that attaches the
> files (unfortunately, I still receive the same Msg 5105 that started this
> thread).
> I double-checked the mdf and ldf files and they are not set to read only;
> however, the file folder is read-only and will not change. Ideas?
> New script:--
> --AttachSampleDB
> --Run the script from the master database.
> USE master
> --Update the paths for the data and log files so they
> --are appropriate for your computer.
> EXEC sp_attach_db @.dbname = N'Chapter02',
> @.filename1 => N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> @.filename2 => N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
>
> "John Bell" wrote:
>> Hi
>> This version of the create database statement would error if the file
>> existed, but your error seems to imply
>> that the directory does not exist or that your permissions are incorrect.
>> Even if the file is read only (which you should change if you copied it
>> from
>> CD!) the file existing would give the same error.
>> Check out sp_attach_db in Books online.
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
>> > Thanks for the reply, John. The code creates a db for each chapter of
>> > the
>> > book and brings in the data and log files for each chapter. It would
>> > seem
>> > that the code from the book should run or else I could find lots of
>> > compaints
>> > around the web. Since I'm new to the game of SQL Server, I wanted to
>> > discover what missteps that I am taking. Could you look at the code
>> > for
>> > me
>> > to see if there is any unmentioned setup that I need to do before
>> > running
>> > this? Here's the chapter 2 sample:
>> >
>> > --CreateSampleDB
>> > --Execute statements from the master database.
>> > USE master
>> > GO
>> >
>> > --Drop any prior version of Chapter02 database.
>> > IF EXISTS (SELECT *
>> > FROM INFORMATION_SCHEMA.SCHEMATA
>> > WHERE CATALOG_NAME = N'Chapter02')
>> > DROP DATABASE Chapter02
>> > GO
>> >
>> > --Create new version of Chapter02 database.
>> > CREATE DATABASE Chapter02
>> > ON
>> > (NAME = Chapter02_dat,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
>> > SIZE = 1)
>> > LOG ON
>> > (NAME = Chapter02_log,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
>> > SIZE = 1,
>> > MAXSIZE = 5)
>> > GO
>> >
>> >
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> It sounds like you should be attaching the files rather than creating
>> >> them?
>> >>
>> >> John
>> >>
>> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> >> > Microsoft
>> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
>> >> > right
>> >> > now,
>> >> > I'm doing lots of crying.) When I try to create the database
>> >> > required
>> >> > for
>> >> > exercises, I receive the error:
>> >> >
>> >> > "Server: Msg 5105, Level 16, State 2, Line 3
>> >> > Device activation error. The physical file name
>> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >> >
>> >> > I copied the physical file name location from Windows Explorer so it
>> >> > should
>> >> > be ok.
>> >> > I checked the MSKB for any bugs and worked around all that are
>> >> > listed
>> >> > including the default directory malady.
>> >> >
>> >> > Any ideas on how to install the database?
>> >> >
>> >>
>> >>
>> >>
>>|||Thank you all for your help. As my name dictates I am really new to the game
and discovered that I transposed the directory names. I feel quite foolish.
Kalen's simple answer made me realize my grievous error.
Thank you.
"Kalen Delaney" wrote:
> Hi
> Both the create and the attach scripts assume you have a directory called
> C:\SQLVBNET\Chapter_02, in which the new database files will be put.
> Do you have this directory already?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> > Thanks all for the thoughts. I discovered script below that attaches the
> > files (unfortunately, I still receive the same Msg 5105 that started this
> > thread).
> >
> > I double-checked the mdf and ldf files and they are not set to read only;
> > however, the file folder is read-only and will not change. Ideas?
> >
> > New script:--
> > --AttachSampleDB
> > --Run the script from the master database.
> > USE master
> >
> > --Update the paths for the data and log files so they
> > --are appropriate for your computer.
> > EXEC sp_attach_db @.dbname = N'Chapter02',
> > @.filename1 => > N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > @.filename2 => > N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> This version of the create database statement would error if the file
> >> existed, but your error seems to imply
> >> that the directory does not exist or that your permissions are incorrect.
> >> Even if the file is read only (which you should change if you copied it
> >> from
> >> CD!) the file existing would give the same error.
> >>
> >> Check out sp_attach_db in Books online.
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> >> > Thanks for the reply, John. The code creates a db for each chapter of
> >> > the
> >> > book and brings in the data and log files for each chapter. It would
> >> > seem
> >> > that the code from the book should run or else I could find lots of
> >> > compaints
> >> > around the web. Since I'm new to the game of SQL Server, I wanted to
> >> > discover what missteps that I am taking. Could you look at the code
> >> > for
> >> > me
> >> > to see if there is any unmentioned setup that I need to do before
> >> > running
> >> > this? Here's the chapter 2 sample:
> >> >
> >> > --CreateSampleDB
> >> > --Execute statements from the master database.
> >> > USE master
> >> > GO
> >> >
> >> > --Drop any prior version of Chapter02 database.
> >> > IF EXISTS (SELECT *
> >> > FROM INFORMATION_SCHEMA.SCHEMATA
> >> > WHERE CATALOG_NAME = N'Chapter02')
> >> > DROP DATABASE Chapter02
> >> > GO
> >> >
> >> > --Create new version of Chapter02 database.
> >> > CREATE DATABASE Chapter02
> >> > ON
> >> > (NAME = Chapter02_dat,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> >> > SIZE = 1)
> >> > LOG ON
> >> > (NAME = Chapter02_log,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> >> > SIZE = 1,
> >> > MAXSIZE = 5)
> >> > GO
> >> >
> >> >
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> It sounds like you should be attaching the files rather than creating
> >> >> them?
> >> >>
> >> >> John
> >> >>
> >> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> >> > Microsoft
> >> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
> >> >> > right
> >> >> > now,
> >> >> > I'm doing lots of crying.) When I try to create the database
> >> >> > required
> >> >> > for
> >> >> > exercises, I receive the error:
> >> >> >
> >> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> >> > Device activation error. The physical file name
> >> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >> >
> >> >> > I copied the physical file name location from Windows Explorer so it
> >> >> > should
> >> >> > be ok.
> >> >> > I checked the MSKB for any bugs and worked around all that are
> >> >> > listed
> >> >> > including the default directory malady.
> >> >> >
> >> >> > Any ideas on how to install the database?
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>

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

Create Database models from XML Schema (.xsd)

Hi .Net Guru's,

I have an urgent requirement for my project; the issue is mentioned below;
Using .Net(C#/VB.Net) I need to generate/created Database objects from XML schemas.

I don't have any sample xml schema file to give you.
You just imagine you have a sample .xsd file and this .xsd file will be used to create database tables.

Please let me know if you have any queries.

Thanks,
nick

Hi

You could create a DateSet from visual studio and add some TableAdapters to it VS will generate XSD for you.

And there is a free tool XSD2DB Utilitylets you manage database schemas using XSD files.

Hope this helps.