multiple-step ole db error

I found the following: http://support.microsoft.com/kb/269495/EN-US/ that relates to this error. Check it out and see if it applies to your problem.

Regards,

Mike Wachal
SQL Express team

MikeWachal-MSFT at 2007-9-8 >

Post the code that is causing this error and mark the palce that results in an error.

I found a post on another fourm that suggests the error you are seeing is fixed by using a System DSN rather than a User DSN, but I don't know what type of DSN you're using, so I couldn't say if this will solve your poblem or not.

Regards,

Mike Wachal
SQL Express team

MikeWachal-MSFT at 2007-9-8 >

I would still like to see the code you are running that is causing the error. If you're not willing to post the code, there isn't much else I can do.

Regards,

Mike Wachal
SQL Express team

MikeWachal-MSFT at 2007-9-8 >

I think we're getting closer here, but I have a couple more questions:

1. The final goal here is to copy information between two databases connected to the same SQL Server, right?
2. What is the literal path to the database after you copy it into a sub-directory of your VB app? (In other words, what does DbPath resolve to in your code?)
3. You list a number queries that you have run on both databases using SSMS. When you are running these queries, what is the literal path to the database files that you are running the queries against?
4. What is the Service Account that you are running SQL Express under? (You can check this in SQL Configuration Manager.

Thanks for the additional information.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

MikeWachal-MSFT at 2007-9-8 >

By Service Account, I mean the account that SQL Server is running as. This is set during installation and can be changed using SQL Configuration manager. Here's how to find out what your Service Account is:

1. Launch Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.
2. Switch to the SQL Server 2005 Services node in the program that opens.
3. In window on the right, find the SQL Express service and scroll across to see the Log On As column.

Let me know what account is listed. Once I have the service account, I should be able to set up a similar configuration on my system and test it out.

One more clarifying point, you plan to run this against the .\SQLEXPRESS instance once it's installed, right? I just wanted to make sure the default instance (MSSQLSERVER) is not the instance we're conserned with.

The paths you listed are enough information for what I need, so no worries about giving exact names.

Mike

MikeWachal-MSFT at 2007-9-8 >

I spent some time working on this today and I managed to get it to work. I've included a code sample a bit later in the message, but first the explaination.

The main issue here seems to be related to how your database gets named when you attach it using AttachDbFilename. If you don't specify a specific name using either Initial Catalog or Database in the connection string, SQL will automatically generate a name that is based on the file path. You get very different results when manually attaching a database in SSMS than you do from a connection string.

Another area I want to clarify is the User Instance=True switch that you are using. User Instances are a special instance of SQL Express that is created in the context of the User making the call at run time. It is completely separate from the main instances, which is named (local)\SQLEXPRESS in default installation. When you open SSMS and attach a database manually, you are attaching it to the main instance, not to the User Instance, and some behaviors are different. Check out the User Instance white paper to learn more about these special instances of SQL Express.

Finally, I'm not sure how you're acctually attaching the second database in your code since I haven't seen the whole listing. There are a couple different ways you can do this. In my sample, I've chosen to open a single connection to one of my databases, and then use a Command object to run sp_attach_single_file_db to attach the second database that I'm using. Once I've done that, I'm able to use a second Command object to run a simple 'SELECT * INTO..." query to copy data from one table to the second table.

To summarize, here is what I'm doing:

1. Opening a connection that specifies AttachDbFilename, User Instance and a database name to hook up to my first database.
2. Use a SqlCommand object to run sp_attach_single_file_db to attach my second database with my original connection so they are both hooked to my User Instance.
3. Run a command using SELECT * INTO... to copy a table from one database to the other.

This is in C# since that's what I have on my system, but the logic is pretty similar. Let me know if this doesn't make sense and I can convert it into VB.NET. I don't have VB 6 installed, but I would expect things to work in a similar fashion.

static void Main(string[] args)

{

// Use a connection string builder just to make it easier to see each
// value in the string. You can just pass a single string in your own code.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

// Connection string for Northwind

builder.DataSource = ".\\SQLEXPRESS";

// You need to pass a literal string in place of |DataDirectory| if the database file
// is not in the same directory as the application.
builder.AttachDBFilename = "|DataDirectory|\\NORTHWND.MDF";

builder.InitialCatalog = "Northwind";

builder.IntegratedSecurity = true;

builder.UserInstance = true;

// Create a connection to Northwind, attaching the file at the same time.

SqlConnection cnn = new SqlConnection();

cnn.ConnectionString = builder.ConnectionString;

cnn.Open();

// Attach the second database using the connection I already have.

using(SqlCommand cmdTarget = new SqlCommand("sp_attach_single_file_db", cnn))

{

cmdTarget.CommandType = CommandType.StoredProcedure;

cmdTarget.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = "Target";

cmdTarget.Parameters.Add("@physname", SqlDbType.NVarChar).Value = "C:\\Documents and Settings\\mikewa\\My Documents\\Visual Studio 2005\\Projects\\TwoDbOperations\\TwoDbOperations\\bin\\Debug\\Target.mdf";

cmdTarget.ExecuteNonQuery();

}

// New command object to actually transfer the data to the second database.

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

// run query

cmd.CommandText = "SELECT * INTO Target.dbo.NewTable5 FROM Shippers";

cmd.Connection = cnn;

int qReturn = new int();

qReturn = cmd.ExecuteNonQuery();

Console.WriteLine("Affected rows: {0}", qReturn.ToString());

// Close the connections

cnn.Close();

}

Hopefully this is clear enough to follow, let me know if it isn't. One final tip, download a tool named SSEUtil from http://www.microsoft.com/downloads/details.aspx?FamilyID=fa87e828-173f-472e-a85c-27ed01cf6b02&DisplayLang=en. This tool will help you look at the database attached to the User Instance on your computer. It's possible to do in SSMS with a couple queries, but SSEUtil does it with a single command line.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

MikeWachal-MSFT at 2007-9-8 >

I'll work this backwards:

Your statement about User Instances is true, but not totally accurate. The truth is a bit more complex and I'll try to give the short version here; I encourage you to read the article I referenced in my last message.

• We made a serious effort to limit the risk that you expose yourself to when you run SQL Express. One way we do this is that a default installation of SQL Express uses Network Service as it's services account. Network Service has limited permissions; it doesn't have access to things like a users profile directories (Read: My Documents).
• We also wanted to have a good story for "normal users" which we define as people who are not administrators on the computer. Certain operations in SQL, such as attaching a database, typically require permissions associated with an adminstrative user.
• We wanted to enable a XCopy type deployment in VS applications so you could just copy the .mdf/.ldf files around and attach them at run time and be able to install the applications without being an admin on the computer. (Opps, problem considering the previous issue about who can actually attach databases.) As a side point, non-administrators generally can't install into Program Files, so VS 2005 apps deployed by ClickOnce usually go to My Documents.

Put it all together, and we needed to come up with a way to run SQL Express that allowed a normal user to do stuff like attach a database and allowed SQL Server to access files in My Documents, where it doesn't, by default, have file access. Viola, our answer was User Instances. (Trivia - Another name for this is RANU, or Run As Normal User, get it?) I won't go into the details here; it's covered in the paper in more detail.

Net/Net - User Instances aren't "required" to use SQL Express with database files in the My Documents folder as long as the main instances of SQL Express has access to those folders. In your case, you're running SQL Express as Local Service, which does have access to those folders (hence your ability to attach to those databases in your earlier tests.) so you (and by this I mean YOU) don't really need User Instances. The problem arises when you deploy, it's no longer YOU, it's now your customers, and they may want to run SQL Express as Network Services, so they would need User Instance? (OK, that was longer than I expected.)

On to the connection string. My bad, managed code assumes the SQL Native provider, so I don't have it shown in my code. VB6 doesn't know squat about the SQL Native provider; as you've found you have to explicitly specify it using the Provider=SQLNCLI.1 statment in your connection string.

As far as the rest of the connection string, it all looks fine; I believe Database and Initial Catalog are functionally the same. Since there is a chance you will need User Instances to support your clients, I would try the following connection string:

strDbConn = "Provider=SQLNCLI.Q;Integrated Security=SSPI;" & _
"Persist Security Info=False;Database=" & strDbName & ";" & _
"AttachDBFileName=" & DbPath & ";Data Source=.\sqlexpress" & _
"User Instance=True"

I belive this will work. To confirm this, I would download SSEUtil (mentioned in an earlier post) and do the following:

1. Run some test code or single step you code to get to the point where you open the connection using the above connection string. Assuming it opens:
2. Open a command window to the directory where you have SSEUtil saved and run the command line "SSEUtil -L". This will list all the databases attached to your User Instance.
3. For good measure, also run the following command line "SSEUtil -main -L" which will list all the databases attached to the main instance of .\sqlexpress. At this point you would want to ensure that the database represented by CleandbName is not attached to either the main or User Instance of SQL Express. (The error you're getting seems to indicate that you've already got the database attached. If it might be attached, you might need to write some code to check for the database, and if it's attached, just skip the code to attach it.) Assuming it's not attached:
4. Step through your code to the point where you call the attach sp and see if it works.

One thing that I think might really help troubleshoot this is to take yourself completely out of your application code. Start everything from scratch, new project, new database files with different names. Hard code everything rather than concatinating variables. Troubleshooting always starts with simplification; get rid of everything that doesn't have to do with the problem you're working with because it's always the simple things that mess you up. If you're already doing some of this, great, I just want to make sure.

My last comment for this response is to ask if you're married to VB6 for this project. You probably have good reasons for using VB6, I'm not trying to talk you out of it; I'm just saying that baring a specific reason, there are somethings that are just easier to do when working between VS 2005 and SQL 2005. VB .NET 2005 Express Edition is free and has quite a bit of functionality. Just thinking out loud here.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

MikeWachal-MSFT at 2007-9-8 >