OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

OpenQuery not working after applyin SQL Server SP4

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Issue:

We have Linked Server setup to OLAP Server. which was working fine for long time. After applying SP4 on SQL Server, we started getting this error. But the same query runs from another server with SP3.

After applying SP4, I have also applied 2 hotfixes to SQL Server. One hot fix is related to using using memory more than 3GB and another hotfix is for this Linked server issue. but still I have the problem.

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP' reported an error.
[OLE/DB provider returned message: Database 'PLS' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize returned 0x80004005: ].

Appreciate your help.

Thanks,

Varad

sql

Only update changed columns

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?binder (rgondzur@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?

In our application, most insert/update procedures exposes about all
columns in the table, and when the client fills the GUI, it reads all
columns from the table. Thus there is little reason to check what
actually changed. (Except for auditing.)

If you want to expose an interface of a procedure where the caller only
specifies what the to change, because it has for some reason not read
all existing columns (and this makes sense for a client that is a
monitori or similar), you could do:

CREATE PROCEDURE update_sp @.keyvalue sometype,
@.par1 someothertype = NULL,
@.par2 yetanothertype = NULL,
... AS

UPDATE tbl
SET col1 = coalesce(@.par1, col1),
col2 = coalesce(@.par2, col2),
...
WHERE keycol = @.keyvalue

That is, the procedure accepts parameters for all updatable columns,
but the caller passes non-NULL values for those it does not want to
change. This presumes that a caller never want to set a column to
NULL. If this is a required, you need to add one flag parameter for
each value parameter.

--
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

only each first item from a select with group ....

i want to get only the first (max sum ) value of each grouped item after a
select
example of the returned records where: two columns (grouped value company
and sum sell price)
in the table there are a nr of entrys of each company
i calculate the sum for the sell price in each company
i want to display only the best of a region
now i get somethink like
nord company33 145678,44
nord company2 34578,44
nord company44 478,44
nord company6 78,44
west company77 645678,44
west company8 35678,44
sud company9 995678,44
sud company4 678,44
but i want to display only the first (max) entry of each group - like
nord company33 145678,44 (the best from region nord)
west company77 645678,44
sud company9 995678,44
thanks
XavierTry,
To make it readable, let us create a view.
create view v1
as
here goes the select statement that does the grouping
go
select *
from v1 as a
where not exists (
select *
from v1 as b
where b.region = a.region
and b.sum_sell_price > a.sum_sell_price
)
-- or
select a.*
from v1 as a
inner join
(
select region, max(sum_sell_price ) as max_sum_sell_price
from v1
group by region
) as b
on a.region = b.region
and a.sum_sell_price = b.max_sum_sell_price
AMB
"Xavier" wrote:

> i want to get only the first (max sum ) value of each grouped item after
a
> select
> example of the returned records where: two columns (grouped value company
> and sum sell price)
> in the table there are a nr of entrys of each company
> i calculate the sum for the sell price in each company
> i want to display only the best of a region
> now i get somethink like
> nord company33 145678,44
> nord company2 34578,44
> nord company44 478,44
> nord company6 78,44
> west company77 645678,44
> west company8 35678,44
> sud company9 995678,44
> sud company4 678,44
> but i want to display only the first (max) entry of each group - like
> nord company33 145678,44 (the best from region nord)
> west company77 645678,44
> sud company9 995678,44
> thanks
> Xavier
>|||it works, thanks for your help
Xavier
"Alejandro Mesa" wrote:
> Try,
> To make it readable, let us create a view.
> create view v1
> as
> here goes the select statement that does the grouping
> go
> select *
> from v1 as a
> where not exists (
> select *
> from v1 as b
> where b.region = a.region
> and b.sum_sell_price > a.sum_sell_price
> )
> -- or
> select a.*
> from v1 as a
> inner join
> (
> select region, max(sum_sell_price ) as max_sum_sell_price
> from v1
> group by region
> ) as b
> on a.region = b.region
> and a.sum_sell_price = b.max_sum_sell_price
>
> AMB
> "Xavier" wrote:
>

Only bringing in select columns from a text file

Hi all,

I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package.
Can i fix this?

And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message

'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'

Blindman, you probably know exactly what to do, huh?Look up BCP and Format file in BOL.|||Exactly what to do?

Shoot the jackass who created the datafile.

If you are in luck (which you probably aren't) the string fields are enclosed by quotes, in which case commas won't cause a problem. But you are having a problem, so forget that...

Check you datafile. Commas embedded in text are followed by spaces, while comma delimiters may not be, depending on the system that generated the file. If this is true with your data, then try this trick:

1) Using a text editor, replace all occurrences of ", " with another string, such as "azbycx".
2) Replace all occurrences of "," with the pipe character "|".
3) Replace all occurrences of "azbycx" with your origianl ", ".

Voila! If all goes well you now have a pipe delimited file.

If your data is too messy for this, then you may have to load it into a staging table as a single column and write a procedure for parsing it yourself, based upon its characteristics.

...or you could just ask the jackass to give you a better file before you shoot him.|||I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

No such luck for me today. Primarily because I can't shoot the jackass (laws, you see), and I can't send the data back. Fortunately, I don't NEED that particular troublesome column, so how can I bring in only the columns preceding the bad data filled one?

I attempted a hideiously clumsy but successful workaround involving opening said text file in excel, copying and pasting, deleting bad columsn, creating array, and importing back into SQL. Obviously, this just won't work once the file gets to be over 65,536 rows.

And I've been told, this is something we will be doing a lot of. I have a boss that doesn't accept the "bad data" excuse, electing rather to spit out, "Get creative, make it work."

Am I just totally screwed??

OH, and the comma delimiters are accompanied by different spacing , because it's really a fixed width comma delimited field. And columns without values are given a random fixed width (blank of course) ie. ,' ',' ',

Nice, eh?|||I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

Relax..

First, you should have books online open at all times...sometimes it's hard to navigate, but it's all in there.

Second, There are MANY ways to do this...like load the whole thing into a single column varchar(8000) column, and use t-sql to audit the crap.

Third, you could use a format card, like

7.0
18
1 SQLCHAR 0 14 "" 1 EmployeeID
2 SQLCHAR 0 40 "" 2 LastName
3 SQLCHAR 0 20 "" 3 FirstName
4 SQLCHAR 0 60 "" 4 Title
5 SQLCHAR 0 50 "" 5 TitleOfCourtesy
6 SQLCHAR 0 26 "" 6 BirthDate
7 SQLCHAR 0 26 "" 7 HireDate
8 SQLCHAR 0 120 "" 8 Address
9 SQLCHAR 0 30 "" 9 City
10 SQLCHAR 0 30 "" 10 Region
11 SQLCHAR 0 20 "" 11 PostalCode
12 SQLCHAR 0 30 "" 12 Country
13 SQLCHAR 0 48 "" 13 HomePhone
14 SQLCHAR 0 8 "" 14 Extension
15 SQLCHAR 0 0 "" 0 Photo
16 SQLCHAR 0 0 "" 0 Notes
17 SQLCHAR 0 14 "" 17 ReportsTo
18 SQLCHAR 0 510 "\r\n" 18 PhotoPath

This is from the Northwind Employees table...notice the zeroes?|||One other thing...you may be worrying about column (I don't know why if they're not image or text)...but I would get a sense that as soon as you figure this out, you're gonna have to start worrying about rows, more than columns...

Did you ever think to create a staging table and make everything varchar?

EDIT: Oh, and I like your bosses 'tude|||Well, if your data file was really "fixed width" you'd have no problems importing it, so I assume you just mean that empty values are deliniated by spaces.

I do wonder, though, how you were able to load it into Excel and delete the bad columns. Not sure why you could do this in Excel and not in SQL Server.

I have to ask: who is sending you this data on a regular basis that cannot send it in a standard format? (The boss's nephew?)

Can you post one or two of the records that contain embedded commas, just so we can see what we are dealing with?

Here is an option: load the record into a single large varchar. Write a User Defined Function that takes a string and parses off everything to the left of the first comma, truncating the rest of the string. Cycle through your staging table once for each column in your production table, using this function to populate the data.|||First of all, thanks so much for the help. I wish I could relax, but I'm under major time constraints and I'm running a 102 fever.

Okay, I uploaded the txt.file. A few new developments...
When I open the file in Excel, I can see the record with the embedded comma. (Do a search on Policy_Number WRA1227). The comma is in one of the 'address lines' near the back of the file. When I so a search for that same Policy_Number in the txt file, I can't find that policy. ??

Also, the other reason why I can't use bulk insert task to do this: Error 229. Don't have the rights.

Other details, I work for a VERY small consulting company. My boss is an actuary, knows nothing about SQL. He programs in APL, and has no troubles do any of this stuff.

I'm the only one here. So alone... :o

This dump used to work before new columns were added. And I am importing everything into a staging table as varchar using Transform Data Properties using the text souce icon, and then playing with the data during later steps in my dts.

Also should mention: I'm not a DBA. So I have a bit of trouble with the dynamic sql. Trying to learn it, but while under massive deadlines, I just do what I know how to do. Which isn't a whole lot. :(

And to answer your question blindman, we are testing a new program for a developer for a fraction of the cost of purchasing a maintstream life insurance processing program. But we will have to be dealing with "bad data" all the time, so that's why he's being such a hardass.|||Please cut and paste some sample rows of data that you have..

Also post the DDL of the final destination table

With that, I'm sure I can get you a solution quickly

Make sure the sample data has good and bad rows.

What does he want doen with the bad data|||I know I should eta, but I noticed my insert didn't work. File too big... so I just replicated the problem on a similar record. Policy Number: T58I30

Product Type,Plan,Policy Number,ID Number,Agent,Group,Issue State,Issue Date,Mode,Specified Amount,Benefit,Base Premium,Rider Premium,Contract Value,Loan Balance,Surrender Charge,Surrender Value,PUA Dividends,OYT Dividends,Dividend Cash Balance,Dividend Cash Value,Status Date,Status Code,Paid To Date,Date Last Bill,APL Option,NFO Option,DIVD Option,District,Next Premium Due Date,Next Premium Due Amount,Scheduled Premiums,Premiums Paid,Insured IssueAge,Insured Sex,Insured Class,Insured Birthdate,2nd Insured IssueAge,2nd Insured Sex,2nd Insured Class,2nd Insured Birthdate,Riders Count,Rider1 Type,Rider1 Code,Rider1 IssDate,Rider1 ExpDate,Rider1 Benefit,Rider1 Premium,Rider1 Period,Rider1 Status,Rider1 TermDate,Rider2 Type,Rider2 Code,Rider2 IssDate,Rider2 ExpDate,Rider2 Benefit,Rider2 Premium,Rider2 Period,Rider2 Status,Rider2 TermDate,Rider3 Type,Rider3 Code,Rider3 IssDate,Rider3 ExpDate,Rider3 Benefit,Rider3 Premium,Rider3 Period,Rider3 Status,Rider3 TermDate,Rider4 Type,Rider4 Code,Rider4 IssDate,Rider4 ExpDate,Rider4 Benefit,Rider4 Premium,Rider4 Period,Rider4 Status,Rider4 TermDate,Rider5 Type,Rider5 Code,Rider5 IssDate,Rider5 ExpDate,Rider5 Benefit,Rider5 Premium,Rider5 Period,Rider5 Status,Rider5 TermDate,Rider6 Type,Rider6 Code,Rider6 IssDate,Rider6 ExpDate,Rider6 Benefit,Rider6 Premium,Rider6 Period,Rider6 Status,Rider6 TermDate,Rider7 Type,Rider7 Code,Rider7 IssDate,Rider7 ExpDate,Rider7 Benefit,Rider7 Premium,Rider7 Period,Rider7 Status,Rider7 TermDate,Rider8 Type,Rider8 Code,Rider8 IssDate,Rider8 ExpDate,Rider8 Benefit,Rider8 Premium,Rider8 Period,Rider8 Status,Rider8 TermDate,Rider9 Type,Rider9 Code,Rider9 IssDate,Rider9 ExpDate,Rider9 Benefit,Rider9 Premium,Rider9 Period,Rider9 Status,Rider9 TermDate,Rider10 Type,Rider10 Code,Rider10 IssDate,Rider10 ExpDate,Rider10 Benefit,Rider10 Premium,Rider10 Period,Rider10 Status,Rider10 TermDate,Rider11 Type,Rider11 Code,Rider11 IssDate,Rider11 ExpDate,Rider11 Benefit,Rider11 Premium,Rider11 Period,Rider11 Status,Rider11 TermDate,Rider12 Type,Rider12 Code,Rider12 IssDate,Rider12 ExpDate,Rider12 Benefit,Rider12 Premium,Rider12 Period,Rider12 Status,Rider12 TermDate,Rider13 Type,Rider13 Code,Rider13 IssDate,Rider13 ExpDate,Rider13 Benefit,Rider13 Premium,Rider13 Period,Rider13 Status,Rider13 TermDate,Rider14 Type,Rider14 Code,Rider14 IssDate,Rider14 ExpDate,Rider14 Benefit,Rider14 Premium,Rider14 Period,Rider14 Status,Rider14 TermDate,Rider15 Type,Rider15 Code,Rider15 IssDate,Rider15 ExpDate,Rider15 Benefit,Rider15 Premium,Rider15 Period,Rider15 Status,Rider15 TermDate,Rider16 Type,Rider16 Code,Rider16 IssDate,Rider16 ExpDate,Rider16 Benefit,Rider16 Premium,Rider16 Period,Rider16 Status,Rider16 TermDate,Next Rider Premium Date,Next Rider Premium Amount,Rider Premiums Scheduled,Rider Premiums Paid,Expiry Date,Insured Last Name,Insured First Name,Insured SSN,Billing Option,Bill Address1,Bill Address2,Bill City,Bill State,Bill Zip,Bill Phone
S,DV583A, 56, 1, HO, 0,AL,06241963,A, 5560.21, 5560.21, 396.20, 0.00, 3345.99, 0.00, 0.00, 3345.99, 0.00, 0.00, 97.37, 97.37,20041201,52,20470624,00000000,Y,E,A, ,00000000, 0.00, 15055.60, 15055.60, 16,F,C,12051947, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20460624,'HOEHN ','MARY ','421-64-8104',D,'C/O FRED M HOEHN ','525 22ND AVENUE S ','BIRMINGHAM ','AL','35205-6429',' '
S,T58I30, 72, 2, HO, 0,FL,12271972,A, 265.99, 265.99, 0.05, 0.00, 186.22, 0.00, 0.00, 186.22, 0.00, 0.00, 0.00, 0.00,20041201,52,20381227,00000000,Y,E,C, ,00000000, 0.00, 0.10, 0.08, 34,M,C,02201939, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20371227,'POWELL ','RANDALL ','000-00-0000',D,'%FAITH,INVESTMENT CO ','NO CURRENT ADDRESS ','PENSACOLA ','FL','32505 ',' '
S,T58I30, 73, 3, HO, 0,FL,12271972,A, 197.98, 197.98, 0.04, 0.00, 113.28, 0.00, 0.00, 113.28, 0.00, 0.00, 0.00, 0.00,20041201,52,20491227,00000000,Y,E,C, ,00000000, 0.00, 0.08, 0.06, 23,M,C,07071949, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20481227,'THOMAS ','HARVEY ','000-00-0000',D,'%FAITH INVESTMENT CO ','NO CURRENT ADDRESS ','PENSACOLA ','FL','32505 ',' '
S,121101, 055, 4, HO, 0,WA,12121967,A, 10540.00, 10540.00, 0.00, 0.00, 9247.54, 0.00, 0.00, 9247.54, 0.00, 0.00, 300.80, 300.80,20041201,50,20181212,00000000,Y,E,C, ,00000000, 0.00, 7727.00, 7727.00, 49,F,C,11211918, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20171212,'HINZE ','PEARL ','479-22-7580',D,'148 102ND SE APT 21 ',' ','BELLEVUE ','WA','98004 ',' '

Excuse my ignorance, but DDL? Is that just the create table logic?

And thank you. :)|||I need the DDL as well

CREATE TABLE tablename(Col1 int, ect

Do you know how to script that in Enterprise Manager?|||Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

And some of it doesn't...is this true?

Does the data normally get loaded with quotes?|||Do you know how to script that in Enterprise Manager?

I have a DTS package in Enterprise Manager that calls a stored procedure that creates the table:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_PolDump_113004_test

AS

IF OBJECT_ID('MEC.mcopper.Pol_Dump113004_test') IS NOT NULL
DROP TABLE MEC.mcopper.Pol_Dump113004_test

CREATE TABLE [Pol_Dump113004_test]
(
[Product_Type] varchar (12) NULL,
[Plan_Number] varchar (12) NULL,
[Policy_Number] varchar (12) NULL,
[ID_Number] varchar (12) NULL,
[Agent] varchar (12) NULL,
[Group_Number] varchar (12) NULL,
[Issue_State] varchar (12) NULL,
[Issue_Date] varchar (12) NULL,
[Mode] varchar (12) NULL,
[Specified_Amount] varchar (12) NULL,
[Benefit] varchar (12) NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] varchar (12) NULL,
[Contract_Value] varchar (12) NULL,
[Loan_Balance] varchar (12) NULL,
[Surrender_Charge] varchar (12) NULL,
[Surrender_Value] varchar (12) NULL,
[PUA_Dividends] varchar (12) NULL,
[OYT_Dividends] varchar (12) NULL,
[Dividend_Cash_Balance] varchar (12) NULL,
[Dividend_Cash_Value] varchar (12) NULL,
[Status_Date] varchar (12) NULL,
[Status_Code] varchar (12) NULL,
[Paid_To_Date] varchar (12) NULL,
[Date_Last_Bill] varchar (12) NULL,
[APL_Option] varchar (12) NULL,
[NFO_Option] varchar (12) NULL,
[DIVD_Option] varchar (12) NULL,
[District] varchar (12) NULL,
[Next_Premium_Due_Date] varchar (12) NULL,
[Next_Premium_Due_Amount] varchar (12) NULL,
[Scheduled_Premiums] varchar (12) NULL,
[Premiums_Paid] varchar (12) NULL,
[Insured_IssueAge] varchar (12) NULL,
[Insured_Sex] varchar (12) NULL,
[Insured_Class] varchar (12) NULL,
[Insured_Birthdate] varchar (12) NULL,
[2nd_Insured_IssueAge] varchar (12) NULL,
[2nd_Insured_Sex] varchar (12) NULL,
[2nd_Insured_Class] varchar (12) NULL,
[2nd_Insured_Birthdate] varchar (12) NULL,
[Riders_Count] varchar (12) NULL,
[Rider1_Type] varchar (12) NULL,
[Rider1_Code] varchar (12) NULL,
[Rider1_IssDate] varchar (12) NULL,
[Rider1_ExpDate] varchar (12) NULL,
[Rider1_Benefit] varchar (12) NULL,
[Rider1_Premium] varchar (12) NULL,
[Rider1_Period] varchar (12) NULL,
[Rider1_Status] varchar (12) NULL,
[Rider1_TermDate] varchar (12) NULL,
[Rider2_Type] varchar (12) NULL,
[Rider2_Code] varchar (12) NULL,
[Rider2_IssDate] varchar (12) NULL,
[Rider2_ExpDate] varchar (12) NULL,
[Rider2_Benefit] varchar (12) NULL,
[Rider2_Premium] varchar (12) NULL,
[Rider2_Period] varchar (12) NULL,
[Rider2_Status] varchar (12) NULL,
[Rider2_TermDate] varchar (12) NULL,
[Rider3_Type] varchar (12) NULL,
[Rider3_Code] varchar (12) NULL,
[Rider3_IssDate] varchar (12) NULL,
[Rider3_ExpDate] varchar (12) NULL,
[Rider3_Benefit] varchar (12) NULL,
[Rider3_Premium] varchar (12) NULL,
[Rider3_Period] varchar (12) NULL,
[Rider3_Status] varchar (12) NULL,
[Rider3_TermDate] varchar (12) NULL,
[Rider4_Type] varchar (12) NULL,
[Rider4_Code] varchar (12) NULL,
[Rider4_IssDate] varchar (12) NULL,
[Rider4_ExpDate] varchar (12) NULL,
[Rider4_Benefit] varchar (12) NULL,
[Rider4_Premium] varchar (12) NULL,
[Rider4_Period] varchar (12) NULL,
[Rider4_Status] varchar (12) NULL,
[Rider4_TermDate] varchar (12) NULL,
[Rider5_Type] varchar (12) NULL,
[Rider5_Code] varchar (12) NULL,
[Rider5_IssDate] varchar (12) NULL,
[Rider5_ExpDate] varchar (12) NULL,
[Rider5_Benefit] varchar (12) NULL,
[Rider5_Premium] varchar (12) NULL,
[Rider5_Period] varchar (12) NULL,
[Rider5_Status] varchar (12) NULL,
[Rider5_TermDate] varchar (12) NULL,
[Rider6_Type] varchar (12) NULL,
[Rider6_Code] varchar (12) NULL,
[Rider6_IssDate] varchar (12) NULL,
[Rider6_ExpDate] varchar (12) NULL,
[Rider6_Benefit] varchar (12) NULL,
[Rider6_Premium] varchar (12) NULL,
[Rider6_Period] varchar (12) NULL,
[Rider6_Status] varchar (12) NULL,
[Rider6_TermDate] varchar (12) NULL,
[Rider7_Type] varchar (12) NULL,
[Rider7_Code] varchar (12) NULL,
[Rider7_IssDate] varchar (12) NULL,
[Rider7_ExpDate] varchar (12) NULL,
[Rider7_Benefit] varchar (12) NULL,
[Rider7_Premium] varchar (12) NULL,
[Rider7_Period] varchar (12) NULL,
[Rider7_Status] varchar (12) NULL,
[Rider7_TermDate] varchar (12) NULL,
[Rider8_Type] varchar (12) NULL,
[Rider8_Code] varchar (12) NULL,
[Rider8_IssDate] varchar (12) NULL,
[Rider8_ExpDate] varchar (12) NULL,
[Rider8_Benefit] varchar (12) NULL,
[Rider8_Premium] varchar (12) NULL,
[Rider8_Period] varchar (12) NULL,
[Rider8_Status] varchar (12) NULL,
[Rider8_TermDate] varchar (12) NULL,
[Rider9_Type] varchar (12) NULL,
[Rider9_Code] varchar (12) NULL,
[Rider9_IssDate] varchar (12) NULL,
[Rider9_ExpDate] varchar (12) NULL,
[Rider9_Benefit] varchar (12) NULL,
[Rider9_Premium] varchar (12) NULL,
[Rider9_Period] varchar (12) NULL,
[Rider9_Status] varchar (12) NULL,
[Rider9_TermDate] varchar (12) NULL,
[Rider10_Type] varchar (12) NULL,
[Rider10_Code] varchar (12) NULL,
[Rider10_IssDate] varchar (12) NULL,
[Rider10_ExpDate] varchar (12) NULL,
[Rider10_Benefit] varchar (12) NULL,
[Rider10_Premium] varchar (12) NULL,
[Rider10_Period] varchar (12) NULL,
[Rider10_Status] varchar (12) NULL,
[Rider10_TermDate] varchar (12) NULL,
[Rider11_Type] varchar (12) NULL,
[Rider11_Code] varchar (12) NULL,
[Rider11_IssDate] varchar (12) NULL,
[Rider11_ExpDate] varchar (12) NULL,
[Rider11_Benefit] varchar (12) NULL,
[Rider11_Premium] varchar (12) NULL,
[Rider11_Period] varchar (12) NULL,
[Rider11_Status] varchar (12) NULL,
[Rider11_TermDate] varchar (12) NULL,
[Rider12_Type] varchar (12) NULL,
[Rider12_Code] varchar (12) NULL,
[Rider12_IssDate] varchar (12) NULL,
[Rider12_ExpDate] varchar (12) NULL,
[Rider12_Benefit] varchar (12) NULL,
[Rider12_Premium] varchar (12) NULL,
[Rider12_Period] varchar (12) NULL,
[Rider12_Status] varchar (12) NULL,
[Rider12_TermDate] varchar (12) NULL,
[Rider13_Type] varchar (12) NULL,
[Rider13_Code] varchar (12) NULL,
[Rider13_IssDate] varchar (12) NULL,
[Rider13_ExpDate] varchar (12) NULL,
[Rider13_Benefit] varchar (12) NULL,
[Rider13_Premium] varchar (12) NULL,
[Rider13_Period] varchar (12) NULL,
[Rider13_Status] varchar (12) NULL,
[Rider13_TermDate] varchar (12) NULL,
[Rider14_Type] varchar (12) NULL,
[Rider14_Code] varchar (12) NULL,
[Rider14_IssDate] varchar (12) NULL,
[Rider14_ExpDate] varchar (12) NULL,
[Rider14_Benefit] varchar (12) NULL,
[Rider14_Premium] varchar (12) NULL,
[Rider14_Period] varchar (12) NULL,
[Rider14_Status] varchar (12) NULL,
[Rider14_TermDate] varchar (12) NULL,
[Rider15_Type] varchar (12) NULL,
[Rider15_Code] varchar (12) NULL,
[Rider15_IssDate] varchar (12) NULL,
[Rider15_ExpDate] varchar (12) NULL,
[Rider15_Benefit] varchar (12) NULL,
[Rider15_Premium] varchar (12) NULL,
[Rider15_Period] varchar (12) NULL,
[Rider15_Status] varchar (12) NULL,
[Rider15_TermDate] varchar (12) NULL,
[Rider16_Type] varchar (12) NULL,
[Rider16_Code] varchar (12) NULL,
[Rider16_IssDate] varchar (12) NULL,
[Rider16_ExpDate] varchar (12) NULL,
[Rider16_Benefit] varchar (12) NULL,
[Rider16_Premium] varchar (12) NULL,
[Rider16_Period] varchar (12) NULL,
[Rider16_Status] varchar (12) NULL,
[Rider16_TermDate] varchar (12) NULL,
[Next_Rider_Premium_Date] varchar (12) NULL,
[Next_Rider_Premium_Amount] varchar (12) NULL,
[Rider_Premiums_Scheduled] varchar (12) NULL,
[Rider_Premiums_Paid] varchar (12) NULL,
[Expiry_Date] varchar (12) NULL,
[Insured_Last_Name] varchar (288) NULL,
[Insured_First_Name] varchar (288) NULL,
[Insured_SSN] varchar (288) NULL,
[Billing_Option] varchar (288) NULL,
[Bill_Address1] varchar (288) NULL,
[Bill_Address2] varchar (288) NULL,
[Bill_City] varchar (288) NULL,
[Bill_State] varchar (288) NULL,
[Bill_Zip] varchar (288) NULL,
[Bill_Phone] varchar (288) NULL
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

And some of it doesn't...is this true?

Does the data normally get loaded with quotes?

Yes, it's true. That was the 'developer's' version of a quick fix. To text delimit the add'l columns that possibly had bad data. He originally had double quotes, but I changed them to single quotes.|||OK, at a certain point (now would be good) you and your boss have to ask yourselves this question:
"How good is the software this guy is developing going to be if he can't perform such a simple task as exporting a standard text file?"

I, (and probably anybody else on this forum with any experience) can tell you that this turd is just going to stink more and more as the months wear on. It's going to cost you a helluva lot in maintenance costs if it turns out to be crappy software.

At least, that is what MY crystal ball is telling me...

As for your data, if you scrap the first line (headers) the rest is pure fixed-width with some superfluous commas tossed in. BCP should be able to handle it, but load it into a staging table and then strip off the trailing commas when you move it to your production table.

Want another quick and dirty solution? MS Access's wizard will import fixed width with ease, (you can even set it up to ignore the comma delimiters). For best results use an Access ADP project linked to your SQL Server, or just use an Access MDB database and link your server tables to it.|||EDIT: I attached the sample file to load from

After you build it you might need to ISDATE and ISNUMERIC to filter out garbage and or REPLACE to get rid of quotes...

Where in the world are you doing this, and take 5 advil

USE Northwind
GO

master..xp_cmdshell ' DIR D:\Tax\m*.*'

CREATE TABLE myTable99(Col1 varchar(8000))
GO

master..xp_cmdshell 'bcp Northwind.dbo.myTable99 in D:\Tax\melaticTestData.txt -SNJROS1D151\NJROS1D151DEV -Usa -P -c'
GO

SELECT LEN(Col1) FROM myTable99

INSERT INTO myTable99(Col1)
SELECT REPLICATE('1234567890',800) UNION ALL

SELECT REPLICATE( SPACE(9)+'1'+SPACE(9)+'2'+SPACE(9)+'3'+SPACE(9)+'4 '+SPACE(9)+'5'
+ SPACE(9)+'6'+SPACE(9)+'7'+SPACE(9)+'8'+SPACE(9)+'9 '+SPACE(9)+'0',80) UNION ALL

SELECT REPLICATE( SPACE(99)+'1'+SPACE(99)+'2'+SPACE(99)+'3'+SPACE(99 )+'4'+SPACE(99)+'5'
+ SPACE(99)+'6'+SPACE(99)+'7'+SPACE(99)+'8'+SPACE(99 )+'9'+SPACE(99)+'0',8) UNION ALL

SELECT SPACE(999)+'1'+SPACE(999)+'2'+SPACE(999)+'3'+SPACE (999)+'4'+SPACE(999)+'5'
+ SPACE(999)+'6'+SPACE(999)+'7'+SPACE(999)+'8'

SELECT * FROM myTable99

-- Then you could build a select statement

SELECT SUBSTRING(Col1,0001,001) AS [Product Type]
, SUBSTRING(Col1,0003,006) AS [Plan]
, SUBSTRING(Col1,0010,010) AS [Policay Number]
, SUBSTRING(Col1,0021,010) AS [ID]
-- ect
FROM myTable99
WHERE SUBSTRING(Col1,1,1) <> '1'
GO

DROP TABLE myTable99
GO

--Use The Column list (Hopefully the match the names of the table

-- Cut and paste the select for reference doen above after to help build the select|||Sorry, i was out sick. SICK I TELL YOU!!! :)

I'm happy now though, because I got that developer to start using vertical bars as column delimiters instead of commas. I persuaded him it was just better, and he happily obliged. Aaah, the power of communication.

So, thanks blindman and brett kaiser for you help. I adore both of you to pieces.|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".
A pipe-delimited developer ... I know the answer to this one!!!
Kris Kringle;)|||I prefer tab delimited|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...Heck, I'll take Tab every time! It is one of the few Coca-Cola products that I like!

-PatP|||If it's not ta kill ya, then it's Poland spring....

I actually pass it on the way to and from Sunday River|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".

I called it pipe delimited, and he calls it vertical bar delimited. He's a C++ developer, I don't know if that matters. Allz I know is, I'm a happy gal.

Again, thanks so much for your help. I would be a stressball if it weren't for the comfort of my expert friends on dbforums.

Only * (All columns) in EM Query builder when database name contains a . (dot)

I encountered a nasty bug in SQL Server 2000.
Symptoms:
When you try to create a query in the Query Builder of Enterprise
Manager, the selected tables are displayed without columns. Only *
(All Columns) is displayed. Besides that, the table receives an alias
with <table name>_1. You can create such a query by right-clicking on
a table in the database browser and selecting the menu option Open
Table - Query. Queries will run just fine, but you will need to type
column names yourself.
Analysis:
When investigating this, the Profile told me that sp_columns_ex was
executed when a table was added to the diagram pane. When I add Table1
in database test.dot to the diagram pane:
exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
The first parameter to this stored procedure is the name of a linked
server. Apparently, the name causes a misinterpretation of the SQL
statement,
SELECT * FROM Table1 Table1_1
that was created by the query builder.
If the SQL statement is modified to:
SELECT * FROM [test.com].[dbo].[table1] table1_1
brackets are removed automatically and upon execution, the error
message
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'test' in sysservers. Execute sp_addlinkedserver to add the server to
sysservers.
is given. A linked server with name test and a database with name dot
are searched.
Solution:
Do not use . (a dot) in a database name.You might not get much response because virtually nobody who really
knows SQL Server will have anything to do with the Query Builder. I've
never opened it in all the years since it became available, and those
who try it don't seem to use it for long.
Query Analyzer does not provide the sort of "help" that Query Builder
does, but at least we only have to deal with the problems we create
for ourselves.
Roy Harvey
Beacon Falls, CT
On 30 Mar 2007 05:02:44 -0700, "Roel Schreurs"
<roel.schreurs@.gmail.com> wrote:
>I encountered a nasty bug in SQL Server 2000.
>Symptoms:
>When you try to create a query in the Query Builder of Enterprise
>Manager, the selected tables are displayed without columns. Only *
>(All Columns) is displayed. Besides that, the table receives an alias
>with <table name>_1. You can create such a query by right-clicking on
>a table in the database browser and selecting the menu option Open
>Table - Query. Queries will run just fine, but you will need to type
>column names yourself.
>Analysis:
>When investigating this, the Profile told me that sp_columns_ex was
>executed when a table was added to the diagram pane. When I add Table1
>in database test.dot to the diagram pane:
>exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
>exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
>The first parameter to this stored procedure is the name of a linked
>server. Apparently, the name causes a misinterpretation of the SQL
>statement,
>SELECT * FROM Table1 Table1_1
>that was created by the query builder.
>If the SQL statement is modified to:
>SELECT * FROM [test.com].[dbo].[table1] table1_1
>brackets are removed automatically and upon execution, the error
>message
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
>'test' in sysservers. Execute sp_addlinkedserver to add the server to
>sysservers.
>is given. A linked server with name test and a database with name dot
>are searched.
>Solution:
>Do not use . (a dot) in a database name.|||On Mar 30, 7:54 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You might not get much response because virtually nobody who really
> knows SQL Server will have anything to do with the Query Builder. I've
> never opened it in all the years since it became available, and those
> who try it don't seem to use it for long.
> Query Analyzer does not provide the sort of "help" that Query Builder
> does, but at least we only have to deal with the problems we create
> for ourselves.
>
Not to mention, nobody who really knows SQL Server would include a dot
in an object name. Why would you do that?|||On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
>Not to mention, nobody who really knows SQL Server would include a dot
>in an object name. Why would you do that?
Good point! 8-)
Roy|||I had not expected any responses, just to be of help at sometime to
anyone who would care to search usenet for clues on this misbehaviour
of the Query Builder.
I am sure you are very clever folks and do not need something as dumb
as a query builder. Personally, I can create queries for analysis
purposes quicker using the Query Builder than the Query Analyzer,
although I never use it to build any stored procedures used by client
software.
Anyway, confronted with someone choosing an unfortunate database name,
containing a version number, this can be a annoying problem.
Not to mention, nobody who spends a lot of money buying Microsoft
products should be pleased with a product that is not fail safe...
On 30 mrt, 15:40, Roy Harvey <roy_har...@.snet.net> wrote:
> On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
> <tracy.mckib...@.gmail.com> wrote:
> >Not to mention, nobody who really knows SQL Server would include a dot
> >in an object name. Why would you do that?
> Good point! 8-)
> Roy

Only * (All columns) in EM Query builder when database name contains a . (dot)

I encountered a nasty bug in SQL Server 2000.
Symptoms:
When you try to create a query in the Query Builder of Enterprise
Manager, the selected tables are displayed without columns. Only *
(All Columns) is displayed. Besides that, the table receives an alias
with <table name>_1. You can create such a query by right-clicking on
a table in the database browser and selecting the menu option Open
Table - Query. Queries will run just fine, but you will need to type
column names yourself.
Analysis:
When investigating this, the Profile told me that sp_columns_ex was
executed when a table was added to the diagram pane. When I add Table1
in database test.dot to the diagram pane:
exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
The first parameter to this stored procedure is the name of a linked
server. Apparently, the name causes a misinterpretation of the SQL
statement,
SELECT * FROM Table1 Table1_1
that was created by the query builder.
If the SQL statement is modified to:
SELECT * FROM [test.com].[dbo].[table1] table1_1
brackets are removed automatically and upon execution, the error
message
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'test' in sysservers. Execute sp_addlinkedserver to add the server to
sysservers.
is given. A linked server with name test and a database with name dot
are searched.
Solution:
Do not use . (a dot) in a database name.
You might not get much response because virtually nobody who really
knows SQL Server will have anything to do with the Query Builder. I've
never opened it in all the years since it became available, and those
who try it don't seem to use it for long.
Query Analyzer does not provide the sort of "help" that Query Builder
does, but at least we only have to deal with the problems we create
for ourselves.
Roy Harvey
Beacon Falls, CT
On 30 Mar 2007 05:02:44 -0700, "Roel Schreurs"
<roel.schreurs@.gmail.com> wrote:

>I encountered a nasty bug in SQL Server 2000.
>Symptoms:
>When you try to create a query in the Query Builder of Enterprise
>Manager, the selected tables are displayed without columns. Only *
>(All Columns) is displayed. Besides that, the table receives an alias
>with <table name>_1. You can create such a query by right-clicking on
>a table in the database browser and selecting the menu option Open
>Table - Query. Queries will run just fine, but you will need to type
>column names yourself.
>Analysis:
>When investigating this, the Profile told me that sp_columns_ex was
>executed when a table was added to the diagram pane. When I add Table1
>in database test.dot to the diagram pane:
>exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
>exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
>The first parameter to this stored procedure is the name of a linked
>server. Apparently, the name causes a misinterpretation of the SQL
>statement,
>SELECT * FROM Table1 Table1_1
>that was created by the query builder.
>If the SQL statement is modified to:
>SELECT * FROM [test.com].[dbo].[table1] table1_1
>brackets are removed automatically and upon execution, the error
>message
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
>'test' in sysservers. Execute sp_addlinkedserver to add the server to
>sysservers.
>is given. A linked server with name test and a database with name dot
>are searched.
>Solution:
>Do not use . (a dot) in a database name.
|||On Mar 30, 7:54 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You might not get much response because virtually nobody who really
> knows SQL Server will have anything to do with the Query Builder. I've
> never opened it in all the years since it became available, and those
> who try it don't seem to use it for long.
> Query Analyzer does not provide the sort of "help" that Query Builder
> does, but at least we only have to deal with the problems we create
> for ourselves.
>
Not to mention, nobody who really knows SQL Server would include a dot
in an object name. Why would you do that?
|||On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:

>Not to mention, nobody who really knows SQL Server would include a dot
>in an object name. Why would you do that?
Good point! 8-)
Roy
|||I had not expected any responses, just to be of help at sometime to
anyone who would care to search usenet for clues on this misbehaviour
of the Query Builder.
I am sure you are very clever folks and do not need something as dumb
as a query builder. Personally, I can create queries for analysis
purposes quicker using the Query Builder than the Query Analyzer,
although I never use it to build any stored procedures used by client
software.
Anyway, confronted with someone choosing an unfortunate database name,
containing a version number, this can be a annoying problem.
Not to mention, nobody who spends a lot of money buying Microsoft
products should be pleased with a product that is not fail safe...
On 30 mrt, 15:40, Roy Harvey <roy_har...@.snet.net> wrote:
> On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
> <tracy.mckib...@.gmail.com> wrote:
> Good point! 8-)
> Roy

Only * (All columns) in EM Query builder when database name contains a . (dot)

I encountered a nasty bug in SQL Server 2000.
Symptoms:
When you try to create a query in the Query Builder of Enterprise
Manager, the selected tables are displayed without columns. Only *
(All Columns) is displayed. Besides that, the table receives an alias
with <table name>_1. You can create such a query by right-clicking on
a table in the database browser and selecting the menu option Open
Table - Query. Queries will run just fine, but you will need to type
column names yourself.
Analysis:
When investigating this, the Profile told me that sp_columns_ex was
executed when a table was added to the diagram pane. When I add Table1
in database test.dot to the diagram pane:
exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
The first parameter to this stored procedure is the name of a linked
server. Apparently, the name causes a misinterpretation of the SQL
statement,
SELECT * FROM Table1 Table1_1
that was created by the query builder.
If the SQL statement is modified to:
SELECT * FROM [test.com].[dbo].[table1] table1_1
brackets are removed automatically and upon execution, the error
message
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find se
rver
'test' in sysservers. Execute sp_addlinkedserver to add the server to
sysservers.
is given. A linked server with name test and a database with name dot
are searched.
Solution:
Do not use . (a dot) in a database name.You might not get much response because virtually nobody who really
knows SQL Server will have anything to do with the Query Builder. I've
never opened it in all the years since it became available, and those
who try it don't seem to use it for long.
Query Analyzer does not provide the sort of "help" that Query Builder
does, but at least we only have to deal with the problems we create
for ourselves.
Roy Harvey
Beacon Falls, CT
On 30 Mar 2007 05:02:44 -0700, "Roel Schreurs"
<roel.schreurs@.gmail.com> wrote:

>I encountered a nasty bug in SQL Server 2000.
>Symptoms:
>When you try to create a query in the Query Builder of Enterprise
>Manager, the selected tables are displayed without columns. Only *
>(All Columns) is displayed. Besides that, the table receives an alias
>with <table name>_1. You can create such a query by right-clicking on
>a table in the database browser and selecting the menu option Open
>Table - Query. Queries will run just fine, but you will need to type
>column names yourself.
>Analysis:
>When investigating this, the Profile told me that sp_columns_ex was
>executed when a table was added to the diagram pane. When I add Table1
>in database test.dot to the diagram pane:
>exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
>exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
>The first parameter to this stored procedure is the name of a linked
>server. Apparently, the name causes a misinterpretation of the SQL
>statement,
>SELECT * FROM Table1 Table1_1
>that was created by the query builder.
>If the SQL statement is modified to:
>SELECT * FROM [test.com].[dbo].[table1] table1_1
>brackets are removed automatically and upon execution, the error
>message
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find s
erver
>'test' in sysservers. Execute sp_addlinkedserver to add the server to
>sysservers.
>is given. A linked server with name test and a database with name dot
>are searched.
>Solution:
>Do not use . (a dot) in a database name.|||On Mar 30, 7:54 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You might not get much response because virtually nobody who really
> knows SQL Server will have anything to do with the Query Builder. I've
> never opened it in all the years since it became available, and those
> who try it don't seem to use it for long.
> Query Analyzer does not provide the sort of "help" that Query Builder
> does, but at least we only have to deal with the problems we create
> for ourselves.
>
Not to mention, nobody who really knows SQL Server would include a dot
in an object name. Why would you do that?|||On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:

>Not to mention, nobody who really knows SQL Server would include a dot
>in an object name. Why would you do that?
Good point! 8-)
Roy|||I had not expected any responses, just to be of help at sometime to
anyone who would care to search usenet for clues on this misbehaviour
of the Query Builder.
I am sure you are very clever folks and do not need something as dumb
as a query builder. Personally, I can create queries for analysis
purposes quicker using the Query Builder than the Query Analyzer,
although I never use it to build any stored procedures used by client
software.
Anyway, confronted with someone choosing an unfortunate database name,
containing a version number, this can be a annoying problem.
Not to mention, nobody who spends a lot of money buying Microsoft
products should be pleased with a product that is not fail safe...
On 30 mrt, 15:40, Roy Harvey <roy_har...@.snet.net> wrote:
> On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
> <tracy.mckib...@.gmail.com> wrote:
> Good point! 8-)
> Roy