create table with dynamic constraint

I am trying to create a table with the type of constraint I don't see in any
of the help resources.
Say the basic table structure for table t1 is (colType int, colDesc
varchar(10), colMiscellaneous varchar(100))
I want to limit the combination colType-colDesc thusly:
If the combination is new, it's okay.
If the combination is exactly the same as one previously used, it's okay.
If the colDesc is the same as one previously entered, but the colType is
different, the constraint is violated and the insert or update operation
aborts.
Is this even doable? I've used multi-column constraints before, but not in
this way.
Thanks in advance,
DaveIn t-SQL, you cannot have a query expression in a CHECK constraint, so
multi-row checks are not easy to implement declaratively. You can have a
scalar UDF in certain cases, but it might fail for UPDATE operations. So one
option is to use a trigger like:
CREATE TRIGGER trg ON t1 FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT <> 0 RETURN
IF EXISTS ( SELECT * FROM inserted i
WHERE EXISTS ( SELECT * FROM t1
WHERE t1.type = i.type
AND t1.descr <> i.descr )
) ROLLBACK
... -- add any error messages if needed.
Anith|||>> IF @.@.ROWCOUNT <> 0 RETURN
should be = 0 to see if there are any rows affected
Anith|||Dave,
I think an easier solution here would be to maintain two tables:
create table cols (
colType int not null primary key,
colDesc varchar(10)
)
create table colMisc (
colType int not null references cols(colType),
colMiscellaneous varchar(100)
)
This enforces the data integrity you want:
A single colType cannot have more than one description
A colMiscellaneous value must be associated with a colType and colDesc
You could preserve an interface like you have by creating a view to match
your current table, on which there is an INSTEAD OF trigger to perform
the one or two insert statements needed for each addition of a
colMiscellaneous
value. It may not seem like less work to do this, but it avoids what you're
awkwardly doing now, which is storing facts like "the description of
column #N
is blahblah" once for every colMiscellaneous value there happens to be
for that
column.
In the long run, what you're doing will likely get you into trouble that
you have to solve with more awkwardness, like by adding DISTINCT
to queries that shouldn't need it.
Steve Kass
Drew University
Dave wrote:

>I am trying to create a table with the type of constraint I don't see in an
y
>of the help resources.
>Say the basic table structure for table t1 is (colType int, colDesc
>varchar(10), colMiscellaneous varchar(100))
>I want to limit the combination colType-colDesc thusly:
>If the combination is new, it's okay.
>If the combination is exactly the same as one previously used, it's okay.
>If the colDesc is the same as one previously entered, but the colType is
>different, the constraint is violated and the insert or update operation
>aborts.
>Is this even doable? I've used multi-column constraints before, but not in
>this way.
>Thanks in advance,
>Dave
>
>|||That is an excellent point, and one that I had considered. However, there
really are only three columns, this is just an ancillary table of about 50
rows that will not get many hits, and there will be only one routine for
each of the operations (SELECT, INSERT, UPDATE, & DELETE). I was also just
curious how I would accomplish such a task.
I do know enough about normalization to recognize your solution is
theoretically better; in this case I think the fewer tables factor will
outweigh the drawbacks you point out.
Thanks,
Dave
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23S36LBWbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> Dave,
> I think an easier solution here would be to maintain two tables:
> create table cols (
> colType int not null primary key,
> colDesc varchar(10)
> )
> create table colMisc (
> colType int not null references cols(colType),
> colMiscellaneous varchar(100)
> )
> This enforces the data integrity you want:
> A single colType cannot have more than one description
> A colMiscellaneous value must be associated with a colType and colDesc
> You could preserve an interface like you have by creating a view to match
> your current table, on which there is an INSTEAD OF trigger to perform
> the one or two insert statements needed for each addition of a
> colMiscellaneous
> value. It may not seem like less work to do this, but it avoids what
you're
> awkwardly doing now, which is storing facts like "the description of
> column #N
> is blahblah" once for every colMiscellaneous value there happens to be
> for that
> column.
> In the long run, what you're doing will likely get you into trouble that
> you have to solve with more awkwardness, like by adding DISTINCT
> to queries that shouldn't need it.
> Steve Kass
> Drew University
> Dave wrote:
>
any

Create SCHEMA - Basic Question - 2005

I am trying to execute the following T-Sql snippet and it gives an
error:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
Error = Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SCHEMA'.
I can't for the life of me work out what's worng. Can someone point me
in the right direction please? Thanks.'create schema' must the be the first line in a batch. here is the
workaround.
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
Exec('CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]')
-oj
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1150474587.056097.296010@.c74g2000cwc.googlegroups.com...
>
> I am trying to execute the following T-Sql snippet and it gives an
> error:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
> CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
> Error = Msg 156, Level 15, State 1, Line 26
> Incorrect syntax near the keyword 'SCHEMA'.
> I can't for the life of me work out what's worng. Can someone point me
> in the right direction please? Thanks.
>|||> 'create schema' must the be the first line in a batch. here is the
> workaround.
Wouldn't it be nice if the error message were similar to the one you get
when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't this
error returned instead of incorrect syntax:
Msg 111, Level 15, State 1, Line 2
'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
A|||yeah...you know how to send a bug/wish report, right. ;-)
-oj
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23V9N94WkGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Wouldn't it be nice if the error message were similar to the one you get
> when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't
> this error returned instead of incorrect syntax:
> Msg 111, Level 15, State 1, Line 2
> 'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
>
> A
>sql

Create Linked Report method

Does anyone have a sample of a CreateLinkedReport method they are using? I am using Reporting Services 2005 and Visual Basic 2005. I need the VB code and how I need to call it. I'd like to change one parameter, and then send it to select recipients using a specified schedule.

Thanks, Iris

Is there anything wrong with the BOL sample code?|||

I'll try that example.

Also, when I try to click Create Linked Report from a master report that I created, I get an internal error. In SS SP2, it shows that this problem was fixed. When I installed SP2 today, it did not fix the problem. I am using a Stored Procedure with one parameter of int value.

Any ideas?

Thanks, Iris

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 with Visual Basic (Urgent)

can i run such a transact SQL script with VB

use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go

Have you tried calling Osql.exe and passing the tsql as a parameter ?|||Yes you can use the TSQL script and run it in VB as a command|||

You cannot execute the GO, as it is a batch seperator defined by the SQL Server tools. You will have to set the context in your connection command, or simply run it in two different batches.

But you should be able to run any T-SQL command from VB.

|||

Use ADO in VB6, ADO.net with VB.net.

As previously mentioned by MSVP, run the db creation in your command text object

Adamus

|||

Ayhan Yerli wrote:

can i run such a transact SQL script with VB

use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go

Wait...you can't USE a db to CREATE a db?

What are you trying to do?

Adamus