April 20, 2009 at 2:36 pm
SORRY - PLEASE DISREGARD - AS I WAS TYPING THIS I FIGURED IT OUT, THEN HIT "POST" SOMEHOW ANYWAY
Hi - I'm modifying an app and am running into a problem. The app currently points to 2 SQL 2000 databases which reside on the same server ("all-2000 scenario"). Because of a looming software upgrade, I'm changing the app to point to one SQL 2000 database (same as before) and one SQL 2005 database on a different server ("split-scenario"). The code below works fine in the all-2000 scenario, but when I run it in the split-scenario, I get this error message:
Could not locate entry in sysdatabases for database 'TRACKIT8_DATA'. No entry found with that name. Make sure that the name is entered correctly
TRACKIT8_DATA is the name of the database on the 2005 box. Here's my code w/comments:
SqlCommand addTIUserCommand = new SqlCommand();
SqlParameter UserParam = null;
addTIUserCommand.CommandType = CommandType.StoredProcedure;
//ConfigurationManager.AppSettings["TrackitDB"].ToString() = TRACKIT8_DATA
addTIUserCommand.CommandText = ConfigurationManager.AppSettings["TrackitDB"].ToString() + ".dbo.rems_AddTIUser";
UserParam = new SqlParameter("@USERID", SqlDbType.Int);
UserParam.Direction = ParameterDirection.Output;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@FULLNAME", SqlDbType.NVarChar, 65);
UserParam.Value = FullName;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@TITLE", SqlDbType.NVarChar, 30);
UserParam.Value = EmployeeInformation["Title"].ToString().ToUpper();
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@PHONE", SqlDbType.NVarChar, 15);
UserParam.Value = string.Empty;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@LOCATION", SqlDbType.NVarChar, 10);
UserParam.Value = Location;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@DEPT", SqlDbType.NVarChar, 30);
UserParam.Value = DEPT;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@DEPT_NUM", SqlDbType.NVarChar, 30);
UserParam.Direction = ParameterDirection.Output;
addTIUserCommand.Parameters.Add(UserParam);
UserParam = new SqlParameter("@EMPLOYEE_ID", SqlDbType.NVarChar, 6);
UserParam.Value = EmployeeID;
addTIUserCommand.Parameters.Add(UserParam);
//ConfigurationManager.AppSettings["DSN_REMS"] = REMS (SQL 2000 database)
string DBString = ConfigurationManager.AppSettings["DSN_REMS"].ToString();
using (SqlConnection conn = new SqlConnection(DBString))
{
SqlTransaction tran = null;
tran = conn.BeginTransaction();
try
{
conn.Open();
addTIUserCommand.Connection = conn;
addTIUserCommand.Transaction = tran;
addTIUserCommand.ExecuteNonQuery();
tran.Commit();
conn.Close();
}
}
The stored procedure rems_addTIUser runs fine from within SQL Server Management Studio.
April 20, 2009 at 2:57 pm
Since you did manage to "accidently" post your question, it would also be nice if you could post your resolution as well. Someone else may have a similar problem and this may help them.
April 20, 2009 at 3:17 pm
Quite correct! I always assume that my goofs are going to be glaringly obvious to everybody but me, but just in case:
I'm setting ConfigurationManager.AppSettings["TrackitDB"].ToString() = TRACKIT8_DATA (which is on the SQL 2005 server)
However, all of this is inside a using block that uses the SQL 2000 server (because I'm doing more stuff than I'm showing here, bouncing between servers with abandon as if I actually knew what I was doing). SO... my ConfigurationManager.AppSettings["TrackitDB] setting should've specified the server the database is on, not just the database name:
ConfigurationManager.AppSettings["TrackitDB"].ToString() = NYDEV305.TRACKIT8_DATA
Now it works fine, ta da.
Sometimes it's helpful just to put it all down for someone else to look at. Now if I could just get that write first, post second thing down, I'd be doing okay. :blush:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply