Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

GO

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

GO

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

go

AMB

sql

EXEC Statements in a sql script

Thanks for the response. Didn't help however can tell you what is
happening now. I do an alter to create a new column. Put it in a
transaction and commit it. Then the next transaction I do an update to
the newly created column and it complains it can't find the column. If
I run all this manually it's fine. Could it be an issue with the speed
that the script is running that SQL Server, even though I committed
between alter and update, still is not done creating the tables or
something?
Thanks.
JRJR (jriker1@.yahoo.com) writes:
> Thanks for the response. Didn't help however can tell you what is
> happening now. I do an alter to create a new column. Put it in a
> transaction and commit it. Then the next transaction I do an update to
> the newly created column and it complains it can't find the column. If
> I run all this manually it's fine. Could it be an issue with the speed
> that the script is running that SQL Server, even though I committed
> between alter and update, still is not done creating the tables or
> something?
No, speed has nothing to do with it.
If you do:
ALTER TABLE tbl ADD newcol int
UPDATE tbl
SET newcol = 91
this will fail, because when SQL Server compiles this batch, it sees
that you references a column that does not exist in tbl, and that is
an error. SQL Server has deferred name resolution, so that if a table
does not exist when the batch is compiled, SQL Server is silent in hope
that the table is created. There is, thankfully, not deferred name
resolution for column names. It is bad as it is.
There are a couple of ways to skin the cat. The best is probably
to wrap the UPDATE into EXEC(), so that it will not be compiled
until after the ALTER TABLE statement has been executed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 27 Mar 2006 22:42:08 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>There are a couple of ways to skin the cat. The best is probably
>to wrap the UPDATE into EXEC(), so that it will not be compiled
>until after the ALTER TABLE statement has been executed.
Hi Erland,
In a stored procedure: yes.
But in a SQL script, just adding a "go" between the ALTER TABLE and the
UPDATE is enough.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> (snip)
> Hi Erland,
> In a stored procedure: yes.
> But in a SQL script, just adding a "go" between the ALTER TABLE and the
> UPDATE is enough.
I didn't mention that possibility because the hour was late, and there are
some caveates with it. Say that you do:
BEGIN TRANSACTION
-- Do something
go
-- Do something more
go
-- Yet something more
COMMIT TRANSACTION
Now, if there is an error on the line of the kind that aborts the batch,
the transaction will be rolled back, but the remaining batches will be
executed. You will get an error when you reach COMMIT, but then the damage
may already been done.
Of course, in this particular case if ALTER TABLE fails, the UPDATE command
will also fail. However, there can be other commands in other batches that
still can be carried out when they shouldn't.
One way to handles this is to open every batch with IF @.@.trancount > 0,
but I think would be prefer to keep all in one batch, and interleave
problematic statments in dynamic SQL. Not the least on SQL 2005, as I
then can have single CATCH handler at the end. (But note that if you
have:
BEGIN TRY
UPDATE tbl SET missingcolumn = <somevalue>
END TRY
BEGIN CATCH
-- handle error
END CATCH
that the CATCH handler will not be reached, as the error is a compilation
error.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 28 Mar 2006 10:27:33 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
>I didn't mention that possibility because the hour was late, and there are
>some caveates with it. Say that you do:
(snip)
Hi Erland,
Good point. Thanks for adding this warning!
Hugo Kornelis, SQL Server MVPsql

Excluding a column with snapshot replication

I have a database that I'm trying to replicate to allow users to run MI
type queries. This would remove the impact on the operational database
of long running expensive queries. The database records details of
emails including the text and is about 40 GB in size. The database is
part of a package system. One table contains all the text of all emails
in an ntext column and accounts for 25 GB. I would like to exclude the
column from replication as with it, replication takes over 12 hours and
the MI queries do not use the column. However, the queries do use views
that reference the column.
I'm using snapshot replication that runs once a day. In the
"Publication Properties" in the "Filter Columns" tab, I de-selected the
column. The target database has an identical schema to the source
database. When the distribution job runs it fails with a message
indicating it couldn't bulk load the table in question and the error
message indicates that an "Unexpected EOF encountered in BCP data-file".
I've assumed that the BCP data file has a structure that is at
variance with the table. I tried dropping the column from the target
table but replication then fails during the application of a number of
*View.sch scripts because the views reference the column. This is
despite the fact that the publication property for all database objects
is not to drop them.
Can anyone suggest a way in which I can get replication to work without
including the data in one column but to retain the complete schema.
TIA
Laurence Breeze
Laurence,
you could replicate the table (minus the problem column) to a table of
another name. Create a view which has the old tablename and queries the new
table, with an additional column containing a hardcoded null.
Rgds,
Paul Ibison
|||Thanks Paul,
This has done the trick.
Laurence
Paul Ibison wrote:
> Laurence,
> you could replicate the table (minus the problem column) to a table of
> another name. Create a view which has the old tablename and queries the new
> table, with an additional column containing a hardcoded null.
> Rgds,
> Paul Ibison
>

Exclude column from replication, but leave column on subscriber.

It seems I can't figure out how to have two tables, say integer columns named
A, B, and C, with a rowguid column.
I want that table structure on my subscriber (A, B, C, rowguid) but I only want
to replicate data from columns A and B.
As it stands, replication tries to create it on the subscriber as A, B,
rowguid, leaving the C column off.
Thanks for any help.
Brian K
should have specified, this is merge replication, SQL Server 2000.
Brian
In article <4IJtc.16319051$Id.2708018@.news.easynews.com>, Brian Keener wrote:
> It seems I can't figure out how to have two tables, say integer columns named
> A, B, and C, with a rowguid column.
> I want that table structure on my subscriber (A, B, C, rowguid) but I only want
> to replicate data from columns A and B.
> As it stands, replication tries to create it on the subscriber as A, B,
> rowguid, leaving the C column off.
> Thanks for any help.
> Brian K
|||Brian,
is it a requirement that the rowguid changes independantly on publisher and
subscriber? If not and the guid can't change then why not include it?
Regards,
Paul Ibison
|||In article <OT6YKXNREHA.3300@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> is it a requirement that the rowguid changes independantly on publisher and
> subscriber? If not and the guid can't change then why not include it?
> Regards,
> Paul Ibison
>
Hmm, rowguid is not a column I'm worried about. It can stay the same
across the board. Row C need to be able to change independently on
the two tables.
Brian K
|||Brian,
you'd have to edit the merge triggers to enable this by preventing entries
into MSmerge_contents from changes to C, and I wouldn't recommend it. It
would also make validation impossible. You could however achieve the same
functionality using views - the view would be a join returning columns A, B,
C, rowguid while the underlying tables would be
table1: A,B,rowguid and
table2: C,pk (one of A or B)
Then you just replicate table1. If changes can occur to cols A/B and C in
one update then you'll need to set up instead of triggers.
HTH,
Paul Ibison
|||its not the merge triggers you edit for this. Its the stored procedures that
is uses which look like this
sp_ins_3477CF08A8A2481D3269F4FE171245B8
look for a statement that looks like this.
if @.retcode<>0 or @.@.ERROR<>0
begin
set @.errcode= 0
goto Failure
end
insert into [dbo].[authors] ( [au_id] , [au_lname] , [au_fname] , [phone] ,
[address] , [city] , [state] , [zip] , [contract] , [msrepl_tran_version] ,
[rowguid] ) values ( @.p1 , @.p2 , @.p3 , @.p4 , @.p5 , @.p6 , @.p7 , @.p8 , @.p9 ,
@.p10 , @.p11 )
select @.rowcount= @.@.rowcount, @.error= @.@.error
do this for both the insert and update procedures
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23zbYbaOREHA.3012@.tk2msftngp13.phx.gbl...
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A,
B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
|||Hilary,
I agree that it could be done in the stored procedure, but think the trigger
modification is simpler:
For an update, what I had in mind is something like
IF Not UPDATE(C)
BEGIN
existing trigger code
END
The insert and delete triggers could be left as they are, as both should
propagate through the normal merge replication.
Cheers,
Paul Ibison
|||Paul
After posting my solution I had reservations about it.
The advantage of your solution is the update trigger will fire but won't
write replication metadata for updates to the columns you wish to exclude.
My solution is that all updates will be propagated to the subscriber, but
will not be applied.
In retrospect I think your solution is perhaps the better one.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23c9lNXaREHA.2408@.tk2msftngp13.phx.gbl...
> Hilary,
> I agree that it could be done in the stored procedure, but think the
trigger
> modification is simpler:
> For an update, what I had in mind is something like
> IF Not UPDATE(C)
> BEGIN
> existing trigger code
> END
> The insert and delete triggers could be left as they are, as both should
> propagate through the normal merge replication.
> Cheers,
> Paul Ibison
>
|||In article <#zbYbaOREHA.3012@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A, B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
Pretty much what I assumed.
I've already implemented this as a view in our test environment, but
thanks for the information.
Brian K

exclude blank records

I have created a script that returns every column and row that is queried
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com

EXCEPTION_ACCESS_VIOLATION

I got this error when i try to insert a data in a table
with a Text Column in a distributed application (MSTDC
COM+ ASP), somebody have some one idea about this
error ? more descriptions from event viewer :
Error : 0 Severity : 19 State : 0
SqlDumpExceptionHandler: Process 61 generated fatal
exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.Check your SQL Server error log for the exact stack dump that goes with the
Access Violation (AV) message. Pull out the keywords from the first couple
of lines of the dump and search for them in the online Knowledge Base.
Also, use profiler to find out what the exact command is that results in
the AV and see if the same thing happens when you run that command from
Query Analyzer.
Are you on the latest service pack for SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Excel with ADO problem

Hello,

I connect to an Excel file with ADO and I get the sheet names and after that I read the table. if the column is all integers or strings there's no problem but if the column is composed of integers and strings then only the strings are returned,others are returned empty.

What's the reason?

Thanks

Hi,

did you try this here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Excel to MS SQL

Hai all,
I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

Thanks
NarayanaswamyYou should import your data as VARCHAR in a temporary table, and transform it in a second step.|||Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy|||Yeah, Excel is great, but not for storing data.

I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.|||Originally posted by Narayanaswamy
Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy

I did have the same problem with importing excel data to MSSQL.
After some fighting I just imported data to Access and then to MSSQL.
It was needed to do not often. :)

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import

Trying to import an excel file into SQL Server. For some reason, this
column is being considered Double(15) and when it imports, it's losing text
data. I'd like it to just be treated as any form of text and import
exactly. Right now all of the numeric ones go through but the hybrid ones
are blank. Is this possible?
KM
--
154+a
84+b
154
84
15
15
84Are you using a script to import or just the import wizard from SQL? If you
use the import wizard, are you predefining the fields before you import but
after you select the flat file source. (first screen but you need to scroll
down to the Advanced - Data source: - format each column)
If you have run through the import and failed, the table is already created
with these predefined fields and you will need to modify them to fit the data.
The above assume you use the wizard (right click - database - choose Import)
--
Regards,
Jamie
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||These little scripts may be of relevance:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
Linchi
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||Hi Jamie,
I am facing the same problem while I import excel to sql server 2000. I
couldnt find the option (first screen but you need to scroll down to the
Advanced - Data source: - format each column) what you have mentioned here.
For workaround before I import I prefix some character for instance "#" or
any other character for these kind of columns which make DTS aware of varchar
datatype and once its imported I fire update query to remove this char. so
far I havent been able to figure out any out of the box solution from SQL
server.
"thejamie" wrote:
> Are you using a script to import or just the import wizard from SQL? If you
> use the import wizard, are you predefining the fields before you import but
> after you select the flat file source. (first screen but you need to scroll
> down to the Advanced - Data source: - format each column)
> If you have run through the import and failed, the table is already created
> with these predefined fields and you will need to modify them to fit the data.
> The above assume you use the wizard (right click - database - choose Import)
> --
> Regards,
> Jamie
>
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >|||Bhavesh,
Worst case scenario for me... when I can't get it to import - I save the
Excel as a CSV - check the file over visually (sometimes users have comma's
embedded in text which will throw off the import) and then change the
extension from CSV to TXT and import it as a comma-delimited Text file. It
isn't often I need to do so but it comes in handy when other methods fail.
--
Regards,
Jamie
"Bhavesh" wrote:
> Hi Jamie,
> I am facing the same problem while I import excel to sql server 2000. I
> couldnt find the option (first screen but you need to scroll down to the
> Advanced - Data source: - format each column) what you have mentioned here.
> For workaround before I import I prefix some character for instance "#" or
> any other character for these kind of columns which make DTS aware of varchar
> datatype and once its imported I fire update query to remove this char. so
> far I havent been able to figure out any out of the box solution from SQL
> server.
>
> "thejamie" wrote:
> > Are you using a script to import or just the import wizard from SQL? If you
> > use the import wizard, are you predefining the fields before you import but
> > after you select the flat file source. (first screen but you need to scroll
> > down to the Advanced - Data source: - format each column)
> >
> > If you have run through the import and failed, the table is already created
> > with these predefined fields and you will need to modify them to fit the data.
> >
> > The above assume you use the wizard (right click - database - choose Import)
> > --
> > Regards,
> > Jamie
> >
> >
> > "James" wrote:
> >
> > > Trying to import an excel file into SQL Server. For some reason, this
> > > column is being considered Double(15) and when it imports, it's losing text
> > > data. I'd like it to just be treated as any form of text and import
> > > exactly. Right now all of the numeric ones go through but the hybrid ones
> > > are blank. Is this possible?
> > >
> > > KM
> > > --
> > > 154+a
> > > 84+b
> > > 154
> > > 84
> > > 15
> > > 15
> > > 84
> > >
> > >
> > >|||Linchi,
Nice little article.
We have someone in our company that does the imports by creating the insert
statement in the Excel sheet and into a column that precedes the columns he
imports... he gets a great deal of work done that way.
--
Regards,
Jamie
"Linchi Shea" wrote:
> These little scripts may be of relevance:
> http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
> Linchi
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >