.sql file ..

hi,
i have a sql file that is exported from the other sql server.
how to import this sql file into my new sql server?
the sql file contains the structure/description of the procedures.
thank you.One method is to run the script from Query Analyzer. Connect to the desired
server and select File --> Open and navigate to your script file. Set the
database context to the desired database and execute.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<Blue> wrote in message news:eLh$G69kEHA.3520@.TK2MSFTNGP11.phx.gbl...
> hi,
> i have a sql file that is exported from the other sql server.
> how to import this sql file into my new sql server?
> the sql file contains the structure/description of the procedures.
> thank you.
>

.sql file ..

hi,
i have a sql file that is exported from the other sql server.
how to import this sql file into my new sql server?
the sql file contains the structure/description of the procedures.
thank you.
One method is to run the script from Query Analyzer. Connect to the desired
server and select File --> Open and navigate to your script file. Set the
database context to the desired database and execute.
Hope this helps.
Dan Guzman
SQL Server MVP
<Blue> wrote in message news:eLh$G69kEHA.3520@.TK2MSFTNGP11.phx.gbl...
> hi,
> i have a sql file that is exported from the other sql server.
> how to import this sql file into my new sql server?
> the sql file contains the structure/description of the procedures.
> thank you.
>

.sql file

hi,
i have a sql file that is exported from the other sql server.
how to import this sql file into my new sql server?
the sql file contains the structure/description of the procedures.
thank you.
Blue
Run
EXEC master..xp_cmdshell 'osql.exe -S SERVER -U sa -P pass -dNorthwind -i
"D:\ORD1.sql"'
Let me say you have ORD1.sql file which constains
CREATE PROC spMyProc
AS
SELECT * FROM Orders
<Blue> wrote in message news:%23jxZ549kEHA.3372@.TK2MSFTNGP09.phx.gbl...
> hi,
> i have a sql file that is exported from the other sql server.
> how to import this sql file into my new sql server?
> the sql file contains the structure/description of the procedures.
> thank you.
>

.sql file

hi,
i have a sql file that is exported from the other sql server.
how to import this sql file into my new sql server?
the sql file contains the structure/description of the procedures.
thank you.Blue
Run
EXEC master..xp_cmdshell 'osql.exe -S SERVER -U sa -P pass -dNorthwind -i
"D:\ORD1.sql"'
Let me say you have ORD1.sql file which constains
CREATE PROC spMyProc
AS
SELECT * FROM Orders
<Blue> wrote in message news:%23jxZ549kEHA.3372@.TK2MSFTNGP09.phx.gbl...
> hi,
> i have a sql file that is exported from the other sql server.
> how to import this sql file into my new sql server?
> the sql file contains the structure/description of the procedures.
> thank you.
>

.NET Provider for SAP and variables

Hi,

I am using .NET Provider for SAP to import data from SAP BW to the SQL-Server 2005.

The OLE-DB source has an sql command like this

"exec Z_SQL_EXPORT @.I_WEEK_FROM='200601', @.I_WEEK_TO='200602'

I want to make this command more flexible by using a variable strWEEK_FROM and strWEEK_TO.

How can I do this?

Thank you for yout help.

Gerd

Two methods, expressions or OLE-DB parameters.

You can use a ? as the placeholder, so like this -

exec Z_SQL_EXPORT @.I_WEEK_FROM=?, @.I_WEEK_TO=?

Then go to the parameters dialog in the source and map the variables to the placholders.

This is a better method, I think, more secure for a start, but does not always work due to what the parameter is or the OLE-DB provider.

or

Create a variable and set EvaluateAsExpression to true.

Set the expression on the variable to build the string you require, perhaps something like this, but do some proper date formatting -

"exec zsqlexport @.week = '" + (DT_WSTR, 10)@.[Varname] + "'"

Change the source query type to be SQL Command from variable.

|||

Thanks DarrenSQLIS for your answer.

I found similar informations on other web-sites.

When I try to use your first possibility and click OK in the dialog box then I receive the response

"Error in data-flow-task: An error occured...".

How can I "map the variables to the placholders"?

Then I treid the second solution, but with the oledb-data-provider for sap I found no possibibilty to change the source type to SQL command from variable.

Can you help me again?

Thanks.

Gerd.

|||

Sorry, my mistake. The SAP provider is a ADO.Net managed provider, so of course you use the DataReader Source which does not have the same options as the OLE-DB Source.

There is no parameter support, but you can use an expression. Rather than using a variable, you can set a property expression actually on the SqlCommand property for the DataReader source.

To set an expression a component property (not all component properties upport though however), you need to be in the context of the data flow. Either select the task in Control View, or just click on a clean area of the Data Flow design surface, and then look at the Properties grid, showing the properties of that task. Now select Expressions from the properties grid. You can then select the SqlCommand property for the instance of the data reader source you wish to work against.

|||

Thanks again,

this works!

For those who have a similar problem, I found an article that give a detailled description on how to work with datareader expressions and sqlcommand:

http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/5/7.aspx

.dbf file import (duplicate Field Names)

I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.

Can anyone make recommendations here that might help?

Oh, this is SQL200 in case that impacts what you might advise!!!!

Anyone out there from MSFT want to make some suggestions?|||

Hi Ellen,

I find it surprising that the DBF file can have duplicate field names. What application created it? Is it a FoxPro DBF or a DbaseIV or Paradox DBF?

I assume you are able to read the table structure with something other than SQL Server, is that correct?

Are you using ODBC or OLE DB? The latest FoxPro and Visual FoxPro OLE DB data provider is downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .

.dat and .idx

I have .idx and .dat files. I need an ODBC driver to allow me to attach to
or import these tables. Anyone have any clue how to help me?idx is the default for index creation statement. I don't know recognize the
.dat extension. You should be able to run the idx file as a normal SQL
statement.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:u46gUx6yGHA.2036@.TK2MSFTNGP05.phx.gbl...
>I have .idx and .dat files. I need an ODBC driver to allow me to attach to
>or import these tables. Anyone have any clue how to help me?
>|||Ben Watts wrote:
> I have .idx and .dat files. I need an ODBC driver to allow me to attach to
> or import these tables. Anyone have any clue how to help me?
>
What are these files from?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

.dat and .idx

I have .idx and .dat files. I need an ODBC driver to allow me to attach to
or import these tables. Anyone have any clue how to help me?idx is the default for index creation statement. I don't know recognize the
.dat extension. You should be able to run the idx file as a normal SQL
statement.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:u46gUx6yGHA.2036@.TK2MSFTNGP05.phx.gbl...
>I have .idx and .dat files. I need an ODBC driver to allow me to attach to
>or import these tables. Anyone have any clue how to help me?
>|||Ben Watts wrote:
> I have .idx and .dat files. I need an ODBC driver to allow me to attach to
> or import these tables. Anyone have any clue how to help me?
>
What are these files from?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

.dat

Hello,

Is there somebody ho can tell me how to import a '.dat' file in Microsoft
SQL Server 2000 Developer?

The '.dat' file contain a fully database, with many different tables. Each
of them contain many different rows.

Thank you very much.

Christian.You need to know how the '.dat' file was created in order to import it. Use
RESTORE FILELISTONLY to determine if this is a database backup:

RESTORE FILELISTONLY
FROM DISK='C:\MyDatabase.dat'

You can then execute a RESTORE with the desired MOVE options.

RESTORE MyDatabase
FROM DISK='C:\MyDatabase.dat'
WITH
MOVE 'MyDatabase' TO 'C:\DBDataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\DBDataFiles\MyDatabase_Log.ldf'

If you get an error during the RESTORE FILELISTONLY that states the file
isn't a valid backup, you'll need to investigate further. You might try
opening it using a text editor to see of that provides a clue of the file
format.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Christian R" <Christian.Rosseel@.pandora.be> wrote in message
news:Zn65f.26124$z_6.1075248@.phobos.telenet-ops.be...
> Hello,
>
> Is there somebody ho can tell me how to import a '.dat' file in Microsoft
> SQL Server 2000 Developer?
> The '.dat' file contain a fully database, with many different tables. Each
> of them contain many different rows.
> Thank you very much.
>
> Christian.
>|||Do you know what format the file is? .DAT could be anything because
plenty of different applications and data sources use DAT as a file
extension. If you know what format the file is then you can proceed to
look for an ODBC driver for that format.

If you really have no idea then try taking a look at the file in a hex
editor or file viewer. Maybe there will be something in the header that
indicates the source. Otherwise you may have to consult whoever was the
originator of the file.

If the file is in some readable text format like a delimited or fixed
width ASCII file then you could use DTS to import it.

Maybe this is in fact a SQL Server database file with a non-standard
file extension. In that case you may be able to attach it using
sp_attach_single_file_db so take a look at that topic in Books Online.
If you do attempt that, make sure you have a backup of the file first
because the server may write to the file.

--
David Portas
SQL Server MVP
--|||Thanks for the help that you given me.

I'm sorry, but Microsoft SQL Server 2000 is pretty new for me, and I really
do not now what kind of format the database (.dat) is.

I have trying to open it with a text editor, because I don't have a hex
editor and the only thing is have seen is computer language. I also tried a
'RESTORE FILELISTONLY' from the command prompt, but that didn't work. It my
be look a little stupid that I have tried it from the command prompt, but I
don't now what else it means. I suppose I have to search it in the server
itself, but my version is in Dutch. Perhaps you can tell me where I can find
that option in the menu of the Enterprise Manager?

What I not have tried is 'sp_attach_single_file_db'. Can you explain me a
little more about that possibility, perhaps that work?

Again, thank you very mouth for the help, and sorry for my imperfect
English! I live in Flanders.

Christian.|||Christian R (Christian.Rosseel@.pandora.be) writes:
> I'm sorry, but Microsoft SQL Server 2000 is pretty new for me, and I
> really do not now what kind of format the database (.dat) is.

As David said, it could be anything. All I know is that you said:

The '.dat' file contain a fully database, with many different tables.
Each of them contain many different rows.

