Problem reattaching database

I am writing a program in vb6 to move a database and its log file from one
directory to another using sp_attach_db and sp_detach_db.
I used 'sp_detach_db mydb' to detach the database, moved it to the new
directory location and then tried to attach the database using the command:
'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
it; however I get the following error message.
"
Run-time error '-2147217900 (80040e14)':
The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
files. A database can have only one primary file.
"
Am I missing a parameter in sp_attach_db?
Is there another step that I am missing that needs to be done before I
attach it again?> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
I would guess that you have a bug in your code and accidentally copied the
primary data file (mdf) to both the mdf and ldf file names.
Hope this helps.
Dan Guzman
SQL Server MVP
"iScanTeam@.community.nospam"
<iScanTeamcommunitynospam@.discussions.microsoft.com> wrote in message
news:164CA596-73DD-46F5-9590-6EED155B5AF9@.microsoft.com...
>I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the
> command:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
> "
> Run-time error '-2147217900 (80040e14)':
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>|||My man! Check to make sure your specified log and data files are indeed in
that directory. Otherwise, try something like this.
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'c:\path\mydb_log.ldf',
@.filename2 = N'c:\path\mydb.mdf'
"iScanTeam@.community.nospam" wrote:

> I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the command
:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
> "
> Run-time error '-2147217900 (80040e14)':
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem inserting date

Hello

The date format in SQL Server 2000 is dd-MM-yyyy. I am writing the following code in the buttons click event. I am using a textbox and button.

Dim conn as SqlConnection=new SqlConnection(connection string)

Dim ins as string="insert into Sample(dval) values(' " & TextBox1.Text & " ')"

Dim cmd as SQlCommand=new SqlCommand(sel,conn)

conn.open()

cmd.ExecuteNonQuerry()

conn.close()

When i am inserting the date 05-03-2007 in the textbox and clicking the button it is inserting date

03-05-2007 rather than 05-03-2007. What changes should i make in the code?

Rathish

Hi,

before insert, convert the date to yyyymmdd, with this no matter the config of your SQL

|||Hello How to convert date to yyyy-mm-dd.Rathish

problem inserting in database

i have a page writing to a dsatabase. this is the string

<code>

cmd = new sqlcommand("insert into company_locations (company_id, location_type, location_name, contact, address, suite, address2, city, state, zip, phone, extension, fax, comments) Values ('" & provider_id & "', '" & location_type.selectedvalue & "', '" & location_name.text & "', '" & contact.text & "', '" & address.text & "', '" & suite.text & "', '" & address2.text & "', '" & city.text & "', '" & states.selecteditem.value & "', '" & zip.text & "', '" & phone.text & "', '" & extension.text & "', '" & fax.text & "', '" & comments.text & "')", xDb_Conn_Str)

</code>

the problem is that in location_name, I am allowing ' as a valid character. Can anyone help me with this. everytime i execute my statement is truncated because of '.

thanks

Your suffering from a problem that is only slightly less serious than the one that you will face in production: your code is liable to SQL injection attacks, assuming that the input is coming from the user.

Consider what would happen if the entry for, say, contact.text contained:

joe' ); DROP TABLE company_locations; --

Given the right level of privilege, your company_locations table would disappear. Note that this is probably the least of your worries - hackers would start attempting to run command line code on you SQL Server via the built in stored procedure for executing shell code.

You should use parameterised queries to overcome both your problem and to help mitigate against SQL injection attacks. Thus you should be using

... Values( @.ProviderId, @.LocationType, etc. )

for the command text, and then add SqlParameter objects for each parameter

eg

cmd.Parameters.Add( "@.ProviderId", provider_id )

(Note that there are lots of overloads for creating parameters, or you can create the SqlParameter object separately and then add it to the Parameters collection).|||I had understood the problem with sql injectioons. I had already implemented validation string to ensure that certain characters would not be allowed for those exact reason. It was just the ' that I was needing to add. Thanks for the advice you gave me though, it will help. I did not realize that by passing my text as a parameter that I could include the apostrophe. Thanks for the help.|||

msamford wrote:

I had already implemented validation string to ensure thatcertain characters would not be allowed for those exact reason.

This approach (filtering out bad input) is called "blacklisting" and isnot at all recommended. "Whitelisting" (letting in only expectedinput) is much preferred. But parameters are the mostpreferredapproach of all -- you should always use parameters.