More questions about porting from MySQL to MS SQL

1) In several tables, in my MySQL version, I created columns using
something like the following:

`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,

This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)

2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.

3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.

Thanks

TedTed wrote:

Quote:

Originally Posted by

1) In several tables, in my MySQL version, I created columns using
something like the following:
>
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
>
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)
>
2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.
>
3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.
>
Thanks
>
Ted

1) You can use an UPDATE trigger to simulate the same functionality.
Usually though it is better to use stored procedures to perform all
your data access. That way you can easily include the timestamp as part
of your update procs.

2) You don't need separate scripts for each View / Function. You do
need separate batches. A batch is separated using the GO keyword in
Query Analyzer.

3) Don't confuse TIMESTAMP with DATETIME. They are not at all the same!
You are referring to DATETIME values, which ARE permitted as parameters
and return values in functions. BTW, you don't need to write a function
to do date arithmetic - it already exists as a built-in function:
DATEADD().

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

1) In several tables, in my MySQL version, I created columns using
something like the following:
>
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
>
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it?

You will have to modify all that code. To have to be portable is indeed
painful, and a good start is to pay attention to what is in ANSI standards,
what is commonly supported. And not to the least to what are extensions
added by a certain vendor. My knowledge of ANSI and other engines are
poor (since I live in a sheltered world where I only need to support
SQL Server), but I would suspect that the ON UPDATE clause for the
default constraint is properitary to MySQL.

The way to do this in SQL Server is to use a trigger. Not that triggers
usually are very portable...

You can also modify the UPDATE statements so that they read:

UPDATE tbl
SET ...,
ab_timestamp = DEFAULT,
WHERE ...

I believe this syntax is portable.

Quote:

Originally Posted by

(Yes, I know 'timestamp' is deprecated in MS SQL and that I should use
datetime instead, and in fact have already done so.)

timestamp is not deprecated in SQL Server, but it's a completely different
data type, which is used to implement optimistic locking. A timestamp
is an 8-bit value is updated each time the row is updated and it's
unique within the database. Further more timestamp value are monotonically
increasing. But there is no correlation with time. Timestamp is
proprietary to SQL Server, so you should probably stay away from it
entirely.

Quote:

Originally Posted by

2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.

Not a separate script, but a separate batch. Batches are separated with
"go" in all query tools. The graphic tools permit you to specify a
different batch separator.

Why? Because else it would be difficult to tell where a procedure ends.
Say that you have:

CREATE PROCEDURE ... AS
...
CREATE TABLE ...

Is that CREATE TABLE part of the procedure or not? (Yes, if you have
BEGIN END it's clear. But of legacy BEGIN END is not required in
stored procedures.)

Quote:

Originally Posted by

3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.

As noted above, there is little reason for your to use the timestamp
data type.

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

More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisRChris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?
>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
>> sql2k sp3
>> Im having a really hard time getting my head into
>> Materialized Views.
>> Accoring to BOL:
>> When a unique clustered index is created on a view, the
>> view is executed and the result set is stored in the
>> database in the same way a table with a clustered index
is
>> stored.
>>
>> Now this sounds good and makes sense. However, say I
have
>> a Materialized View:
>> create view MatTest
>> as
>> select * from table1
>> Create unique clustered index... on MatTest(Column1)
>>
>> So, if Im reading BOL correctly, the results are stored
in
>> the DB already. But then what happens when I am
selecting
>> from my view and put a WHERE clause on the SELECT?
>> select * from MatTest
>> where column1 = bla
>> and column2 = bla
>> Wouldn't the view then have to rebuild the results just
>> like a normal query would?
>> Also, (dont know if I would do this one, just curious)
>> what if I want to use Dynamic SQL to call my view from
an
>> SP and be able to feed in Parameters? Is the clustered
>> index still used?
>>
>> Thanks to all for your insights.
>> TIA, ChrisR
>
>.
>|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> >--Original Message--
> >Chris,
> >
> >Let's say we have the following non-materialized view:
> >
> >CREATE VIEW NonMaterialized
> >AS
> > SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> >
> >When you run a query against the view, e.g.:
> >
> >SELECT *
> >FROM NonMaterialized
> >WHERE ColA = 5
> >
> >What really happens is that your query is re-written
> first as:
> >
> >SELECT *
> >FROM
> > (SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
> >WHERE ColA = 5
> >
> >And then most likely optimized into:
> >
> >SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> > WHERE ColA = 5
> >
> >So using this view, you still do all of the work, at
> query time, as you
> >would selecting from the base tables.
> >
> >Now, assume that we've materialized the view. SQL Server
> now stores the
> >entire results of the view in the same way that it stores
> table data. So
> >when you exercise this query:
> >
> >SELECT *
> >FROM Materialized
> >WHERE ColA = 5
> >
> >There are no base tables to JOIN. This query can use
> indexes directly
> >against the view, as if it were a base table itself.
> >
> >Does that make more sense?
> >
> >
> >"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Im having a really hard time getting my head into
> >> Materialized Views.
> >>
> >> Accoring to BOL:
> >>
> >> When a unique clustered index is created on a view, the
> >> view is executed and the result set is stored in the
> >> database in the same way a table with a clustered index
> is
> >> stored.
> >>
> >>
> >> Now this sounds good and makes sense. However, say I
> have
> >> a Materialized View:
> >>
> >> create view MatTest
> >> as
> >> select * from table1
> >>
> >> Create unique clustered index... on MatTest(Column1)
> >>
> >>
> >> So, if Im reading BOL correctly, the results are stored
> in
> >> the DB already. But then what happens when I am
> selecting
> >> from my view and put a WHERE clause on the SELECT?
> >>
> >> select * from MatTest
> >> where column1 = bla
> >> and column2 = bla
> >>
> >> Wouldn't the view then have to rebuild the results just
> >> like a normal query would?
> >>
> >> Also, (dont know if I would do this one, just curious)
> >> what if I want to use Dynamic SQL to call my view from
> an
> >> SP and be able to feed in Parameters? Is the clustered
> >> index still used?
> >>
> >>
> >> Thanks to all for your insights.
> >>
> >> TIA, ChrisR
> >>
> >
> >
> >.
> >

More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisR
Chris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>
|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?

>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
is[vbcol=seagreen]
have[vbcol=seagreen]
in[vbcol=seagreen]
selecting[vbcol=seagreen]
an
>
>.
>
|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView
|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...[vbcol=seagreen]
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> first as:
> query time, as you
> now stores the
> table data. So
> indexes directly
> message
> is
> have
> in
> selecting
> an

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

More efficient than LEFT JOIN

I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. I'm looking for an
efficient way to select only the active data.

Currently I use:

SELECT ...
FROM DataTable AS D
LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
WHERE D.Key IS NULL

However I am not convinced that this is the most efficient, or the most
intuitive method of acheiving this.

Can anyone suggest a more efficient way of getting this information
please.

Many thanks.

*** Sent via Developersdex http://www.developersdex.com ***Hi, Brian

I think that you wanted to write "WHERE I.Key IS NULL" (instead of
"WHERE D.Key IS NULL"). In this case, you can use something like this:

SELECT ...
FROM DataTable WHERE Key NOT IN (
SELECT Key FROM InactiveTable
)

Razvan

PS. I assume that the "Key" column does not allow NULL-s.|||On Wed, 15 Feb 2006 12:41:09 GMT, Brian Wotherspoon wrote:

>
>I have a table with data that is refreshed regularly but I still need to
>store the old data. I have created a seperate table with a foreign key
>to the table and the date on which it was replaced. I'm looking for an
>efficient way to select only the active data.
>Currently I use:
>SELECT ...
>FROM DataTable AS D
>LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
>WHERE D.Key IS NULL
>However I am not convinced that this is the most efficient, or the most
>intuitive method of acheiving this.
>Can anyone suggest a more efficient way of getting this information
>please.
>Many thanks.

Hi Brian,

The most intuitive way, IMO, is

SELECT ...
FROM DataTable AS D
WHERE NOT EXISTS
(SELECT *
FROM InActiveTable AS I
WHERE I.Key = D.Key)

The most efficient is either the above or your own LEFT OUTER JOIN query
(but do change D.Key to I.Key in the IS NOT NULL check!!) - but it'll be
only efficient if the I.Key column is indexed.

--
Hugo Kornelis, SQL Server MVP|||try to bring it up a level.

for instance, you are probably creating a temp table? Perhaps create
the temp table with closer to the data you really need.
if you are looking for only one cusotmer, then only pull that one
customer. or, for a specfiic time period, then only that time period.

also, make sure you have an index on inactivetable.key.

if you knew that inactivetable started at some timeframe for all
records, then you could create a composite index on
inactivedata.timestamp plus key.

would it be worth putting an "inactive data datestamp" at the customer
level? perhaps if you have enough rows.

Monitoring cube aggregations.

Hi All,Can some one explain me the process of monitoring the aggregations created during the cube design are being used while running a MDX , for example aggregations are created during the design process of adventure works DW cube, I would like to verify those aggregations are being used when I fire a query against a particular measure in a measure group for which aggregations are created.Thanks

It is mentioned in Teo Lachev's book (Applied Microsoft Analysis Services p.460):

"You can use SQL Server Profiler to determine if a given query is satisfied from the cube aggregation or not. However the SQL Server Profiler won't tell you why the server has decided to use a particular aggregation. If the server didn't use aggregations, the Progess Report event will show 'Start reading data from the Partition Name partition'. Otherwise the report will say 'Start reading from the Aggregation Name aggregation'."

Hope this answers your question

Christina

|||Thanks Christina,I got the answer from your message.

Monitoring Clustered SQL Server

I am attempting to montior failovers, using Sitescope, so does anyone know
event what events are created when a node fails over?
Dave B
The SDK for Windows Clustering should give you a few.
http://msdn.microsoft.com/library/de...start_page.asp I
am not a developer, so I can't help you anymore with the SDK.
You can also create a failure and use the ones you want from that wonderful
experience.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:5C9DDBD9-4FF8-461D-BAEE-8C480EA9A713@.microsoft.com...
>I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
> --
> Dave B
|||Dave B wrote:
> I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
We use nagios to monitor if the sql service is running on the prefered
node. If not it must have failed over. sitescope can monitor services too.
Hans

Monitor user changes

I need to monitor sql user additions and changes and put login name and
change date into a user created table.
Is ther anyway I can do this from a trigger or sp.
Regards
Don GroverYou can use a trigger on the tables you want to audit. Just
look at the values in the deleted and inserted tables used
by the triggers for before and after images of the rows. You
can capture the date with GetDate() and the user with
suser_sname. And then just insert that data into your audit
table.
You can find an example of this in the following article:
http://www.aspfaq.com/show.asp?id=2448
-Sue
On Thu, 10 Feb 2005 07:53:50 +1100, "Don Grover"
<dgrover@.assoft.com.au> wrote:

>I need to monitor sql user additions and changes and put login name and
>change date into a user created table.
>Is ther anyway I can do this from a trigger or sp.
>Regards
>Don Grover
>|||Sorry Sue
I actually mean't users logins, not user data
ie: I actually want to monitor if any more users have been add, deleted or
edited on/to any database.
I have this msql server that I need to monitor remotely by script/email when
any user/login changes are made
Regards
Don
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
> You can use a trigger on the tables you want to audit. Just
> look at the values in the deleted and inserted tables used
> by the triggers for before and after images of the rows. You
> can capture the date with GetDate() and the user with
> suser_sname. And then just insert that data into your audit
> table.
> You can find an example of this in the following article:
> http://www.aspfaq.com/show.asp?id=2448
> -Sue
> On Thu, 10 Feb 2005 07:53:50 +1100, "Don Grover"
> <dgrover@.assoft.com.au> wrote:
>
>|||Don
SQL Server does not have a track of this.
Look at SQL Server Profiler to do it for you.
"Don Grover" <dgrover@.assoft.com.au> wrote in message
news:%23KamiQzDFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Sorry Sue
> I actually mean't users logins, not user data
> ie: I actually want to monitor if any more users have been add, deleted or
> edited on/to any database.
> I have this msql server that I need to monitor remotely by script/email
when
> any user/login changes are made
> Regards
> Don
>
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
>|||Look at the Security Audit Events classes in profiler. You
can capture these changes using Profiler (or a server side
trace) using the Security Audit event category.
-Sue
On Thu, 10 Feb 2005 16:48:21 +1100, "Don Grover"
<dgrover@.assoft.com.au> wrote:

>Sorry Sue
>I actually mean't users logins, not user data
>ie: I actually want to monitor if any more users have been add, deleted or
>edited on/to any database.
>I have this msql server that I need to monitor remotely by script/email whe
n
>any user/login changes are made
>Regards
>Don
>
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
>