Problem creating database diagram

I have both SQL Server 2000 and SQL Server 2005 installed on my PC running windows XP SP2.

In the Microsoft SQL Server Management Studio, when I right click Database Diagram in the Object Explorer, I cannot find any option to create a database diagram. What is given is "Working with SQL Server 2000 diagrams".

How can I fix my configuration so that I am able to create database diagrams in the SQL Server 2005?

Did you expand the database diagrams folder first to get the support objects created in the database?

What menu items do you get when you right click on the database diagrams folder?

Also in Help -> about, what version of SQL Server Management Studio do you have installed?

|||1. There isn't anything to expand under the database diagrams folder.
2. Right clicking on this folder gives three items:
a. Working with SQL Server 2000 diagrams
b. Reports --> Custom Report ...
c. Refresh
3. Version info:
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Hope these info help. Thanks.

problem creating assembly using other assemblies

Hi,

I have developed a Stored procedure to output text to a text file, after doing a lot of reading here.
I want to be able to output data to a Postgres database, to be used for web mapping. I have referenced an Assembly Npgsql.dll so I can send updates to the postgres database when my ms sql database is updated. However, after building my class library, when I try to CREATE ASSEMBLY in sql server 2005 express, it sends out a message:
Msg 10301, Level 16, State 1, Line 1
Assembly 'ClassLibrary1' references assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(error not found)). Please load the referenced assembly into the current database and retry your request.
The error seems to be in the Npgsql.dll assembly, can anybody please shed some light on this.
I tried to load the system.drawing assembly, and can do so in Unrestricted mode. It then spits out the same problem for System.Windows.Forms.dll.
I am still very new to all of this so any help would be appreciated.
Cheers,
Jatz91.

I got around this problem.

I used CREATE ASSEMBLY with PERMISSION_SET=UNSAFE and put the Microsoft.NET Framework Assemblies in the same folder where my built assembly was.

When I used CREATE ASSEMBLY, a warning was spat out for each assembly saying:

Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

So now I run my CLR in UNSAFE mode

Cheers

Problem creating ASPNETDB under SQL Server 2005

Hi All... We're cloning an application currently under development on Server 2003 / SQL Server 2005 / dotnet 2.0. The clone's going to live on an XPSP2 box with SQL Server 2003 - it's for demo purposes... The application's able to get to our own database, but it's having a hard time creating the ASPNETDB that dot net likes to have.

Aside from the OS, the only difference between the two SQL Servers is that on the working system, we're using windows authentication on the database while on the non-working system, we're using SQL Server authentication. But we've tuned the connection strings appropriately - again, we can get to our own db just fine.

We have the following lines in our web.config:

<connectionstrings>
<clear />
<add name="LocalSqlServer" connectionstring="Server=localhost; Database=ASPNETDB; UID=sa; PWD=mypassword" />
</connectionstring>

On the working system, the Server is an actual IP address, integrated security is set to true, and of course the uid/pwd is a bit different. But again, using a very similar connection string, we able to get to OUR database on the non-working system...

The error we're getting at runtime is:

Cannot open database "ASPNETDB" requested by the login. The login failed. Login failed for user 'sa'.

When we poke into the database from SQL Server Management Studio - on a remote machine - we can see our own database is there, but the ASPNETDB isnt there. Yet... I suspect it failing getting in to just create the db...

Any thoughts? Thanks for the help!!! -- Curt

Did you Attach the aspnet db to the server?

|||

Thanks for the reply, Lock. Good question - and the answer is no. I did that and it worked. I guess somehow I thought ASPNET would create the database if it wasnt there - after all, I swear when using 2005 Express, if I delete the mdf, it magically comes back. Perhaps not... Back on my bike though - many thanks. -- Curt

Problem creating an enumerator?

Hi,

I tried to create an enumerator using Vb.NET but it will give me an error in this statement:

<DtsForEachEnumerator(DisplayName = "LSParseEnumerator", Description = "Returns an Enumerator by separating items in a String by a certain character", UITypeName = FullyQualifiedTypeName, AssemblyName, Version = 1.00.000.00, Culture = Neutral, PublicKeyToken = "")> _

The error is in the Version = 1.00.000.00 and i have already tried to put it in between "".
Error Returned:
Error 1 Comma, ')', or a valid expression continuation expected. C:\Documents and Settings\Luis Sim?es\My Documents\Visual Studio 2005\Projects\LSParserEnumerator\LSParserEnumerator\LSParserEnumerator.vb 5 217 LSParserEnumerator

Your quoting is off a bit. UITypeName, Version, and Culture should look like this:

UITypeName="FullyQualifiedTypeName,AssemblyName", Version="1.00.000.00",Culture="Neutral"|||Jay i have already tried that to but the example i gave you is from the MSDN2 website so it should be good...

http://msdn2.microsoft.com/en-us/library/ms136120.aspx

The way you told me to use gives me the following errors:
DisplayName is not declared
Description is not declared
and so on for all variables....

The only method that works is

<DtsForEachEnumerator()> _

But this way i can't refer to UI and specify all the other options...

This should be easy i think...
Best Regards,
Luis Sim?es

|||Okay, so much for documentation. I'm still focusing on quoting. Usually those strings look like this:

“Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

Have you tried UITypeName="FullyQualifiedTypeName, AssemblyName, Version = 1.00.000.00, Culture = Neutral, PublicKeyToken =" ? Not sure what you'd do about that PublicKeyToken. Quote it? Leave it off?
|||

This is something really odd... can it be from visual studio 2005 express? bug or something?

When is insert the <DtsForEachEnumerator(...
it will tell me that i have all optional parameters like this:

New([Description As String], [DisplayName as String], [ForEachEnumeratorContact As String], [LocalizationType As Type], [UITypeName as String]) Initializes a new instance of Microsoft.SqlServer.DTS.Runtime.DtsForEachEnumeratorAttribute

But i have already tried using just types in that order like this:

<DtsForEachEnumerator("MyEnumerator", "A managed enumerator", "Name of company to contact", , "WorldVision.LuisSimoes.SQLServer2005.Enumerators.LSParseEnumeratorUI, LSParseEnumerator")>

But the following error occurs:
Error 1 Too many arguments to 'Public Sub New()'

This is odd since it gives the parameters and then it shows an empty constructor?

|||Ok problem solved :)

<DtsForEachEnumerator(DisplayName:="For Each String Token Enumerator", Description:="Enumerates string tokens split by a certain separator character", UITypeName:="WorldVision.LuisSimoes.SQLServer2005.Enumerators.LSTokenEnumeratorUI,LSTokenEnumerator,Version=1.0.0.0,Culture=Neutral")> _

Cheers

Problem Creating a subscription

I am trying to publish / subscribe a database onto SQL Server Mobile and when I run the following code that was, for the most part, generated by the SQL Server Management Studio Subscription Wizard,

repl.InternetUrl=@."file://chiefjoseph/walklisttransfersite/sqlcesa30.dll";

repl.InternetLogin = @."rr";

repl.InternetPassword = @."88";

repl.Publisher = @."CHIEFJOSEPH\SQLSERVER2005";

repl.PublisherDatabase = @."WalkList";

repl.PublisherSecurityMode = SecurityType.DBAuthentication;

repl.PublisherLogin = @."sx";

repl.PublisherPassword = @."xxx";

repl.Publication = @."WalkList";

repl.Subscriber = @."WalkListSubscription";

repl.SubscriberConnectionString = @."Data Source=""\My Documents\SQLServer\NewWalkList.sdf"";Password=""pwd"";Max Database Size=32;Default Lock Escalation =100;";

try

{

// repl.AddSubscription(AddOption.ExistingDatabase);

repl.AddSubscription(AddOption.ExistingDatabase);

repl.Synchronize();

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.ToString());

}

I get the following error on the Pocket PC emulator

“A native exception has occurred in WalkLister.exe.Select Quit and then restart this program, or select Details for more information.”

When I try to use the subscription wizard to create the subscription, I get the following error:

Synchronizing Data (Error)

Messages

· Connection broken.
HRESULT 0x80004005 (0)

· The operation could not be completed.

Any insight to what the problem might be?

-Rob

I see a couple of odd things in your replication properties -

1. repl.InternetUrl=@.file://chiefjoseph/walklisttransfersite/sqlcesa30.dll

should be repl.InternetUrl = "http://chiefjoseph/walklisttransfersite/sqlcesa30.dll ";

2. the subscriber connection string should be

repl.SubscriberConnectionString = @."Data Source='\My Documents\SQLServer\NewWalkList.sdf';Password='pwd';Max Database Size=32;Default Lock Escalation =100";

I would recommend you start by using anonymous authentication (be sure to grant account IUSR_chiefjoseph permissions on your publication and the Walklist database if you do so) and then add SSL later. NT and DB authentication are a poor choice for merge replication because the userid and password are sent in the clear to the server.

-Darren

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if(not(is_srvrolemember('securityadmin')= 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if (not (is_srvrolemember('securityadmin') = 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins

problem creating a sqldatasource connection

I am able to connect but when I try to use the advanced sql generation options the two check boxes are non enabled (generate insert, update, and delete statements

use optimistic concurrency

what is happening the user id has permissions to update/delete/select from the selected table

Are you using a SQL statement that has a JOIN in it? The sql generator does not work with joined tables.

Problem creating a secondary datafile on drive E:\

Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
programming. I created a database, tsqldb, on C:\, where the ms sql server
files reside. Now, I used the alter database command to add a secondary
datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
want to put this secondary datafile file, E:\. the alter database command
goes like this:
alter database tsqldb
add file
( name = tsqldb_data2,
filename = 'E:\tsqldb_data2.ndf',
size = 50mb,
maxsize = 150mb,
filegrowth = 25%
)
and this is the error I get:
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
Server: Msg 5009, Level 16, State 1, Line 1
ALTER DATABASE failed. Some disk names listed in the statement were not
found. Check that the names exist and are spelled correctly before rerunning
the statement.
Extending database by 50.00 MB on disk 'tsqldb_data2'.
Can someone help me? Thanks.
DominickCheck whether the service account that runs the SQL Server instance has the
permission to create files in E:\.
Linchi
"Dominick D." wrote:
> Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> programming. I created a database, tsqldb, on C:\, where the ms sql server
> files reside. Now, I used the alter database command to add a secondary
> datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> want to put this secondary datafile file, E:\. the alter database command
> goes like this:
> alter database tsqldb
> add file
> ( name = tsqldb_data2,
> filename = 'E:\tsqldb_data2.ndf',
> size = 50mb,
> maxsize = 150mb,
> filegrowth = 25%
> )
> and this is the error I get:
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> Server: Msg 5009, Level 16, State 1, Line 1
> ALTER DATABASE failed. Some disk names listed in the statement were not
> found. Check that the names exist and are spelled correctly before rerunning
> the statement.
> Extending database by 50.00 MB on disk 'tsqldb_data2'.
> Can someone help me? Thanks.
> Dominick|||How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> > programming. I created a database, tsqldb, on C:\, where the ms sql server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> > want to put this secondary datafile file, E:\. the alter database command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.) while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Using Windows Explorer, right click on the drive or folder and bring up its
properties. It's under the Security tab.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
How do I check that? I"m a newbie to SQL, file permissions, so I'd
appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has
> the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > and
> > programming. I created a database, tsqldb, on C:\, where the ms sql
> > server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > I
> > want to put this secondary datafile file, E:\. the alter database
> > command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.)
> > while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before
> > rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Talk to the person who administers the machine in question. They should be able to check what
service account the service is using and see whether that account has permissions to create files in
the folder in question.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>> Check whether the service account that runs the SQL Server instance has the
>> permission to create files in E:\.
>> Linchi
>> "Dominick D." wrote:
>> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
>> > programming. I created a database, tsqldb, on C:\, where the ms sql server
>> > files reside. Now, I used the alter database command to add a secondary
>> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
>> > want to put this secondary datafile file, E:\. the alter database command
>> > goes like this:
>> >
>> > alter database tsqldb
>> > add file
>> > ( name = tsqldb_data2,
>> > filename = 'E:\tsqldb_data2.ndf',
>> > size = 50mb,
>> > maxsize = 150mb,
>> > filegrowth = 25%
>> > )
>> >
>> > and this is the error I get:
>> >
>> > Server: Msg 5123, Level 16, State 1, Line 1
>> > CREATE FILE encountered operating system error 5(Access is denied.) while
>> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
>> > Server: Msg 5009, Level 16, State 1, Line 1
>> >
>> > ALTER DATABASE failed. Some disk names listed in the statement were not
>> > found. Check that the names exist and are spelled correctly before rerunning
>> > the statement.
>> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
>> >
>> > Can someone help me? Thanks.
>> >
>> > Dominick|||OK, I managed to do a little research on E:\ and I included the service
account that I set up to have full control over E:\ and it did the trick.
"Tom Moreau" wrote:
> Using Windows Explorer, right click on the drive or folder and bring up its
> properties. It's under the Security tab.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
> news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd
> appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
> > Check whether the service account that runs the SQL Server instance has
> > the
> > permission to create files in E:\.
> >
> > Linchi
> >
> > "Dominick D." wrote:
> >
> > > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > > and
> > > programming. I created a database, tsqldb, on C:\, where the ms sql
> > > server
> > > files reside. Now, I used the alter database command to add a secondary
> > > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > > I
> > > want to put this secondary datafile file, E:\. the alter database
> > > command
> > > goes like this:
> > >
> > > alter database tsqldb
> > > add file
> > > ( name = tsqldb_data2,
> > > filename = 'E:\tsqldb_data2.ndf',
> > > size = 50mb,
> > > maxsize = 150mb,
> > > filegrowth = 25%
> > > )
> > >
> > > and this is the error I get:
> > >
> > > Server: Msg 5123, Level 16, State 1, Line 1
> > > CREATE FILE encountered operating system error 5(Access is denied.)
> > > while
> > > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > > Server: Msg 5009, Level 16, State 1, Line 1
> > >
> > > ALTER DATABASE failed. Some disk names listed in the statement were not
> > > found. Check that the names exist and are spelled correctly before
> > > rerunning
> > > the statement.
> > > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> > >
> > > Can someone help me? Thanks.
> > >
> > > Dominick
>

Problem creating a secondary datafile on drive E:\

Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
programming. I created a database, tsqldb, on C:\, where the ms sql server
files reside. Now, I used the alter database command to add a secondary
datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
want to put this secondary datafile file, E:\. the alter database command
goes like this:
alter database tsqldb
add file
( name = tsqldb_data2,
filename = 'E:\tsqldb_data2.ndf',
size = 50mb,
maxsize = 150mb,
filegrowth = 25%
)
and this is the error I get:
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
Server: Msg 5009, Level 16, State 1, Line 1
ALTER DATABASE failed. Some disk names listed in the statement were not
found. Check that the names exist and are spelled correctly before rerunning
the statement.
Extending database by 50.00 MB on disk 'tsqldb_data2'.
Can someone help me? Thanks.
DominickCheck whether the service account that runs the SQL Server instance has the
permission to create files in E:\.
Linchi
"Dominick D." wrote:

> Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> programming. I created a database, tsqldb, on C:\, where the ms sql server
> files reside. Now, I used the alter database command to add a secondary
> datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> want to put this secondary datafile file, E:\. the alter database command
> goes like this:
> alter database tsqldb
> add file
> ( name = tsqldb_data2,
> filename = 'E:\tsqldb_data2.ndf',
> size = 50mb,
> maxsize = 150mb,
> filegrowth = 25%
> )
> and this is the error I get:
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> Server: Msg 5009, Level 16, State 1, Line 1
> ALTER DATABASE failed. Some disk names listed in the statement were not
> found. Check that the names exist and are spelled correctly before rerunni
ng
> the statement.
> Extending database by 50.00 MB on disk 'tsqldb_data2'.
> Can someone help me? Thanks.
> Dominick|||How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciat
e
your assistance. Thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Check whether the service account that runs the SQL Server instance has th
e
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
>|||Using Windows Explorer, right click on the drive or folder and bring up its
properties. It's under the Security tab.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
How do I check that? I"m a newbie to SQL, file permissions, so I'd
appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Check whether the service account that runs the SQL Server instance has
> the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
>|||Talk to the person who administers the machine in question. They should be a
ble to check what
service account the service is using and see whether that account has permis
sions to create files in
the folder in question.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...[vbcol=seagreen]
> How do I check that? I"m a newbie to SQL, file permissions, so I'd appreci
ate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>|||OK, I managed to do a little research on E:\ and I included the service
account that I set up to have full control over E:\ and it did the trick.
"Tom Moreau" wrote:

> Using Windows Explorer, right click on the drive or folder and bring up it
s
> properties. It's under the Security tab.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
> news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd
> appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>
>

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)
Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegro ups.com...

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)Did you install Integration Services?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> When trying to create a maintenance plan using the wizard to backup the
> system databases in SQL 2005 the following error is displayed.
> How can I find what is causing this problem with a simple maintenance
> plan?
>
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "SystemDBs Backup" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "SystemDBs Backup" (Stopped)
>|||Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> > When trying to create a maintenance plan using the wizard to backup the
> > system databases in SQL 2005 the following error is displayed.
> >
> > How can I find what is causing this problem with a simple maintenance
> > plan?
> >
> >
> > Maintenance Plan Wizard Progress
> >
> > - Creating maintenance plan "SystemDBs Backup" (Error)
> > Messages
> > * Create maintenance plan failed.
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Create failed for JobStep 'Subplan'.
> > (Microsoft.SqlServer.MaintenancePlanTasks)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> >
> > --
> >
> > An exception occurred while executing a Transact-SQL statement or
> > batch. (Microsoft.SqlServer.ConnectionInfo)
> >
> > --
> >
> > The specified '@.subsystem' is invalid (valid values are returned by
> > sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> >
> >
> > - Adding tasks to the maintenance plan (Stopped)
> >
> > - Adding scheduling options (Stopped)
> >
> > - Adding reporting options (Stopped)
> >
> > - Saving maintenance plan "SystemDBs Backup" (Stopped)
> >|||I see... Well, to the best of my knowledge, you need SSIS to execute an MP (in 2005), since an MP is
an SSIS package.
I've heard that you won't need this with sp2, but sp2 is still only in CTP.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165355311.079516.289340@.j72g2000cwa.googlegroups.com...
> Integration Services are not installed on this server but other servers
> without it the plans can be created.
> Tibor Karaszi wrote:
>> Did you install Integration Services?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <robin9876@.hotmail.com> wrote in message
>> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
>> > When trying to create a maintenance plan using the wizard to backup the
>> > system databases in SQL 2005 the following error is displayed.
>> >
>> > How can I find what is causing this problem with a simple maintenance
>> > plan?
>> >
>> >
>> > Maintenance Plan Wizard Progress
>> >
>> > - Creating maintenance plan "SystemDBs Backup" (Error)
>> > Messages
>> > * Create maintenance plan failed.
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Create failed for JobStep 'Subplan'.
>> > (Microsoft.SqlServer.MaintenancePlanTasks)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> >
>> > --
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch. (Microsoft.SqlServer.ConnectionInfo)
>> >
>> > --
>> >
>> > The specified '@.subsystem' is invalid (valid values are returned by
>> > sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> >
>> >
>> > - Adding tasks to the maintenance plan (Stopped)
>> >
>> > - Adding scheduling options (Stopped)
>> >
>> > - Adding reporting options (Stopped)
>> >
>> > - Saving maintenance plan "SystemDBs Backup" (Stopped)
>> >
>

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)Did you install Integration Services?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> When trying to create a maintenance plan using the wizard to backup the
> system databases in SQL 2005 the following error is displayed.
> How can I find what is causing this problem with a simple maintenance
> plan?
>
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "SystemDBs Backup" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
>
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "SystemDBs Backup" (Stopped)
>|||Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...|||I see... Well, to the best of my knowledge, you need SSIS to execute an MP (
in 2005), since an MP is
an SSIS package.
I've heard that you won't need this with sp2, but sp2 is still only in CTP.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165355311.079516.289340@.j72g2000cwa.googlegroups.com...
> Integration Services are not installed on this server but other servers
> without it the plans can be created.
> Tibor Karaszi wrote:
>

Problem creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

You should probably post to the agent forum since this is an agent problem not an SSIS problem.

Thanks,
Matt

Problem creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

Whatever the problem was, restarting the server fixed it. I hadn't restarted the server since I installed SQL2005, because the install didn't require a reboot. I had a chance to do so over the weekend and when I checked on this problem, it was gone.

All is good now.

Thanks!

Jarret

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem creating a Foreign key Constraint

Hello, I'm having some problems trying to create this foreign key constraint:

ALTER TABLE dbo.t2_demaclie
ADD CONSTRAINT FK03_T2_DEMACLIE FOREIGN KEY (dclPerfilCompania, dclOrdenPedido)
REFERENCES DBO.T2_PEDIDOCLIENTE (cdPerfilCompania, nmOrdenPedido)

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint 'FK03_T2_DEMACLIE'. The conflict occurred in database 'Comfruta_dllo', table 't2_pedidoCliente'.

I'm sure there's no other constraint with the same name, and there's no othe one with the same columns...

Thanks a lot !!

Sounds like you have rows in t2_demaclie which don't match rows in t2_pedidocliente.

Check this first and see how you go. Something like this should do the trick (should give you zero rows):

select *
from t2_demaclie d
where not exists (select * from t2_pedidocliente p where p.cdPerfilCompania = d.dclPerfilCompania and p.nmOrdenPedido = d.dclOrdenPedido)

Rob|||Very useful ... Thank you so much !|||:) No problem.

Problem counting records

Hi,

I am struggling with a simple query, but I just don't see it.
I have the following example table.

Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0

The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.

What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.

ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0

Any assistance would be greatly appreciated.Did you think of this:

select t1.ID, t1.Subject, count(1) as Num_replies_to
from tbl t1
left join tbl t2
on t2.Reply_to=t1.ID
where t1.Reply_to=0
group by t1.ID, t1.Subject

Bye, Manfred|||What I would like to have returned is this: a list of messages where

Quote:

Originally Posted by

Reply_to = 0 and the number of replies to this message.

A subquery like the example below is one method.

SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@.netscape.netwrote in message
news:474fe374$0$22307$ba620e4c@.news.skynet.be...

Quote:

Originally Posted by

Hi,
>
I am struggling with a simple query, but I just don't see it.
I have the following example table.
>
Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0
>
The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.
>
What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.
>
ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0
>
Any assistance would be greatly appreciated.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I would like to have returned is this: a list of messages where
>Reply_to = 0 and the number of replies to this message.

>
A subquery like the example below is one method.
>
SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0
>

I knew it was simple. It had to be simple. I just didn't see it.
Many thanks to both Dan and Manfred.

Cheers.

problem counting multiple occurrances of a pair of entries

Hi Everyone,

I was having problems writing up a query to do the following:

I have a table with columns A B and C

I needed to count all occurrances of unique (A,B) tuples in the table

For Eg:

A B C
foo foo ZOO
foo foo BCV
xoo cdv rdf
foo foo ert
xoo cdv see
red gre

i needed an output

foo foo 3
xoo cdv 1SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
HAVING COUNT(*) > 1

problem counting multiple occurrances of a pair of entries

Apologies for the previous post - I hit the wrong mouse button

I was having problems writing up this query

I had a table with columns A B C i needed to write up a query that would count all occurrances of unique A,B pair entries in the table

for eg:

A B C
red pink x
red pink y
green blue z
red pink a
green yello b
green blue c

The query should return

red pink 3
green blue 2
green yello 1

I hope that helps... any help would be appreciated

Thanks in advanceOK...really simple now...just cut and paste in to QA

USE Northwind
GO

CREATE TABLE myTable99 (A varchar(10),B varchar(10),C varchar(10))
GO

INSERT INTO myTable99 (A,B,C)
SELECT 'red', 'pink', 'x' UNION ALL
SELECT 'red', 'pink', 'y' UNION ALL
SELECT 'green','blue', 'z' UNION ALL
SELECT 'red', 'pink', 'a' UNION ALL
SELECT 'green','yello', 'b' UNION ALL
SELECT 'green','blue', 'c'

SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
GO

DROP TABLE myTable99
GO

Problem correctly setting a local_variable

Hi,

I've been trying to write a stored proc to no avail. I'm trying to set a local variable and every time I try to execute the stored proc I get the following error:

Running [dbo].[StoredProcedure2].

Conversion failed when converting the nvarchar value 'TEST2' to data type int.

No rows affected.

(0 row(s) returned)

@.RETURN_VALUE =

Finished running [dbo].[StoredProcedure2].

Here's the code (which I skimmed down to try and pinpoint where the issue was).

Initially, I was trying to assign a numeric string value stored as nvarchar(10) and anytime the value would be over '2147999999' (in that area) it would bomb... Now even this simple code won't work and I can't understand why. If anyone would be kind enough to enlighten me ;-)

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

RETURN @.ME

u must use Print/Select instead of Return.

fromBOL

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Syntax

RETURN [ integer_expression ]

alter PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

select @.me

Madhu

|||

The value following the RETURN keyword MUST be an integer value.

Strings are not allowable RETURN values.

Instead use:

Code Snippet

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

SELECT @.Me

GO

|||

It always better to use OUTPUT parameter, or Select statement to get the single row & column data from the SP. Return only supports integer.

NOTE:

PRINT – you can’t get the result on the UI. It will get suppressed.

Problem copying tables to another server with Primary and Foreign Key constrains

How can I copy a table with a Primary Key to another server's table of
the same name without receiving the following error: (Error at
Destination for Row number 489. Errors encountered so far in this
task:1.The statement has been terminated. Violation of PRIMARY KEY
constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
'INDIVIDUALS'.)
I want to copy about 20 interrelated cascaded tables with Primary and
Foreign Keys to another server's tables with the identical structure
and not receive the above error. I suggested dropping the tables and
recreating them, but my co-worker feel that this would not work because
of the interrelationship of the tables.
I tried to manually delete a table that is part of this group and
received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
it is referenced by a FOREIGN KEY constraint.
THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
REPLICATED.
Any Suggestion?Some odd questions:
What field is the primary key? Is it the replication id? Is it an Identity
Field ? Some other field?
Instead of deleteing the target table you could try truncating it.
Your error says you are trying to load a duplicate key. The only answers are
Delete, Truncate, Drop or Remove Constraint.
What is the problem you are trying to solve? If it is just having a copy of
the database 'somewhere else', you could consider a backup and restore cycle.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"war_wheelan@.yahoo.com" wrote:
> How can I copy a table with a Primary Key to another server's table of
> the same name without receiving the following error: (Error at
> Destination for Row number 489. Errors encountered so far in this
> task:1.The statement has been terminated. Violation of PRIMARY KEY
> constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
> 'INDIVIDUALS'.)
> I want to copy about 20 interrelated cascaded tables with Primary and
> Foreign Keys to another server's tables with the identical structure
> and not receive the above error. I suggested dropping the tables and
> recreating them, but my co-worker feel that this would not work because
> of the interrelationship of the tables.
> I tried to manually delete a table that is part of this group and
> received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
> it is referenced by a FOREIGN KEY constraint.
> THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
> WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
> REPLICATED.
> Any Suggestion?
>|||What field is the primary key? Is it the replication id? Is it an
Identity Field ? Some other field? THE PRIMARY KEY VARIES FROM TABLE
TO TABLE.
Instead of deleteing the target table you could try truncating it. I
AM NOT A T-SQL PROGRAMMER SO IN NON PROGRAMMING TERMS - CAN I DROP THE
KEYS ON THE DESTINATION TABLE(S) I.E. PRIMARY AND FOREIGN KEYS THEN
COPY THE NEW TABLES TO THE DESTINATION? WOULD THIS RE-ESTABLISH THE
ORIGINAL PRIMARY AND FOREIGN KEYS WITHOUT CORRUPTING THE
DATABASE/TABLES AND THEIR RELATIONSHIPS?
Your error says you are trying to load a duplicate key. The only
answers are Delete, Truncate, Drop or Remove Constraint. SAME QUESTION
AS ABOVE, BUT PERHAPS THE REMOVE CONTRAINT OPTION WOULD WORK. NEW
QUESTION: WOULD SQL LET ME REMOVE A CONTRAINT WITH INTER-RELATIONSHIPS
TO OTHER TABLES OR WOULD IT FAIL BECAUSE OF THE INTER-RELATIONSHIPS.
What is the problem you are trying to solve? If it is just having a
copy of the database 'somewhere else', you could consider a backup and
restore cycle. THE PROLEM IS THAT THESE TABLES WOULD BE REPLICATE IF
REPLICATION WERE WORKING. REPLICATION HAS NOT BEEN WORKING FOR ABOUT A
MONTH SO THE PRIMARY AND SECONDARY DATABASES ARE OUT OF SYNC. IF THE
PRIMARY SERVER GOES DOWN WE WOULD BE MISSING A MONTHS WORTH OF CHANGES.
ERGO BACK TO MY ORIGINAL QUESTION (I want to copy about 20
interrelated cascaded tables with Primary and Foreign Keys to another
server's tables with the identical structure and not receive the above
error.
Thanks for you response. I am trying to get suggestions for my PART
TIME T-SQL programmer.

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?
berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem copying SQL DB file

Hi,

I have a VB project which uses SQL Server 2005 Express. When the project first runs, it copies its DB files from the data directory to a directory created on the C: drive. This works fine.

In a later part of the application, it is necessary to copy the DB file to another location e.g. memory stick so that it can be used to merge data onto another PC running the same application.

The data merging works too.

My problem is, despite the fact that it worked okay this morning, I am now unable to copy the DB into a second location (memory stick or location on local disc). I get an exception stating that the file is in use.

I have no other applications running (I shut down Visual Studio and was running the installed version).

What is more, the form that implements the file copying, first closes down all other forms in the application to ensure that nothing is connected to the DB.

Furthermore, the form that performs this task has no DB connections whatsoever - it simply allows the user to select a location, then creates a directory and copies the DB files to it.

If I copy/paste the code plus the FolderBrowserDialog into the first form that copies the original database out of the data directory - it works!

The code for the whole form is below - I would be hugely grateful for any ideas ...

Public Class Export

Private Sub Export_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub NextBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBtn.Click

'Close all other windows to ensure that the databases are not in use.

Dim Counter As Integer = My.Application.OpenForms.Count

Do While Counter > 1

For Each f As Form In My.Application.OpenForms

If Not (f.GetType Is GetType(Export)) Then

f.Close()

Exit For

End If

Next

Counter -= 1

Loop

' Set the Help text description for the FolderBrowserDialog.

Me.FolderBrowserDialog.Description = _

"Select a location to export the data to e.g. a memory stick. Or, you can export to a local directory and then burn to CD or e-mail to another user."

' Do not allow the user to create New files via the FolderBrowserDialog.

Me.FolderBrowserDialog.ShowNewFolderButton = False

' Default to the My Documents folder.

Me.FolderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer

Dim result As DialogResult = FolderBrowserDialog.ShowDialog()

Dim foldername As String

If result = DialogResult.OK Then

foldername = FolderBrowserDialog.SelectedPath

Try

If Not My.Computer.FileSystem.DirectoryExists(foldername & "\ClubSecExportMerge") Then

My.Computer.FileSystem.CreateDirectory(foldername & "\ClubSecExportMerge")

MsgBox("Merge Directory created.")

End If

My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB.mdf", foldername & "\ClubsecExportMerge\MergeDB.mdf", True)

My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB_log.ldf", foldername & "\ClubsecExportMerge\MergeDB_log.ldf", True)

MsgBox("Database merge files successfully copied to " & foldername & " \ClubSecExportMerge.", MsgBoxStyle.OkOnly, "Operation successful")

Catch ex As Exception

MsgBox("There was an error." & Chr(13) & "Export of database was not achieved." & Chr(13) & ex.ToString, MsgBoxStyle.OkOnly, "Operation failed.")

Exit Sub

End Try

Else ' Cancel button pressed

MsgBox("No path selected. Data not exported", MsgBoxStyle.OkOnly, "Operation cancelled.")

End If

Dim home As New HomeScreen

home.Show()

Me.Close()

End Sub

End Class

Accroding ot the mdf and ldf file your are using a SQL Server instance for storing some data. are the mdf files attached to any SQL Server ? If yes, is it a user instance or a server instance. If you don′t know post your connection string of your application here. The file is probably in use by any SQL Server instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

Thanks for the response. I don't know much about user instances but I thought they defaulted to False. When I looked at the connection strings, they are true. So I changed them to false. The app ran but I still couldn't copy the files to a new directory.

I noticed that in the Database Explorer, the database filename shown was different to what was intended and referred back to the project, within my Visual Studio directory. I changed this to point at the database files on the C: drive and now nothing works.

I get the first exception below when I try to run the app. Yesterday, most of it worked! I could even get the file copying to work if I pasted the code into a different form. Now nothing works!

The current connection string is as follows:

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\ClubSec\ClubSecDB.mdf;Integrated Security=True;User Instance=False

Just by way of information, I will explain a little more about what I am trying to achieve. I'm new to VB and SQL Express and only know the stuff learned from the online videos and a book. I created the application without worrying about connection strings, user instances etc as they were all handled by the IDE. However, I then ran into a problem - if I update the app, the database files will overwrite the user data in the data directory. Seriously bad news!

Rather than try to be too clever, I thought ok, all I need do is chek whether a directory called C:\ClubSec exists on app startup. If it doesn't, it is the first ever run - create the directory and copy the DB files to it from the data directory and use these files in future. That way, any app updates won't affect the user data. This all semed to work fine.

I now wanted to add an Export/Import data feature as different users of the app could be changing data and there would be a requirement to merge it. To achieve this, the user is allowed to select a directory on any drive (intended to be a memory stick in real life) at which point their DB files are copied to a directory called ExportMerge in the chosen location. This worked yesterday and will work if I paste the code into a different form other than the one it's in!

Next, for an import, the ExportMerge directory is located and the contents copied to the Import Merge directory within Clubsec. This works fine and the merge happens (except for a few logic bugs that I've been too busy to address yet!)

Excuse me while a go and find a convenient wall upon which to bang my head several times!

UPDATE:

I have read that ADO.net maintains open connections sometimes which are eventually discarded. I have discovered that if I close all forms with data access then use a timer to try a copy my directories every so often, after 5-6 minutes they are freed up and I can perform the copy. I also tried to disable pooling but this had no effect although I can't be sure that I successfully disabled it because any time I change any connection properties - they change back again!

Regards,

Rich

First message

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=65536
Message="Unable to open the physical file "C:\Clubsec\ClubSecDB.mdf". Operating system error 32: "32(error not found)".
Unable to open the physical file "C:\Clubsec\ClubSecDB_log.ldf". Operating system error 32: "32(error not found)".
Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'.
File activation failure. The physical file name "C:\Clubsec\ClubSecDB_log.ldf" may be incorrect."
Number=5120
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=101
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Then, as an experiment, tried to close the connection in Database explorer and got this one:

System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'."
Number=4064
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()