Continuously querying a datastream using SQL Server 2005

I was wondering if it is possible to continuously query a real-time
datastream using SQL Server. Does anyone have any experience of this? I
have found LINUX based systems such as Borealis and STREAM. I would
prefer to use a Windows based system as the program using the query
results is Windows based.paulb (paulbermingham@.gmail.com) writes:

Quote:

Originally Posted by

I was wondering if it is possible to continuously query a real-time
datastream using SQL Server. Does anyone have any experience of this? I
have found LINUX based systems such as Borealis and STREAM. I would
prefer to use a Windows based system as the program using the query
results is Windows based.

What more exactly what you want to achieve? Query notification is the first
thing that comes to mind, but it may not be exactly what you are looking
for.

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

Query Notifications look like they would do the job alright but it
would mean writing each new piece of data to a table, querying it, and
then pulling the result back into my program. If this is the only
solution then I would probably be better dealing with the data-stream
within the program as speed is a huge factor and recording the data is
a secondary concern. My main question was whether there is specific
functionality within SQL Server to deal with real time analysis of data
streams?

Thanks,

Paul.

Erland Sommarskog wrote:

Quote:

Originally Posted by

paulb (paulbermingham@.gmail.com) writes:

Quote:

Originally Posted by

I was wondering if it is possible to continuously query a real-time
datastream using SQL Server. Does anyone have any experience of this? I
have found LINUX based systems such as Borealis and STREAM. I would
prefer to use a Windows based system as the program using the query
results is Windows based.

>
What more exactly what you want to achieve? Query notification is the first
thing that comes to mind, but it may not be exactly what you are looking
for.
>
>
--
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

|||paulb (paulbermingham@.gmail.com) writes:

Quote:

Originally Posted by

Query Notifications look like they would do the job alright but it
would mean writing each new piece of data to a table, querying it, and
then pulling the result back into my program. If this is the only
solution then I would probably be better dealing with the data-stream
within the program as speed is a huge factor and recording the data is
a secondary concern.

Well, the main purpose of a database engine is to record the data. To me
it sounds like you are looking for a solution without an RDBMS.

--
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|||Well, I thought SQL Server may have some real time data analysis
capability. Obviously it is not equiped for this. I guess I'll have to
stick to the LINUX based packages I am currently using. Probably faster
anyway.

Thanks,

Paul.

Erland Sommarskog wrote:

Quote:

Originally Posted by

paulb (paulbermingham@.gmail.com) writes:

Quote:

Originally Posted by

Query Notifications look like they would do the job alright but it
would mean writing each new piece of data to a table, querying it, and
then pulling the result back into my program. If this is the only
solution then I would probably be better dealing with the data-stream
within the program as speed is a huge factor and recording the data is
a secondary concern.

>
Well, the main purpose of a database engine is to record the data. To me
it sounds like you are looking for a solution without an RDBMS.
>
>
--
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

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas

"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff
choppertoo
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message555779.html

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaf
-
chopperto
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message555779.htm

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff