Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
Andrew
Just a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one case table allowed for Time series algortihm?

Hi, all experts here,

Thank you very much for your kind attention.

I just encountered the problem that with Microsoft Time Series algorithm, no more than one case table is allowed? Mining structure is not allowed to contain any nested table?

Or did I miss out anything there? Thank you very much in advance for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Just found that we can actually use nested table as input column for Time Series algorithm? So what is the problem then?

Thanks again and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||You can use a case table and a nested table for the TS algorithm. Can you please explain in more details what the schema of your mining structure (case table, nested table) looks like.

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