Do you know that this is actually SQL 2000? .dat is not normal file
extension in SQL 2000. This could be Ingress, Informix, Oracle or
whatever. (Well, maybe none of them would ever use .dat, but since I
don't know them, I will have to assume that they may.)

There is one SQL Server context where .dat appears though, and that is
an SQL 6.5 device file. If this really is such a file, you cannot
access it with SQL 2000. SQL 6.5 had a completely architecture, and
the only program that can read SQL 6.5 device files is SQL 6.5. Which,
thankfully, is available on MSDN Subscriber Downloads.

> I have trying to open it with a text editor, because I don't have a hex
> editor and the only thing is have seen is computer language. I also
> tried a 'RESTORE FILELISTONLY' from the command prompt, but that didn't
> work. It my be look a little stupid that I have tried it from the
> command prompt, but I don't now what else it means. I suppose I have to
> search it in the server itself, but my version is in Dutch. Perhaps you
> can tell me where I can find that option in the menu of the Enterprise
> Manager?

Use Query Analyzer to run T-SQL commands.

> What I not have tried is 'sp_attach_single_file_db'. Can you explain me a
> little more about that possibility, perhaps that work?

exec sp_attach_single_file_db 'yourdb', 'C:\temp\yourfile.dat'

Note that the file path relates to the server, not your workstation
(unelss you run SQL Server locally).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

.BAK extension ?

Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG.BAK is the standard extension for a full backup. You'll have to have a
recent version of SQL Server to try and restore it. If it restores OK, you
can extract the structure in the form of a script.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Drazen Gemic" <anyone@.anywhere.tk> wrote in message
news:dv7haf$me7$1@.magcargo.vodatel.hr...
Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

Seems to me that the data being imported is being imported as an int and hence the values which are not integer (row 62 and beyond) do not get imported into the table. However, this is just my guess. You might get better answer from Integration Services forum and I am moving this thread to the forum for you to get better assistance.

HTH,

|||

Yes that is correct. SSIS will look at the first few rows of Excel data to determine what the data type of the column is. As far as I know there is no easy way around that. Your best option is to ensure that one of the "R" values is in the first few rows.

Otherwise, you could do an import from .csv isntead of from Excel. That would give you more control.

Dylan.

|||

I struggle with the strange behaviour of excel imports for days now..

what helped a lot was this articel:

http://blog.lab49.com/?p=196

I hope it helps you too...

Mybe someone has a solution to my urgent excel import problem too... please look here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2129460&SiteID=1

if you have a minute left...

Thanks

Jens

|||

Thank you every one for your input... I solved the problem by changing the cell format as text and then copied all the information back.. to the excel spread sheet as text and everything works fine...

Regards

Karen

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

A short answer(work around): import throgh an Access table will address this issue.

Or change the numbers in this column to text by adding an ' in front of these numbers before your import.

I hope someone can provide more elegant solution(s) to this issue.

(URgent)Problem with excel File

Hi,

I am trying to import data from a spread sheet to a sql server database... and one of the cells contains which are numeric and only and some are alpha numeric also... but when i try to import them to Sql server i get a NULL in the cells where there is Alpha numeric characters...

I have also tried opening a new spread sheet and setting the format for that particular column and text then i just paste it and then saving,.. but when i try to upload the data i am getting an error (thru my asp.net website) Saying that

No value given for one or more required parameters.

Hope someone can help me solve this.....

Regards

Karen

You could probably use BCP.exe to get this done quickly. The process would be to save the excel file as a .csv, then strip off the headers, if you use tab as your columns separator and \n as your row terminator, then you should be able to bcp it in with the -c option and no further work. Otherwise, you might need to create a format file. More information on how to use BCP can be found in books online for bcp.exe.

Hope that helps,

John

|||

Have you tried using OPENROWSET?

|||

Whats that about.. The user has data in spread sheet and right now i have built a interface that the user can directly log in and upload the excel files and then it will transfer the data to the database.. it works fine if a particular column doesnt have alpha numeric characters..

Regards

Karen

|||

Check out the following KB articles

http://support.microsoft.com/kb/321686 - This address's possible methods for importing from Excel to SQL Sever

Also http://support.microsoft.com/kb/194124/EN-US/

This article directly address's the issue of "Excel Values Returned as NULL Using DAO OpenRecordset"

This sounds like the issue you are facing

Regards

Nadreck

(URgent)Problem with excel File

Hi,

I am trying to import data from a spread sheet to a sql server database... and one of the cells contains which are numeric and only and some are alpha numeric also... but when i try to import them to Sql server i get a NULL in the cells where there is Alpha numeric characters...

I have also tried opening a new spread sheet and setting the format for that particular column and text then i just paste it and then saving,.. but when i try to upload the data i am getting an error (thru my asp.net website) Saying that

No value given for one or more required parameters.

Hope someone can help me solve this.....

Regards

Karen

You could probably use BCP.exe to get this done quickly. The process would be to save the excel file as a .csv, then strip off the headers, if you use tab as your columns separator and \n as your row terminator, then you should be able to bcp it in with the -c option and no further work. Otherwise, you might need to create a format file. More information on how to use BCP can be found in books online for bcp.exe.

Hope that helps,

John

|||

Have you tried using OPENROWSET?

|||

Whats that about.. The user has data in spread sheet and right now i have built a interface that the user can directly log in and upload the excel files and then it will transfer the data to the database.. it works fine if a particular column doesnt have alpha numeric characters..

Regards

Karen

|||

Check out the following KB articles

http://support.microsoft.com/kb/321686 - This address's possible methods for importing from Excel to SQL Sever

Also http://support.microsoft.com/kb/194124/EN-US/

This article directly address's the issue of "Excel Values Returned as NULL Using DAO OpenRecordset"

This sounds like the issue you are facing

Regards

Nadreck

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly