How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
I'm already discussing this with you here
http://forums.asp.net/thread/1299924.aspx
sqlHi,everyone!
I'm new to this forum and also new to Visual Studio 2005 .NET.
I'm going to develop a pocket pc(windows mobile 2005) application which needs to save some data introduced by the user.Later that data should be syncronized with MS SQL Server database.
The application is going to be developed using Visual Studio 2005 with c#.
So I'm doubting wich is the best way of syncronising the data between Pocket Pc and a Desktop PC.Should i use MS SQL Server Mobile(RDA,Merge Replication) or XML(Xml Web Services) would help me better?Or is there any other better way of data syncronization/storage?
Could anyone help me,or post some links + code samples,please?
Thanks!
You'll need to provide more information ... how much data? What sort of data? Will it need to be wrapped in a transaction and/or have other potential for rollback? How critical is it/how reliable does the synchronization need to be? Have you looked into the Service Broker? (I'm exploring this for a project myself.)
Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
Hi,
The only thing I can think of is the cells in excel with your decimal numbers is formated 1. to display 2 decimal places only, whilst the value is much more precise 2. (that's guess) cell is formated as percentage, so after convesion there is decimal point moved 2 places left ....
Hope at least one of these is true
Tomek
|||I changed the Format from Percentage to general but it still doesnt work.|||
So you've changed cell formating to general it shows in spreadsheet 0.76 and after transfer you get 0.00762711864406778
What db type has destination column, do you use vb(or any other language) or dts to transfer it?
Try to transfer it to flat file and then see which side cause problem.
Tomek
|||I am using a oledb connection to connect to the excel spreadsheet and then using a sqlbulkcopy to copy it to sql server. Have changed the dbase field from varchar to money and when it was varchar i get 0.007627118464406778 in the db column and when i changed to money it is been stores as 0.0076
Regards
Karen
|||This is strange, you changed the cell format from percentage to general and the cells still display 0.76? That seems wrong to me. When you change to general, it should change to .0076 right? So all we are left with as a mystery is why you only see .0076 and if you copy it over to SQL Server you see .0076.....otherstuff, right? Did you change it to "general" or "number"? Number and percentage have a box with "decimal places" in it, that specify how many decimal places to show, but general should show all of them up to the width of the box, the rest are truncated just like any other overflowing field. Is this what is happening? If you go to "number" and increase "decimal places" under formatting options to 10 or 50, does it show some of what SQL Server shows you?
If so, you should consider that Excel is different from SQL Server in that Excel is designed to be both front-end and database at the same time, so it understands the idea of truncating data on display implicitly while separately storing the truth. SQL Server does not implicitly do so, you would need to write a stored procedure or a query that explicitly rounds to see rounded numbers, so you might do 'SELECT round(column, 2.) from table' to get that rounding behavior that you desire. If you want a rounded table, meaning that the data itself is truncated, then you can write a quick t-sql statement to truncate the data in your table but that would affect the accuracy of any further calculations you do from that data.
Hope that helps,
John
|||
John,
Thanks for your answer, I am not doing any calculation for this field, it just gonna displayed in a report. so in the report i will multiply the field by 100 and that should give the right result.
Regards
Karen
Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
You probably have the columns in excel formatted as a percentage column.
Multiply by 100 and round to the nearest .01
|||the columns excel are in General, i have tried currency and number and text and they dont work
Regards
Karen
Hi i am trying to transfer the contents of a dbf file to sql server 2005 and this is my error message
'C:\Documents and Settings\Karen\My Documents\Visual Studio 2005\WebSites\ASC1\pdf\client\AS.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
This is my code
If (FlAS.PostedFile.FileName.ToLower.EndsWith(".dbf"))Then FlAS.PostedFile.SaveAs(location) Try'Connection string to a dbase file Dim dbfConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={0};Extended Properties= dBase IV", location) 'create connectionto the DBFfile Using connectionAs Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(dbfConnectionString) Dim commandAs Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select *from AS.DBF", connection) connection.Open()'Create a dbDatareader to the dbf file Using dr As Data.Common.DbDataReader = command.ExecuteReader() Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION) Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)) Dim query As String = "Truncate table ASDBF" myConnection.Open() Dim cmd As New SqlCommand(query, myConnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myConnection.Close() 'bulk copyof sql server Using BulkCopyAs SqlBulkCopy = New SqlBulkCopy(sqlConnectionString) BulkCopy.DestinationTableName = "ASDBF" BulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using Catch exAs Exception Throw exEnd TryEnd IfAny help will be appreciated.
Regards
Karen
What is the "location" variable? What is it's value? Where is it set?
|||location is set before the if Statement and its a string
location = Server.MapPath("pdf/client/" + Session("ClientName") + strAS)
|||Debug your code and verify that the string "location" looks as you would expect
|||yeah it does... but how would i know if i have to use a foxpro connection string or a dbase connection string and which version?
Regards
Karen
|||Where is your code failing? On file.saveas(...)?
|||no its failing atconnection.Open() ... cause i can see the file name in the location that i am saving it to.
|||
Using 'Tools' --> 'Connect to Database', try to create a connection to the .DBF file. I think that there is a problem with the connection string. Once you successfully connect from Visual Studio, you should be able to copy that connection string into your code.
|||I found this article.. Hopefully this will help you. Looks like you may need to leave the filename off of the Data Source (only include the folder name).. Take a look
http://www.msdner.com/forum/thread51916.html
|||thanks should i use this provider?
Microsoft Jet 4.0 OLE DB Provider or which one?
|||
Just a quick glance through the article, it looked like your connection string was ok, except for the filename on the end of the datasource.
|||i used the same connection string to a excel file but specified the extended properties as excel 8.0 and it works fine... but not sure why isnt it working for a dbf file
|||Thanks SSWanner for your answer... I got it working... I downloaded the fox pro oledb provider... and it kinda worked good it failed at the sql insertion (sql exception) and i am familiar with that error. The error i got is
Received an invalid column length from the bcp client for colid 1
I just need to change the column length in sql server..
Again Thanks a lot.
Regards
Karen