How would I get a query output with different than comma delimiter?

[katrina11]

Hi experts,

Being a new in MS SQL Server 2005 just wonder how can I change delimiter “,” in the output to any other (for example a pipe?)

Actually after I had got a query output I chosed submenu “results to” under “Query” menu.

After that I right click somewhere on the output and get the menu options:
-To the text
-To the Grid
-To the file

…and chosed the "text" option . It always generate comma delimited output as .csv text.

Try as I might I was unable to get a file with different delimiter or fixed width file (even when I chose other than “text” options.

Could you please tell what I am supposed to do in order to get:
-the output with different delimiter
or
-fixed width file

Any help greatly appreciated

Katrin

 

[katrina11]

Now I am trying to create a table on Server in order to grab it into ACCESS through ODBC connection;

I created a table but for some reason system complains on statement in the middle:
INSERT INTO dbo.TempSiteIR_
"Msg 8152, Level 16, State 14, Line 13
String or binary data would be truncated.
The statement has been terminated."

It looks my CREATE part really create a structure. Select part works perfect ....separately

This is my whole code. What is wrong with my "INSERT INTO "?
***********************************

CREATE TABLE dbo.TempSiteIR_ (
[memberId][int],
[membername][varchar](60),
[pcpsiteid][int],
[pcpsitename][varchar](95),
[serviceproviderid][int],
[serviceproviderName][varchar](95),
[Serviceprovidertype][varchar](60),
[primaryspecialty][varchar](15),
[servicedatefrom][datetime],
[ismembersPCP] [int]
)
INSERT INTO dbo.TempSiteIR_

select distinct
memberid
,membername
,pcpsiteid
,pcpsitename
,ch.serviceproviderid
,ServiceProviderName
,Serviceprovidertype
,p.primaryspecialty
,cl.servicedatefrom
,case when PcpProviderId=ch.serviceproviderid then 1 else 0 end as ismembersPCP
FROM
DW.dbo.ClaimLine CL
inner join DW.dbo.Claimheader ch ON CH.ClaimID = CL.ClaimID
INNER JOIN DW.dbo.CoeEventDimMap CEDM ON (CEDM.ClaimID = CL.ClaimID and CEDM.LineNumber = cl.LineNumber)
INNER JOIN DW.dbo.Event_DIM ED ON ED.Event_PK = CEDM.Event_PK
inner join dw..providerdemographics p ON ch.serviceproviderid=p.providerid

 

[TysonLPrice]

Go to Options, Query results, results to text, click custom delimiter from the dropdow, key in the pipe delimiter.

 

[Qik3Coder]

You have to explicitly trim the fields down, so that they will fit in your new table.

You've got questions and source code. We want both!

 

[katrina11]

I have just found a new syntax
select distinct
memberid
,membername
,pcpsiteid
,pcpsitename
,ch.serviceproviderid
,ServiceProviderName
,Serviceprovidertype
,p.primaryspecialty
,cl.servicedatefrom
,case when PcpProviderId=ch.serviceproviderid then 1 else 0 end as ismembersPCP
into dbo.TempSiteIR
FROM
DW.dbo.ClaimLine CL
inner join DW.dbo.Claimheader ch ON CH.ClaimID = CL.ClaimID
INNER JOIN DW.dbo.CoeEventDimMap CEDM ON (CEDM.ClaimID = CL.ClaimID and CEDM.LineNumber = cl.LineNumber)
INNER JOIN DW.dbo.Event_DIM ED ON ED.Event_PK = CEDM.Event_PK
inner join dw..providerdemographics p ON ch.serviceproviderid=p.providerid

It looks like it works and I finally was able to get newly created table from Server to Access2007 through ODBC!!!!!!!!