More on querying remote server

i'll just keep posting the problems, as my own archive.
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
Issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT Tbl1001."TransactionGUID"
Col1003,Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
And tries to bring back 8.5M rows - and takes minutes to return, when really
there is only one row.
Question #1: Why doesn't it include the WHERE clause in the remote query?
If i change the query to:
SELECT TOP 1 TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
It still issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionGUID" Col1003,
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
But this time it only brings over 10 rows (somehow), performs a top 1
filter, and returns instantly.
Question #2: How is it bringing over 10 rows only? (hint: cursor)
If i change the query to:
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE TransactionNumber = 9679
It issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
which returns only one row, and returns instantly. Obviously SQL Server can
*sometimes* do the optimization, othertimes it won't.
Question #3: Why does it include the WHERE clause in the remote query.
If i change the query to:
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE TransactionDate = '2002-06-10 08:19:10.513'
It issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "cmsarchivetraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionDate"='2002-06-10T08:19:10.513'
)
Question #4: Why does it include the WHERE clause in the remote query?
Now i create a view:
CREATE VIEW CMSArchiveTranasctions AS
SELECT *
FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90
and issue the query:
SELECT TransactionNumber
FROM CMSArchiveTransactions
WHERE TransactionNumber = 9679
It wants to issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1005
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
And tries to bring back 8.5M rows - and takes minutes to return, when really
there is only one row.
Question #5: Why does it not include the WHERE clause in the remote query?
If i change the query to:
SELECT TOP 1 TransactionNumber
FROM CMSArchiveTransactions
WHERE TransactionNumber = 9679
it issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT TOP 1 Col1004
FROM (
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
) Qry1018
)
Question #6: Why is it now including the TOP inside the remote query, when
my earlier issue of a TOP query didn't include the TOP limiter?
Question #7: Why is it now including the WHERE clause in the remote query,
when not including a TOP 1 it won't include the where clause limiter?
Question #8: Why does it include the WHERE clause when i have any TOP
limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
where clause when i don't?
So i try changing my query to:
SELECT TransactionNumber
FROM (
SELECT *
FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90) t
WHERE TransactionNumber = 9679
And it issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
So querying through a view it will not include the WHERE clause, but if i
include the view as a derived table, it can include the WHERE clause.
Question #9: Why does it include the WHERE clause when i query through a
derived table, and not through a view?
So i try changing the query to
SELECT TransactionNumber
FROM (
SELECT *
FROM CMSArchiveTransactions) t
WHERE TransactionNumber = 9679
So here we are, pay attention to this one. If i attempt to query the view
directly, it doesn't get optimized. Now i am going to query my view THROUGH
a derived table. What do you think it will do?
SOURCE:(SERVERTEST),
QUERY:(
SELECT Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
It in fact now DOES include the where clause, but if i try to query my view
directly, it can't optimize it. This is a stunning development.
It means the rules are random, which makes it difficult to optimize
distributed queries.
Rules for including where clause:
Filter on uniqueidentifer:
Query includes TOP 1: Yes*
Query includes TOP 2: No
Query includes TOP n: No
Query includes TOP n PERCENT: No
Query doesn't include TOP: No
Filter on integer:
Query linked SQL Server: Yes
Query view that queries linked SQL Server:
Query view directly:
Query includes TOP 1: Yes
Query includes TOP 2: Yes
Query includes TOP 100: Yes
Query includes TOP 999999999: Yes
Query includes TOP 100 PERCENT: Yes
Query doesn't include TOP: No
Query view through derived table:
Query includes TOP 1: Yes
Query includes: TOP 2: Yes
Query includes: TOP n: Yes
Query includes: TOP 100 PERCENT: Yes
Query includes TOP: YesYou haven't posted any DDL. Are there any indexes on the remote table? Which
columns are indexed?
ML
http://milambda.blogspot.com/|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:E89D60ED-EAEF-46F9-9F31-1D56842B277E@.microsoft.com...
> You haven't posted any DDL. Are there any indexes on the remote table?
> Which
> columns are indexed?
Before answering that, let me ask you this: why does it matter? It doesn't
seem to matter *sometimes*. And even if there aren't an indexes on the
remote tables, everyone can agree that sending any filtering criteria to a
remote server is faster than trying to filter rows after they've crossed a
link.
Even a table scan on a remote server is better than bringing the rows over a
network to be filtered.
Additionally, my post is not dealing with query performance. When SQL Server
does what it should do, the queries run fine. If SQL Server doesn't do what
it's supposed to do, the queries do not run fine. Indexes are not the
performance limitation here.
That having been said: yes.|||If you think you know your data better than the optimizer does (as might be
the case - especially with outdated statistics), you could use the REMOTE
join hint to tell the optimizer to process the join on the remote server.
Basically, the optimizer can only rely on statistics. If they are missing on
the remote site, he prefers "staying local" although filtering data remotely
might have yielded better performance. Why put a stress on a remote server i
f
poor performance is expected anyway?
But when statistics are up to date on both sites, then the optimizer still
has full control over the execution of queries (unless specified otherwise
through the use of hints) which basically means that he can still make the
wrong assumptions - and I'm as much in the dark here as you are.
ML
http://milambda.blogspot.com/|||> Basically, the optimizer can only rely on statistics. If they are missing
> on
> the remote site, he prefers "staying local" although filtering data
> remotely
> might have yielded better performance. Why put a stress on a remote server
> if
> poor performance is expected anyway?
i guess this is where common sense gets to take a back seat.

> But when statistics are up to date on both sites, then the optimizer still
> has full control over the execution of queries (unless specified otherwise
> through the use of hints) which basically means that he can still make the
> wrong assumptions - and I'm as much in the dark here as you are.
Is there anyone who could explain the bewildering optimizer choices?|||Can anyone
ever imagine
any situation
under any circumstances
in any manner
in any capacity
in any way
with any product
running on any kind of data link
fast or slow
on any database setup
existing in any universe
moving at any velocity
at any time since the big bang itself
where it would be better to filter rows after fetching them?|||Here is some SQL to create a table named "Transactions", and will contain
some fields including "TransactionDate", "TransactionNumber",
"TransactionGUID"
USE pubs
go
DROP TABLE Transactions
go
CREATE Table Transactions (
[TransactionID] [int] NOT NULL IDENTITY,
TransactionGUID uniqueidentifier NOT NULL default newid(),
[title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price] [money] NULL ,
[advance] [money] NULL ,
[royalty] [int] NULL ,
[ytd_sales] [int] NULL ,
[notes] [varchar] (200) NULL ,
[TransactionDate] [datetime] NOT NULL DEFAULT (getdate())
)
go
INSERT INTO Transactions (title, type, pub_id, price, advance, royalty,
ytd_sales, notes, TransactionDate)
SELECT
[title],
[type],
[pub_id],
[price],
[advance],
[royalty],
[ytd_sales],
[notes],
CAST(CAST([pubdate] as real) + RAND(3234)*2000 - 1000 AS datetime)
FROM Titles
CROSS JOIN (
SELECT (a.Number * 256) + b.Number AS Number
FROM master..spt_values a,
master..spt_values b
WHERE a.Type = 'p'
AND b.Type = 'p') numbers
You'll have to create the linked server yourself, you'll need two servers.
You'll also have to substitute your own values for TransactionGUID,
TransactionDate, TransactionNumber|||I would hazard a guess that the local SQL server has no knowledge of the
statistics on the remote server and can only determine which query to send
across to the remote server based on the existence of keys (possibly indexes
or unique indexes).
The question would be what indexes exist on the remote table. I believe if
you look at these closely your results will turn out to be much more
consistent than you think.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:064A71B2-9B67-4F97-8635-6854A4B9551C@.microsoft.com...
> If you think you know your data better than the optimizer does (as might
be
> the case - especially with outdated statistics), you could use the REMOTE
> join hint to tell the optimizer to process the join on the remote server.
> Basically, the optimizer can only rely on statistics. If they are missing
on
> the remote site, he prefers "staying local" although filtering data
remotely
> might have yielded better performance. Why put a stress on a remote server
if
> poor performance is expected anyway?
> But when statistics are up to date on both sites, then the optimizer still
> has full control over the execution of queries (unless specified otherwise
> through the use of hints) which basically means that he can still make the
> wrong assumptions - and I'm as much in the dark here as you are.
>
> ML
> --
> http://milambda.blogspot.com/|||I am guessing to a fair extent here, but some possible (and I think likely)
explanations for this behavior...
The main thing is, I think, that SQL Server has no statistics available when
accessing a remote database and makes a best guess based on the existence of
keys.
Incidently, when working with Oracle 8 I found similar issues with their
"database links".

>Question #1: Why doesn't it include the WHERE clause in the remote query?
No Index on transactionGUID?

>Question #2: How is it bringing over 10 rows only? (hint: cursor)
10 rows is the size of the chunks retrieved. The top 1 is in the first ten
rows, so SQL Server doesnt bother retrieving any further data from the
remote database.

>Question #3: Why does it include the WHERE clause in the remote query.
Index or PK on TransactionNumber?

>Question #4: Why does it include the WHERE clause in the remote query?
Index or PK on TransactionDate?

>Question #5: Why does it not include the WHERE clause in the remote query?
Entire view is retrieved. The optimizer will sometimes do this with local
views as well, depending on how they are used.

>Question #6: Why is it now including the TOP inside the remote query, when
>my earlier issue of a TOP query didn't include the TOP limiter?
>Question #7: Why is it now including the WHERE clause in the remote query,
>when not including a TOP 1 it won't include the where clause limiter?
>Question #8: Why does it include the WHERE clause when i have any TOP
>limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
>where clause when i don't?
Top says return only X rows from the view. Because it needs to order the
results, a different execution plan is returned. This will happen with
local views also, depending on statistics and how they are used.

>Question #9: Why does it include the WHERE clause when i query through a
>derived table, and not through a view?
The local SQL server is rewriting your select to directly access the table.
This particular SQL may look different to a human, but logically, and to SQL
Server, it is identical to your 3rd example. This is perfectly consistent.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:O$7N6IZWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> i'll just keep posting the problems, as my own archive.
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
> Issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT Tbl1001."TransactionGUID"
> Col1003,Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> And tries to bring back 8.5M rows - and takes minutes to return, when
really
> there is only one row.
> Question #1: Why doesn't it include the WHERE clause in the remote query?
> If i change the query to:
> SELECT TOP 1 TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
> It still issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionGUID" Col1003,
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> But this time it only brings over 10 rows (somehow), performs a top 1
> filter, and returns instantly.
> Question #2: How is it bringing over 10 rows only? (hint: cursor)
> If i change the query to:
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE TransactionNumber = 9679
> It issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> which returns only one row, and returns instantly. Obviously SQL Server
can
> *sometimes* do the optimization, othertimes it won't.
> Question #3: Why does it include the WHERE clause in the remote query.
> If i change the query to:
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE TransactionDate = '2002-06-10 08:19:10.513'
> It issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "cmsarchivetraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionDate"='2002-06-10T08:19:10.513'
> )
> Question #4: Why does it include the WHERE clause in the remote query?
> Now i create a view:
> CREATE VIEW CMSArchiveTranasctions AS
> SELECT *
> FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90
> and issue the query:
> SELECT TransactionNumber
> FROM CMSArchiveTransactions
> WHERE TransactionNumber = 9679
> It wants to issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1005
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> And tries to bring back 8.5M rows - and takes minutes to return, when
really
> there is only one row.
> Question #5: Why does it not include the WHERE clause in the remote query?
> If i change the query to:
> SELECT TOP 1 TransactionNumber
> FROM CMSArchiveTransactions
> WHERE TransactionNumber = 9679
> it issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT TOP 1 Col1004
> FROM (
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> ) Qry1018
> )
> Question #6: Why is it now including the TOP inside the remote query, when
> my earlier issue of a TOP query didn't include the TOP limiter?
> Question #7: Why is it now including the WHERE clause in the remote query,
> when not including a TOP 1 it won't include the where clause limiter?
> Question #8: Why does it include the WHERE clause when i have any TOP
> limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
> where clause when i don't?
> So i try changing my query to:
> SELECT TransactionNumber
> FROM (
> SELECT *
> FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90) t
> WHERE TransactionNumber = 9679
> And it issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> So querying through a view it will not include the WHERE clause, but if i
> include the view as a derived table, it can include the WHERE clause.
> Question #9: Why does it include the WHERE clause when i query through a
> derived table, and not through a view?
> So i try changing the query to
> SELECT TransactionNumber
> FROM (
> SELECT *
> FROM CMSArchiveTransactions) t
> WHERE TransactionNumber = 9679
> So here we are, pay attention to this one. If i attempt to query the view
> directly, it doesn't get optimized. Now i am going to query my view
THROUGH
> a derived table. What do you think it will do?
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> It in fact now DOES include the where clause, but if i try to query my
view
> directly, it can't optimize it. This is a stunning development.
> It means the rules are random, which makes it difficult to optimize
> distributed queries.
>
> Rules for including where clause:
> Filter on uniqueidentifer:
> Query includes TOP 1: Yes*
> Query includes TOP 2: No
> Query includes TOP n: No
> Query includes TOP n PERCENT: No
> Query doesn't include TOP: No
> Filter on integer:
> Query linked SQL Server: Yes
> Query view that queries linked SQL Server:
> Query view directly:
> Query includes TOP 1: Yes
> Query includes TOP 2: Yes
> Query includes TOP 100: Yes
> Query includes TOP 999999999: Yes
> Query includes TOP 100 PERCENT: Yes
> Query doesn't include TOP: No
> Query view through derived table:
> Query includes TOP 1: Yes
> Query includes: TOP 2: Yes
> Query includes: TOP n: Yes
> Query includes: TOP 100 PERCENT: Yes
> Query includes TOP: Yes
>|||Absolutely. The same way there are cases when a full table scan is better
than using an index.
If you have 1,000 rows in a table and you add a filter on and indexed field
that returns 999 records, then SQL server will do a full table scan instead
of using the index.
If you have 1,000 rows in a remote table and you add a filter that returns
999 records, let the database that is actually going to use the data spend
the cycles filtering it. Let the remote server just do a quick IO and send
the data over the network with minimal CPU usage.
Now, if the local server has statistics on the tables in the remote
database, then it could make much better decisions regarding when to send
the filter and when to not. Because it doesn't know the remote database, it
has no idea how many rows will be returned (unless querying on a key field
maybe?) and makes a (rather useless) judgement call.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23fsEP7ZWGHA.2064@.TK2MSFTNGP03.phx.gbl...
> Can anyone
> ever imagine
> any situation
> under any circumstances
> in any manner
> in any capacity
> in any way
> with any product
> running on any kind of data link
> fast or slow
> on any database setup
> existing in any universe
> moving at any velocity
> at any time since the big bang itself
> where it would be better to filter rows after fetching them?
>sql

more info

If I just do a regular Subscription, allowing a new table
to be created a regular way, with the same schema as the
Publisher and creating its own procs and everything, the
snapshot looks alot different. When I do all my custom
stuff, the snapshot data file gets created with spaces in
the words. So "bla" becomes "b l a". When I don't do my
custom stuff, "bla" stays "bla". Don't know if this is
causing my prob, but thought it would be worth mentioning.

>--Original Message--
>sql2k sp3
>Howdy kids. Im using Custom Sync Objects for Replication.
>The Subscriber has a different schema than the Publisher.
>(more columns) So I use sp_addarticle to create the
>article, @.creation_script to create the table,
and "before
>applying the snapshot, apply this script" to create the
>insert Stored Proc. The snapshot runs. The table and proc
>get created correctly. (In the format of the Subscriber.)
>However, I still get an error:
>The process could not bulk copy into table '"transdtl"'.
>Unexpected EOF encountered in BCP data-file
>(Source: NECDEVSQL1 (ODBC); Error number: S1000)
>Below are the scripts neccessary to duplicate my
>environment.
>
>--sync view
>create view SyncTransDTL
>as select
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort = Convert(varchar(10), TransDate, 101),
> TransDateMonth = Month(TransDate),
> TransDateYear = Year(TransDate),
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort = Convert(varchar(10), PostDate, 101),
> PostDateMonth = Month(PostDate),
> PostDateYear = Year(PostDate),
> CreateDate ,
> MerchSIC
>from dbo.transdtl
>--article
>sp_addarticle @.publication = 'transdtl'
> , @.article = 'transdtl'
> , @.source_table = 'transdtl'
> , @.destination_table = 'transdtl'
> , @.type = 'logbased manualview'
> , @.sync_object = 'SyncTransDTL'
> , @.Creation_Script = '\\necdevsql2
>\d$\Replication\CreateTables.txt'
>,@.schema_option = 0x00
>,@.status = 8
>,@.ins_cmd = 'CALL sp_MSins_TransDTL'
>,@.del_cmd = 'CALL sp_MSdel_TransDTL'
>,@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
>
>--create table script
>CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
> CREATE CLUSTERED INDEX [IX_TransDtl_TransDate] ON
[dbo].
>[TransDtl]([TransDate]) WITH FILLFACTOR = 100 ON
[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_PostDate] ON [dbo].[TransDtl]
>([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_CustomerKey] ON [dbo].
>[TransDtl]([CustomerKey]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_RefNo] ON [dbo].[TransDtl]
>([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_SerialNbr] ON [dbo].[TransDtl]
>([SerialNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
>/****** The index created by the following statement is
>for internal use only. ******/
>/****** It is not a real index but exists as statistics
>only. ******/
>if (@.@.microsoftversion > 0x07000000 )
>EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
>[TransDtl] ([MerchSIC]) ')
>GO
>
>--insert proc script
>create procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
>as
>insert into TransDTL
>(
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
>)
>values
>(
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
>)
>.
>
Chris,
I used your script and after running sp_refreshsubscriptions everything
worked ok. Spaces get created between letters if I use native format, but
just to check if this was a problem, I tried once with native and once with
character format and both worked fine. I added the stored procedure to the
subscriber by hand, but apart from that it was all the same. Can you try
without the stored proc and use native format, just to check that the bcp is
the same style as the one you are used to? If this still errors, please can
you export some data to a csv file and post it up, just so I am using more
or less the same data.
TIA,
Paul Ibison
|||Paul as usual I appreciate your insights. Character format
wont work as then the option "before applying the
snapshot, exec this script" is not available. Which made
me realize, I also didnt post the schema for the Publisher:
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TransDtl] WITH NOCHECK ADD
CONSTRAINT [PK_TransDtl] PRIMARY KEY CLUSTERED
(
[TransDtlKey]
) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TransDtl] ADD
CONSTRAINT [DF_TransDtl_CreateDate] DEFAULT
(getdate()) FOR [CreateDate]
GO
CREATE INDEX [IX_TransDtl_PostDate] ON [dbo].[TransDtl]
([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl_SerialNbr] ON [dbo].[TransDtl]
([SerialNbr]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl_TransDate_TranCode] ON [dbo].
[TransDtl]([TransDate], [TranCode]) WITH FILLFACTOR = 95
ON [PRIMARY]
GO

>--Original Message--
>Chris,
>I used your script and after running
sp_refreshsubscriptions everything
>worked ok. Spaces get created between letters if I use
native format, but
>just to check if this was a problem, I tried once with
native and once with
>character format and both worked fine. I added the stored
procedure to the
>subscriber by hand, but apart from that it was all the
same. Can you try
>without the stored proc and use native format, just to
check that the bcp is
>the same style as the one you are used to? If this still
errors, please can
>you export some data to a csv file and post it up, just
so I am using more
>or less the same data.
>TIA,
>Paul Ibison
>
>.
>
|||Paul attached is the data Im using. Ive also attached the snapshot file
which I noticed only includes one of the two rows of data in the table.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239dkpX%23gEHA.592@.TK2MSFTNGP11.phx.gbl...
> Chris,
> I used your script and after running sp_refreshsubscriptions everything
> worked ok. Spaces get created between letters if I use native format, but
> just to check if this was a problem, I tried once with native and once
with
> character format and both worked fine. I added the stored procedure to the
> subscriber by hand, but apart from that it was all the same. Can you try
> without the stored proc and use native format, just to check that the bcp
is
> the same style as the one you are used to? If this still errors, please
can
> you export some data to a csv file and post it up, just so I am using more
> or less the same data.
> TIA,
> Paul Ibison
>
begin 666 Transdtl.CSV
M,2PQ+#$@.(" @.(" @.(" L=&5S="PR,# T+3 Q+3 Q(# P.C P.C P+#$N,# P
M,"QT97-T(" @.(" @.(" @.(" @.(" @.(" @.("QT97-T+'1E<W0L=&5S+'1E<W0L
M,C P-"TP,2TP,2 P,#HP,#HP,"XP,# L,C P-"TP,2TP,2 P,#HP,#HP,"XP
M,# L=&5S= T*,BPQ+#$@.(" @.(" @.(" L8FQA("PR,# T+3 Q+3 Q(# P.C P
M.C P+#$N,# P,"QT97-T(" @.(" @.(" @.(" @.(" @.(" @.("QT97-T+'1E<W0L
M=&5S+'1E<W0L,C P-"TP,2TP,2 P,#HP,#HP,"XP,# L,C P-"TP,2TP,2 P
2,#HP,#HP,"XP,# L=&5S= T*
`
end
begin 666 transdtl_0.bcp
M`0````0!````,0`@.`" `( `@.`" `( `@.`" `( !T`&4`<P!T`&&4```4`# `
M,0`O`# `,0`O`#(`, `P`#0`! $````$U <````````0)P``= !E`',`= `@.
M`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.``@.`= !E`',`
M= `(`'0`90!S`'0`!@.!T`&4`<P`(`'0`90!S`'0`890````````4 `# `,0`O
M`# `,0`O`#(`, `P`#0`! $````$U <``&&4````````" !T`&4`<P!T``(`
M```$`0```#$`( `@.`" `( `@.`" `( `@.`" `8@.!L`&$`( !AE ``% `P`#$`
M+P`P`#$`+P`R`# `, `T``0!````!-0'````````$"<``'0`90!S`'0`( `@.
M`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `(`'0`90!S`'0`
M" !T`&4`<P!T``8`= !E`',`" !T`&4`<P!T`&&4````````% `P`#$`+P`P
I`#$`+P`R`# `, `T``0!````!-0'``!AE ````````@.`= !E`',`= ``
`
end

moniker for own SSIS task

How can i find out the moniker for my own ssis task?

(Assuming you are using C#, VB.NET or other .NET language)

The moniker is assembly-qualified type name of your task class, same as here

http://msdn2.microsoft.com/en-us/library/system.type.assemblyqualifiedname.aspx

This article might also help:

http://msdn2.microsoft.com/en-us/library/2exyydhb.aspx

|||thanks

MOM blocking analysis

MOM contains its own Block Analysis script. It is a script written on
VB, that looks for the monitored servers, creates for each one
MomCreateObject("SQLDMO.SQLServer"), afterwards connects to each
database on each server and executes SELECT GETDATE() query. If MOM
doesn't receive answer for 6 minutes, it sends alert. Several times
since I started to monitor my servers I received the next alert:

The program "SQLDMO_789" has been blocked for 6 minutes on database
BurstingDataWarehouse in the SQL instance MSSQLSERVER. The defined
acceptable blocking threshold is 1 minute(s). "SQLDMO_789" is running
on SPID 134 as login NT AUTHORITY\SYSTEM and is blocked by SPID 133.
The resource id is KEY: 10:2:1 (a2007950f190)

SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -
is BurstingDataWarehouse database. As far as I can judge, MOM connects
to the server succesfully (otherwise, how can it know SPID?) The server
itself worked fine at that time - nothing unusual, all the jobs
finished succesfully including the heavy ones. What can block SELECT
GETDATE() query for 6 minutes?I had a similar issue and it turned out that some scrappy application
held thousands of locks on the tempdb. Run sp_who2 in combination with
sp_lock to see which process is blocking the MOM agent.

M