Comma Delimited Fields: common

Showing posts with label common. Show all posts

Sunday, March 11, 2012

Compare Script

I need to write a compare script to compare the data that two databases may have in common and to delete the records in the first database. And the records are are not incommon to be moved over to the second database.
Thanks
Lystrahttp://www.sqlscripter.com/|||Thanks, but theres resistions on my PC

I was able to write this:

Select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
From IISLOG.dbo.IISLOG as a where NOT EXISTS
(select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
from IISLOG as b
Where a.ClientHost =b.ClientHost
and a.Username = b.Username
and a.Logtime = b.Logtime
and a.Service = b.Service
and a.Machine = b.Machine
and a.ServerIP = b.ServerIP
and a.ProcessingTime = b.ProcessingTime
and a.BytesRecvd = b.BytesRecvd
and a.BytesSent = b.BytesSent
and a.serviceStatus = b.ServiceStatus
and a.Win32Status = b.Win32Status
and a.Operation = b.Operation
and a.Target = b.Target
and a.Parameters = b.Parameters
and a.Department = b.Department)|||Don't you have a primary key?|||That's the problem because once the data is archive the Id which is the primary key is not archive. But it does have a ID field in the second database. It kind of like ID autonumber in MS access.

This is the code it uses:

CREATE PROCEDURE sp_archiveLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
copy updated rows to archive database
*/
insert IISLOG_ARCHIVE2004..iislog
select clienthost,username,logtime,service,machine,server ip,processingtime,bytesrecvd,bytessent,servicestat us,win32status,operation,target,parameters,departm ent from IISLOG..iislog
where department is not null
and ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )
GO

As you see it doesn't archive the id number

The next code deletes the whole record

CREATE PROCEDURE sp_cleanupLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
delete rows from current iislog table
*/
delete from IISLOG..iislog
where ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )

So for me to compare I would have to compare the whole field.

Lystra

Friday, February 24, 2012

common UPDATE syntax for SqlServer and Oracle

The UPDATE table FROM syntax is not supported by Oracle.

I am looking for a syntax that is understood by both Oracle and SqlServer.

Example:

Table1:

id name city city_id
1 john newyork null
2 peter london null
3 hans newyork null

Table2:

id city
23 london
24 paris
25 newyork

UPDATE table1
SET city_id = table2.id
FROM table1, table2
WHERE table1.city = Table2.city

If possible I do not want to have two different statements for Oracle and
SqlServer

Please do not tell me that these tables are not normalized, it's just an
example!

Thanks for any hints.

Jan van VeldhuizenThe ANSI Standard syntax supported by both products is

UPDATE Table1
SET city_id =
(SELECT T2.id
FROM Table2 AS T2
WHERE T2.city = Table1.city) ;

Depending on requirements you may want to include a WHERE EXISTS (equivalent
to the proprietary INNER JOIN syntax)

UPDATE Table1
SET city_id =
(SELECT T2.id
FROM Table2 AS T2
WHERE T2.city = Table1.city)
WHERE EXISTS
(SELECT *
FROM Table2 AS T2
WHERE T2.city = Table1.city) ;

--
David Portas
SQL Server MVP
--|||Like most new ideas, the hard part of understanding what the relational
model is comes in un-learning what you know about file systems. As
Artemus Ward (William Graham Sumner, 1840-1910) put it, "It ain't so
much the things we don't know that get us into trouble. It's the things
we know that just ain't so."

If you already have a background in data processing with traditional
file systems, the first things to un-learn are:

(0) Databases are not file sets.
(1) Tables are not files.
(2) Rows are not records.
(3) Columns are not fields.

Modern data processing began with punch cards, or Hollerith cards used
by the Bureau of the Census. Their original size was that of a United
States Dollar bill. This was set by their inventor, Herman Hollerith,
because he could get furniture to store the cards from the United States
Treasury Department, just across the street. Likewise, physical
constraints limited each card to 80 columns of holes in which to record
a symbol.

The influence of the punch card lingered on long after the invention of
magnetic tapes and disk for data storage. This is why early video
display terminals were 80 columns across. Even today, files which were
migrated from cards to magnetic tape files or disk storage still use 80
column records.

But the influence was not just on the physical side of data processing.
The methods for handling data from the prior media were imitated in the
new media.

Data processing first consisted of sorting and merging decks of punch
cards (later, sequential magnetic tape files) in a series of distinct
steps. The result of each step feed into the next step in the process.

Relational databases do not work that way. Each user connects to the
entire database all at once, not to one file at time in a sequence of
steps. The users might not all have the same database access rights
once they are connected, however. Magnetic tapes could not be shared
among users at the same time, but shared data is the point of a
database.

Tables versus Files

A file is closely related to its physical storage media. A table may or
may not be a physical file. DB2 from IBM uses one file per table,
while Sybase puts several entire databases inside one file. A table is
a set of rows of the same kind of thing. A set has no ordering
and it makes no sense to ask for the first or last row.

A deck of punch cards is sequential, and so are magnetic tape files.
Therefore, a physical file of ordered sequential records also
became the mental model for data processing and it is still hard
to shake. Anytime you look at data, it is in some physical ordering.

The various access methods for disk storage system came later, but even
these access methods could not shake the mental model.

Another conceptual difference is that a file is usually data that deals
with a whole business process. A file has to have enough data in
itself to support applications for that business process. Files tend to
be "mixed" data which can be described by the name of the business
process, such as "The Payroll file" or something like that.

Tables can be either entities or relationships within a business
process. This means that the data which was held in one file is often
put into several tables. Tables tend to be "pure" data which can be
described by single words. The payroll would now have separate tables
for timecards, employees, projects and so forth.

Tables as Entities

An entity is physical or conceptual "thing" which has meaning be itself.
A person, a sale or a product would be an example. In a relational
database, an entity is defined by its attributes, which are shown as
values in columns in rows in a table.

To remind users that tables are sets of entities, I like to use plural
or collective nouns that describe the function of the entities within
the system for the names of tables. Thus "Employee" is a bad name
because it is singular; "Employees" is a better name because it is
plural; "Personnel" is best because it is collective and does not summon
up a mental picture of individual persons.

If you have tables with exactly the same structure, then they are sets
of the same kind of elements. But you should have only one set for each
kind of data element! Files, on the other hand, were PHYSICALLY
separate units of storage which coudl be alike -- each tape or disk file
represents a step in the PROCEDURE , such as moving from raw data, to
edited data, and finally to archived data. In SQL, this should be a
status flag in a table.

Tables as Relationships

A relationship is shown in a table by columns which reference one or
more entity tables. Without the entities, the relationship has no
meaning, but the relationship can have attributes of its own. For
example, a show business contract might have an agent, an employer and
a talent. The method of payment is an attribute of the contract itself,
and not of any of the three parties.

Rows versus Records

Rows are not records. A record is defined in the application program
which reads it; a row is defined in the database schema and not by a
program at all. The name of the field in the READ or INPUT statements
of the application; a row is named in the database schema.

All empty files look alike; they are a directory entry in the operating
system with a name and a length of zero bytes of storage. Empty tables
still have columns, constraints, security privileges and other
structures, even tho they have no rows.

This is in keeping with the set theoretical model, in which the empty
set is a perfectly good set. The difference between SQL's set model and
standard mathematical set theory is that set theory has only one empty
set, but in SQL each table has a different structure, so they cannot be
used in places where non-empty versions of themselves could not be used.

Another characteristic of rows in a table is that they are all alike in
structure and they are all the "same kind of thing" in the model. In a
file system, records can vary in size, datatypes and structure by having
flags in the data stream that tell the program reading the data how to
interpret it. The most common examples are Pascal's variant record, C's
struct syntax and Cobol's OCCURS clause.

The OCCURS keyword in Cobol and the Variant records in Pascal have a
number which tells the program how many time a record structure is to be
repeated in the current record.

Unions in 'C' are not variant records, but variant mappings for the same
physical memory. For example:

union x {int ival; char j[4];} myStuff;

defines myStuff to be either an integer (which are 4 bytes on most
modern C compilers, but this code is non-portable) or an array of 4
bytes, depending on whether you say myStuff.ival or myStuff.j[0];

But even more than that, files often contained records which were
summaries of subsets of the other records -- so called control break
reports. There is no requirement that the records in a file be related
in any way -- they are literally a stream of binary data whose meaning
is assigned by the program reading them.

Columns versus Fields

A field within a record is defined by the application program that reads
it. A column in a row in a table is defined by the database schema.
The datatypes in a column are always scalar.

The order of the application program variables in the READ or INPUT
statements is important because the values are read into the program
variables in that order. In SQL, columns are referenced only by their
names. Yes, there are shorthands like the SELECT * clause and INSERT
INTO <table name> statements which expand into a list of column names in
the physical order in which the column names appear within their table
declaration, but these are shorthands which resolve to named lists.

The use of NULLs in SQL is also unique to the language. Fields do not
support a missing data marker as part of the field, record or file
itself. Nor do fields have constraints which can be added to them in
the record, like the DEFAULT and CHECK() clauses in SQL.

Relationships among tables within a database

Files are pretty passive creatures and will take whatever an application
program throws at them without much objection. Files are also
independent of each other simply because they are connected to one
application program at a time and therefore have no idea what other
files looks like.

A database actively seeks to maintain the correctness of all its data.
The methods used are triggers, constraints and declarative referential
integrity.

Declarative referential integrity (DRI) says, in effect, that data in
one table has a particular relationship with data in a second (possibly
the same) table. It is also possible to have the database change
itself via referential actions associated with the DRI.

For example, a business rule might be that we do not sell products which
are not in inventory. This rule would be enforce by a REFERENCES clause
on the Orders table which references the Inventory table and a
referential action of ON DELETE CASCADE

Triggers are a more general way of doing much the same thing as DRI. A
trigger is a block of procedural code which is executed before, after or
instead of an INSERT INTO or UPDATE statement. You can do anything with
a trigger that you can do with DRI and more.

However, there are problems with TRIGGERs. While there is a standard
syntax for them in the SQL-92 standard, most vendors have not
implemented it. What they have is very proprietary syntax instead.
Secondly, a trigger cannot pass information to the optimizer like DRI.
In the example in this section, I know that for every product number in
the Orders table, I have that same product number in the Inventory
table. The optimizer can use that information in setting up EXISTS()
predicates and JOINs in the queries. There is no reasonable way to
parse procedural trigger code to determine this relationship.

The CREATE ASSERTION statement in SQL-92 will allow the database to
enforce conditions on the entire database as a whole. An ASSERTION is
not like a CHECK() clause, but the difference is subtle. A CHECK()
clause is executed when there are rows in the table to which it is
attached. If the table is empty then all CHECK() clauses are
effectively TRUE. Thus, if we wanted to be sure that the Inventory
table is never empty, and we wrote:

CREATE TABLE Inventory
( ...
CONSTRAINT inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0), ... );

it would not work. However, we could write:

CREATE ASSERTION Inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0);

and we would get the desired results. The assertion is checked at the
schema level and not at the table level.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks. I'm going to test that.

That syntax will work with one column to be updated.
What if I have to columns?

I think the oracle sql will support something like:
UPDATE Table1
SET (city_id, another_column) =
(SELECT T2.id, other_column FROM etctera...

But that no standard SqlServer syntax as far as I know.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:qoednbGWLZwC-TvcRVn-1A@.giganews.com...
> The ANSI Standard syntax supported by both products is
> UPDATE Table1
> SET city_id =
> (SELECT T2.id
> FROM Table2 AS T2
> WHERE T2.city = Table1.city) ;
> Depending on requirements you may want to include a WHERE EXISTS
> (equivalent to the proprietary INNER JOIN syntax)
> UPDATE Table1
> SET city_id =
> (SELECT T2.id
> FROM Table2 AS T2
> WHERE T2.city = Table1.city)
> WHERE EXISTS
> (SELECT *
> FROM Table2 AS T2
> WHERE T2.city = Table1.city) ;
> --
> David Portas
> SQL Server MVP
> --|||On Fri, 26 Nov 2004 11:01:39 +0100, Jan van Veldhuizen wrote:

>Thanks. I'm going to test that.
>That syntax will work with one column to be updated.
>What if I have to columns?
>I think the oracle sql will support something like:
>UPDATE Table1
> SET (city_id, another_column) =
> (SELECT T2.id, other_column FROM etctera...
>But that no standard SqlServer syntax as far as I know.

Hi Jan,

That's right. Using ANSI-standard SQL, the only way to update multiple
columns with values from another table is to repeat the subquery:

UPDATE Table1
SET city_id = (SELECT T2.id FROM etcetera...)
, another_column = (SELECT other_column FROM etcetera...)
WHERE ...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Fri, 26 Nov 2004 11:01:39 +0100, Jan van Veldhuizen wrote:
>
>>Thanks. I'm going to test that.
>>
>>That syntax will work with one column to be updated.
>>What if I have to columns?
>>
>>I think the oracle sql will support something like:
>>UPDATE Table1
>> SET (city_id, another_column) =
>> (SELECT T2.id, other_column FROM etctera...
>>
>>But that no standard SqlServer syntax as far as I know.
>
> Hi Jan,
> That's right. Using ANSI-standard SQL, the only way to update multiple
> columns with values from another table is to repeat the subquery:
> UPDATE Table1
> SET city_id = (SELECT T2.id FROM etcetera...)
> , another_column = (SELECT other_column FROM etcetera...)
> WHERE ...
> Best, Hugo
I believe the ANSI standard allows:
UPDATE Table1
SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
WHERE ...)
WHERE EXISTS(...)

Cheers
Serge|||On Fri, 26 Nov 2004 07:31:59 -0500, Serge Rielau wrote:

>I believe the ANSI standard allows:
>UPDATE Table1
> SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
>WHERE ...)
>WHERE EXISTS(...)

Hi Serge,

Umm, yes. I believe you're right. Unfortunately, that part of ANSI sql is
not available in SQL Server 2000 (don't know about Oracle, thoug), so it
won't help Jan.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Jan van Veldhuizen" <jan@.van-veldhuizen.nl> wrote in message news:<41a6fed9$0$78279$e4fe514c@.news.xs4all.nl>...
> Thanks. I'm going to test that.
> That syntax will work with one column to be updated.
> What if I have to columns?
> I think the oracle sql will support something like:
> UPDATE Table1
> SET (city_id, another_column) =
> (SELECT T2.id, other_column FROM etctera...
> But that no standard SqlServer syntax as far as I know.

Jan,

The multi-column UPDATE you describe above is actually included in the
SQL-2003 standard. (The non-Core feauture T641 - "Multiple column
assignment")

Regards,
Jarl|||Hugo Kornelis wrote:
> On Fri, 26 Nov 2004 07:31:59 -0500, Serge Rielau wrote:
>
>>I believe the ANSI standard allows:
>>UPDATE Table1
>> SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
>>WHERE ...)
>>WHERE EXISTS(...)
>
> Hi Serge,
> Umm, yes. I believe you're right. Unfortunately, that part of ANSI sql is
> not available in SQL Server 2000 (don't know about Oracle, thoug), so it
> won't help Jan.
> Best, Hugo

It does exist in Oracle. Too bad about SQL Server though.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)

common temp tables in procedures

I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.

job1:

EXEc sp1
EXEC sp2
EXEC sp3

Job2

EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5

Job3

EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4

But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.

Eg:

procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.

Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.You can and there is no performance penalty. You might get a performance increase by using table variables though, depending on how small the data is. Temp tables are local to a procedure, so having them named the same in two procs that call each other isn't a problem.

Common Table Expressions!

Hi all
I've checked the CTE's in SQL server 2005 beta 2 it is really a very useful feature but I want to use it with the output of a stored procedure. Is this available?

Thanks in advanceHi,

Can you please be more specific about what you want?

I understood you to say that you wanted to use the result set of a stored procedure as the operand to a relational JOIN operator inside a CTE. AFAIK, that is not allowed. AFAIK, the only place where you can use a stored proceure in a relational (table-valued) expression is the INSERT INTO table.. EXEC proc syntax.

However - I believe you should be able to use a table-valued function.

Regards,
Clifford Dibble

Common Table Expressions

Can I use a CTE inside a user defined function? If so, could you lead me to
an example.
Thanks,
jione example
create function fn1()
returns table
as
return(with fact_tbl as
(select 1 as num,cast(1 as bigint) as fact
union all
select num+1,fact*(num + 1) from fact_tbl where num < 20)
select * from fact_tbl
)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thank you...
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:C534E46E-7279-482A-AF2F-4694086794B7@.microsoft.com...
> one example
> create function fn1()
> returns table
> as
> return(with fact_tbl as
> (select 1 as num,cast(1 as bigint) as fact
> union all
> select num+1,fact*(num + 1) from fact_tbl where num < 20)
> select * from fact_tbl
> )
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Common Table Expressions

CTE's appear to have 2 advantages:
1. Compliance with ANSI
2. Recursive queries.
Aside from these reasons, and perhaps a syntax preference by some, I do not
see a good reason to go out of my way to use them. The remaining situations
don't seem to give any benefit:
1. If I wanted to use the results of the CTE more than once, and the results
of the CTE did not change, I might as well use a temporary table so the CTE
doesn't have to be executed more than once.
2. In situations where I only use the CTE once, the CTE could frequently be
integrated into the existing query that leverages the CTE.
Comments? Thanks.
MarkMark,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Mark" <mark@.nojunkmail.com> wrote in message
news:%23NoYQJC1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> CTE's appear to have 2 advantages:
> 1. Compliance with ANSI
> 2. Recursive queries.
> Aside from these reasons, and perhaps a syntax preference by some, I do
> not see a good reason to go out of my way to use them. The remaining
> situations don't seem to give any benefit:
> 1. If I wanted to use the results of the CTE more than once, and the
> results of the CTE did not change, I might as well use a temporary table
> so the CTE doesn't have to be executed more than once.
> 2. In situations where I only use the CTE once, the CTE could frequently
> be integrated into the existing query that leverages the CTE.
> Comments? Thanks.
> Mark
>

Common Table Expression?

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston"N. Shamsundar" <shamsundar_AT_uh.edu@.nospam.xyz> wrote in message
news:c4npes$3ecc$1@.masala.cc.uh.edu...
> What is the SQL Server equivalent of DB2 common table expressions? For
> example,
> with gry(year,count) as(
> select floor(sem/10),count(distinct ssn)
> from grades
> group by floor(sem/10)
> )
> select year,sum(count) Head_Count from gry
> group by year
> having year >= 1980;
> N. Shamsundar
> University of Houston

If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon|||something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year

Common Table Expression Issue

I am playing around with Common Table Expressions in SQL Server 2005 and am noticing a strange issue.

SQL Server Version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Service Pack 2

Please find a query below that works on AdventureWorks.

Purpose of query

The query starts from the CEO of the company (ManagerID = null) and there is a recursive portion that it uses to find all the direct reports.

What I am seeing

Look at the recursive query where I have made the field, Title, really big and bold. This field always returns, "Chief Executive Officer" - the person at the very top of the food-chain.

What I expect to see

I thought that you should see the title of your immediate supervisor (the person who you directly report to). NOTE: Not everybody reports directly to the CEO.

Query

USE AdventureWorks;

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- POSSIBLE PROBLEM! with this part of the query.

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, d.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

I was able to get the desired results with the following query:

USE AdventureWorks;

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, e2.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN HumanResources.Employee AS e2

ON e.managerId = e2.employeeId

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

Can somebody explain why I am not able to get the Title of the Immediate supervisor in the first query? It looks like the Title of the very first row is propagating during recursion. Is this how CTEs are supposed to behave?

Thanks!

Radha Mukkai

The anchor member's title is being propagated to all rows simply because you're not introducing any other Employee title into the two SELECT statements that form the CTE.

During the first recursion you return d.Title which is actually the title belonging to the anchor member. You now have two rows with the Title of the anchor member, the recursion then continues keeping the value of d.Title constant each time.

To keep the CTE simple you could use the code below as an alternative to your second example.

Chris

Code Snippet

WITH DirectReports (ManagerID, EmployeeID, Title, LEVEL)
AS
(
-- Anchor member definition -- Start from the CEO of the company
SELECT e.ManagerID, e.EmployeeID, 0 AS LEVEL
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL

-- Recursive member definition for getting direct reports
SELECT e.ManagerID, e.EmployeeID, LEVEL + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)

-- Statement that executes the CTE
SELECT dr.ManagerID,
dr.EmployeeID,
CASE WHEN dr.ManagerID IS NULL
THEN ( SELECT e2.Title
FROM HumanResources.Employee e2
WHERE e2.EmployeeID = dr.EmployeeID),
ELSE em.Title
END AS Title,
dr.LEVEL
FROM DirectReports dr
LEFT JOIN HumanResources.Employee AS em
ON dr.managerId = em.employeeId

|||Your logic is kinda messed up here.

Let's talk through the pseudo code a little.

1 - Populate DirectReports with the CEO, using "CEO" as Title.
2 - Populate the DirectReports with the people that report to anyone in the DirectReports table, using the Title that's already in there as the new value for Title.
3 - Repeat from 2.

So you see, you're never actually putting any new Titles in there. Your source of the Titles is your existing list of Titles, which is just "CEO".

To solve this, you could store both titles in the CTE, or just fetch the Manager's title after you've created the CTE.

Hope this helps,

Rob|||

Thanks Rob and Chris. I realized my mistake after mentally walking through the recursion tree.

Thanks,

Radha Mukkai

Common SQL errors

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:548103
Hello,
Can someone recommend a good book that explains the various common design
and query errors that can happen in SQL? Things like fan and chasm traps,
circular references, etc.
Many Thanks.SQL PROGRAMMING STYLE|||Is the whole book in caps, or just the title :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124756158.408802.294350@.g44g2000cwa.googlegroups.com...
> SQL PROGRAMMING STYLE
>|||Here is a small set
http://toponewithties.blogspot.com/...es.blogspot.com
"Bill George" <WovenPath@.frontiernet.net> wrote in message
news:101FE72.4F78%WovenPath@.frontiernet.net...
> Hello,
> Can someone recommend a good book that explains the various common design
> and query errors that can happen in SQL? Things like fan and chasm traps,
> circular references, etc.
> Many Thanks.
>

Common Password

Hi, I would like to setup a common password for all the MS SQL Server Users. Is there any generalized script for the same ? Thanks In Advance.

No, there is no such script that ships with SQL Server because we do not recommend setting a common password for all SQL logins.

Thanks
Laurentiu

|||Thanks, Let me put my question right -

How to change a user's password through a System SP ? If I only know the administrator's username and password.|||

If you are an administrator and you want to reset a user's password, then you can just execute:

ALTER LOGIN user WITH PASSWORD = 'new_password'

See the ALTER LOGIN topic in BOL for more info on this statement.

Is this what you were looking for?

Laurentiu

|||Thanks Again. Yes you are right but i tried using the Statement and it does not work. I did not find anything related in BOL.

Note: I am using MS SQL Server 2000. Thanks|||In SQL Server 2000, you can use sp_password. CREATE LOGIN is new DDL available in SQL Server 2005 only. Here's the equivalent syntax using sp_password:

sp_password NULL, 'new_password', 'user'

You can search for "sp_password" in BOL for SQL Server 2000 for additional info on this.

Thanks
Laurentiu|||Thanks Lots Laurentiu.

Common Paging Practice

Is there a method used to limit the returned number of records from a
query, useful for paging, that does not involve the process of creating
a temp table with an identity field and limiting results by that.See if this helps:
How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
AMB
"jw56578@.gmail.com" wrote:

> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||Depending on what application you are using to display the resultset
(ASP.NET, MS Access, Crystal, etc), you will have different options for
paging at the client side. For a pure SQL solution, let's assume you have a
table (mytable) that contains a record for each zip code for each state, and
you want to page 10 zip codes at a time for the state of Arizona.
Page zip codes 31 - 40:
select bottom 10 zip_code from (select top 40 zip_code from mytable where
state_code='AZ' order by zip_code) as x
Page zip codes 41 - 50:
select bottom 10 zip_code from (select top 50 zip_code from mytable where
state_code='AZ' order by zip_code) as x
<jw56578@.gmail.com> wrote in message
news:1124732360.078016.240550@.f14g2000cwb.googlegroups.com...
> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||The subquery solution is fine for 50 rows, but not 50,000...
(Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
ORDER BY.)

> Page zip codes 31 - 40:
> select bottom 10 zip_code from (select top 40 zip_code from mytable where
> state_code='AZ' order by zip_code) as x
> Page zip codes 41 - 50:
> select bottom 10 zip_code from (select top 50 zip_code from mytable where
> state_code='AZ' order by zip_code) as x|||It should prove quite scalable, assuming that state_code and zip_code are
both indexed. Besides, any application that requires the user to page
through 50,000 records needs some serious re-engineering from a GUI
usability perspective. For example, when a user is browsing available
properties at a realestate website, then they are typically confined to
searching a specific zip code and price range. If they want a listing of all
properties on a regional basis, then that should be delivered in report form
or cached on the client side.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCCdOo0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The subquery solution is fine for 50 rows, but not 50,000...
> (Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
> ORDER BY.)
>
>
>
>

Common Objects in SQL Server 2005

Hi, please tell me where I can find Common Objects in SQL Server
2005....that who appear in Query Analizer (SQL Server 2000)
Common Objects: Configuration Functions, Cursor Functions, Date and Time
Functions etc
Thanks a lot
Look up 'Functions' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Dio" <Dio@.discussions.microsoft.com> wrote in message
news:EC56348F-6637-4015-BFE1-17DE37A44B4D@.microsoft.com...
> Hi, please tell me where I can find Common Objects in SQL Server
> 2005....that who appear in Query Analizer (SQL Server 2000)
> Common Objects: Configuration Functions, Cursor Functions, Date and Time
> Functions etc
>
> Thanks a lot
>
|||Dio (Dio@.discussions.microsoft.com) writes:
> Hi, please tell me where I can find Common Objects in SQL Server
> 2005....that who appear in Query Analizer (SQL Server 2000)
> Common Objects: Configuration Functions, Cursor Functions, Date and Time
> Functions etc
Most of them are under Programmability->Functions->System functions.
Obviously things that are not system functions are not there.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Common Objects in Management Studio?

I recently started using the SQL Server Management Studio that comes with SQL
Server 2005 in place of SQL Query Analyzer that comes with SQL Server 2000.
In SQL Query Analyzer's Object Explorer there is a set of Common Objects
under all of the DataBase Objects. These were very handy for me when I would
forget the syntax for a certain Transact-SQL function. It is more convenient
than looking it up in Books Online.
I can't seem to find the equivalent of the Common Objects in SQL Server
Management Studio. Anyone know where they are?
Thanks,
- Chase
Chase (Chase@.discussions.microsoft.com) writes:
> In SQL Query Analyzer's Object Explorer there is a set of Common Objects
> under all of the DataBase Objects. These were very handy for me when I
> would forget the syntax for a certain Transact-SQL function. It is more
> convenient than looking it up in Books Online.
> I can't seem to find the equivalent of the Common Objects in SQL Server
> Management Studio. Anyone know where they are?
Welcome to Management Studio! You will find that there is a lot of
functionality in Query Analyzer and Enterprise Manager that was not
brought over to Mgmt Studio. No, please don't ask me why! All I can suggest
is go to http://lab.msdn.microsoft.com/ProductFeedback/ and post a
suggestion for it to come back.
(I will have to admit that this is a feature of Query Analyzer that I
was not aware of myself.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||A lot of that functionality can be replaced by templates that you can add
yourself. Open Template Explorer and right-click on the top node, select New
> Template.
Also, if you are just looking for a quick syntax help, you should check
Dynamic Help window. To open it click Help > Dynamic Help menu option. Once
the window opens, you can dock it somewhere in the Management Studio shell.
As you type in an editor, you will see links to help topics that relate to
what you just typed, including syntax help for T-SQL.
Hope that helps.
Regards,
Maciek Sarnowicz
SQL Server SDE
This posting is provided "AS IS" with no warranties, and confers no rights
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9741F0113F282Yazorman@.127.0.0.1...
> Chase (Chase@.discussions.microsoft.com) writes:
> Welcome to Management Studio! You will find that there is a lot of
> functionality in Query Analyzer and Enterprise Manager that was not
> brought over to Mgmt Studio. No, please don't ask me why! All I can
> suggest
> is go to http://lab.msdn.microsoft.com/ProductFeedback/ and post a
> suggestion for it to come back.
> (I will have to admit that this is a feature of Query Analyzer that I
> was not aware of myself.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Common Image in Reports

I need to display the company logo on 100 odd reports that run from different folders on the report manager. Since the company logo has changed before and we had to change every report, I want the image stored on a server (with SSL on) and want the reports to point to the server. The server is going to be different in development and production environments.

When I try to add the image using "Image Wizard" I get the error "Invalid image.". The wizard wouldn't let me use a function to build the path based on the environment and select the image from the correct server. It doesn't accept anything that doesn't start with "http:" when I select "web" as the image source.

Please Help!!!

Don't serve the image over SSL (which requires https: as the protocol). Why can't the image come from a non secure web address? I assume you are just serving these reports over an intranet?

|||Yes, the reports are rendered on the intranet. Even if I have the image come from a non secure web address, I'd still need to get the image from different web addresses in development and production. Reporting services doesn't let me use a web image, unless I specify a valid web address and to switch between development production I need to have some kind of variable in the address.|||

Hello,

Here's what you can do:

Create your report and add a string parameter (Environment) that you will set for development or production. Set this variable to Internal and give it 2 available values (D and P) with a default of D since this is where you will be testing. Add your image to your report via your web address for your development environment. Click on the image and in the properties window and change the Value property to an expression with something like this:

=Switch(Parameters!Environment.Value = "D", "http://www.DevServer.com/images/DevImage.jpg", Parameters!Environment.Value = "P", "http://www.ProdServer.com/images/ProdImage.jpg")

Now, when you deploy your report to production, go into the properties of your report in report manager and change the value of the Evironment parameter to P.

Jarret

|||Thanks Jarret, I'll try and let you know how it goes!!!

common header - AGAIN -

I have seen this question few times but could not find the answer.
What are the recomendations for creating common reusable report header? I
have many reports and all have common header - company logo (image), report
name, date, pagevount, etc. I need to have the header in one place, so if I
edit the header, it chandes in all the reports.
Is there any way to do this?
Thanks for any suggestion.
MykytaPS subreport does not work - can't put subreport on header/footer
"my" wrote:
> I have seen this question few times but could not find the answer.
> What are the recomendations for creating common reusable report header? I
> have many reports and all have common header - company logo (image), report
> name, date, pagevount, etc. I need to have the header in one place, so if I
> edit the header, it chandes in all the reports.
> Is there any way to do this?
> Thanks for any suggestion.
> Mykyta|||We created an Add-In to write into the rdl. In lue of sub-reports in page
headers, it's an ok solution.
"my" wrote:
> PS subreport does not work - can't put subreport on header/footer
> "my" wrote:
> > I have seen this question few times but could not find the answer.
> >
> > What are the recomendations for creating common reusable report header? I
> > have many reports and all have common header - company logo (image), report
> > name, date, pagevount, etc. I need to have the header in one place, so if I
> > edit the header, it chandes in all the reports.
> > Is there any way to do this?
> >
> > Thanks for any suggestion.
> >
> > Mykyta|||Would you share exactly what you have done?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tad" <Tad@.discussions.microsoft.com> wrote in message
news:1152F1D8-3537-4BA9-B380-29F02E7D7DDB@.microsoft.com...
> We created an Add-In to write into the rdl. In lue of sub-reports in page
> headers, it's an ok solution.
> "my" wrote:
> > PS subreport does not work - can't put subreport on header/footer
> >
> > "my" wrote:
> >
> > > I have seen this question few times but could not find the answer.
> > >
> > > What are the recomendations for creating common reusable report
header? I
> > > have many reports and all have common header - company logo (image),
report
> > > name, date, pagevount, etc. I need to have the header in one place, so
if I
> > > edit the header, it chandes in all the reports.
> > > Is there any way to do this?
> > >
> > > Thanks for any suggestion.
> > >
> > > Mykyta|||I would also be interested in this.
"Tad" <Tad@.discussions.microsoft.com> wrote in message
news:1152F1D8-3537-4BA9-B380-29F02E7D7DDB@.microsoft.com...
> We created an Add-In to write into the rdl. In lue of sub-reports in page
> headers, it's an ok solution.
> "my" wrote:
>> PS subreport does not work - can't put subreport on header/footer
>> "my" wrote:
>> > I have seen this question few times but could not find the answer.
>> >
>> > What are the recomendations for creating common reusable report header?
>> > I
>> > have many reports and all have common header - company logo (image),
>> > report
>> > name, date, pagevount, etc. I need to have the header in one place, so
>> > if I
>> > edit the header, it chandes in all the reports.
>> > Is there any way to do this?
>> >
>> > Thanks for any suggestion.
>> >
>> > Mykyta

Common Generation Watermark error for Merge Replication

Hello,

We have a sqlexpress subscriber that is receiving the error below. SQL Server 2005

We have tried:

Generate new snapshot.

Reinitialize the subscription.

Dropped the subscription.

Reboot the web server box.

Reboot the sql server box.

Yet we still get the errors for these specific subscribers. Any ideas?

Error in replication monitor....

Error messages:

The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363

The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800

Can you offer more details on how your merge topology is set up? How are the machines layed out and what version of SQL Server 2005 is on each, RTM or SP1? I am also interested in the publication, article, and subscription settings. Perhaps you could post the publication and subscription scripts. Any other info you can offer on repro steps would be helpful.

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Did you manage to resolve this issue in the end? I'm getting the same error periodically here, so I'd be interested to know if you managed to get it fixed.

Cheers

Nick Hoggard

|||

The same problems occurs in my situation. 9 identical notebooks, each of them synchronizing using filters with SUSER_SNAME() rule. 2 of them stops with the same message. Have no idea what to do.

|||

Same problem here!

Some subscriptions fail with this error.
I really need a way to resolve this at the server as the subscribers are in the field.

Some more Info:
We have about 200 subscriptions and at the moment this problem appears at two of them.

At the client the error is 29006, which is not helping.

|||Unfortunately these issues are very very difficult to debug on a forum, I highly suggest opening a support case so you can have an engineer dig deeper into the problem.|||

Did anything ever get resolved for this? We are having same problem.

thx,

|||Can you describe your problem scenario or the sequence of events that have led you to this failure please?|||I am receiving the same error. I have pretty much the same architecture in place. The error seems to center around the Upload changes first.|||Hi all,

found this one in Microsoft site.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256575&wa=wsignin1.0

Thank you.|||

Do these forums ever get checked? I experienced a similar problem, if not the same problem.

I have a publication that includes Views, SPs, and Functions only. The publication was running successfully on a SQL Express SP2 subscriber, but failed with the error message stated previously in this topic. I had to mark the subscriber as needing to reinitialize and then the publication got passed the error. No changes were made to the articles or publication to cause the error.

Thanks in advance,

Graham

|||

As mentioned above, please create a case with Customer Support so you cna have an engineer debug your issue, especially if this is blocking or hindering your production system.

Common Generation Watermark error for Merge Replication

Hello,

We have a sqlexpress subscriber that is receiving the error below. SQL Server 2005

We have tried:

Generate new snapshot.

Reinitialize the subscription.

Dropped the subscription.

Reboot the web server box.

Reboot the sql server box.

Yet we still get the errors for these specific subscribers. Any ideas?

Error in replication monitor....

Error messages:

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Did you manage to resolve this issue in the end? I'm getting the same error periodically here, so I'd be interested to know if you managed to get it fixed.

Cheers

Nick Hoggard

|||

The same problems occurs in my situation. 9 identical notebooks, each of them synchronizing using filters with SUSER_SNAME() rule. 2 of them stops with the same message. Have no idea what to do.

|||

Same problem here!

Some subscriptions fail with this error.
I really need a way to resolve this at the server as the subscribers are in the field.

Some more Info:
We have about 200 subscriptions and at the moment this problem appears at two of them.

At the client the error is 29006, which is not helping.

|||Unfortunately these issues are very very difficult to debug on a forum, I highly suggest opening a support case so you can have an engineer dig deeper into the problem.|||

Did anything ever get resolved for this? We are having same problem.

thx,

found this one in Microsoft site.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256575&wa=wsignin1.0

Thank you.|||

Do these forums ever get checked? I experienced a similar problem, if not the same problem.

Thanks in advance,

Graham

|||

As mentioned above, please create a case with Customer Support so you cna have an engineer debug your issue, especially if this is blocking or hindering your production system.

Common Generation Watermark error for Merge Replication

Hello,

We have a sqlexpress subscriber that is receiving the error below. SQL Server 2005

We have tried:

Generate new snapshot. Reinitialize the subscription. Dropped the subscription. Reboot the web server box. Reboot the sql server box.

Yet we still get the errors for these specific subscribers. Any ideas?

Error in replication monitor....

Error messages:

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Did you manage to resolve this issue in the end? I'm getting the same error periodically here, so I'd be interested to know if you managed to get it fixed.

Cheers

Nick Hoggard

|||

The same problems occurs in my situation. 9 identical notebooks, each of them synchronizing using filters with SUSER_SNAME() rule. 2 of them stops with the same message. Have no idea what to do.

|||

Same problem here!

Some subscriptions fail with this error.
I really need a way to resolve this at the server as the subscribers are in the field.

Some more Info:
We have about 200 subscriptions and at the moment this problem appears at two of them.

At the client the error is 29006, which is not helping.

|||Unfortunately these issues are very very difficult to debug on a forum, I highly suggest opening a support case so you can have an engineer dig deeper into the problem.|||

Did anything ever get resolved for this? We are having same problem.

thx,

found this one in Microsoft site.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256575&wa=wsignin1.0

Thank you.
|||

Do these forums ever get checked? I experienced a similar problem, if not the same problem.

Thanks in advance,

Graham

|||

As mentioned above, please create a case with Customer Support so you cna have an engineer debug your issue, especially if this is blocking or hindering your production system.

Common Generation Watermark error for Merge Replication

Hello,

We have a sqlexpress subscriber that is receiving the error below. SQL Server 2005

We have tried:

Generate new snapshot.

Reinitialize the subscription.

Dropped the subscription.

Reboot the web server box.

Reboot the sql server box.

Yet we still get the errors for these specific subscribers. Any ideas?

Error in replication monitor....

Error messages:

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Did you manage to resolve this issue in the end? I'm getting the same error periodically here, so I'd be interested to know if you managed to get it fixed.

Cheers

Nick Hoggard

|||

The same problems occurs in my situation. 9 identical notebooks, each of them synchronizing using filters with SUSER_SNAME() rule. 2 of them stops with the same message. Have no idea what to do.

|||

Same problem here!

Some subscriptions fail with this error.
I really need a way to resolve this at the server as the subscribers are in the field.

Some more Info:
We have about 200 subscriptions and at the moment this problem appears at two of them.

At the client the error is 29006, which is not helping.

|||Unfortunately these issues are very very difficult to debug on a forum, I highly suggest opening a support case so you can have an engineer dig deeper into the problem.|||

Did anything ever get resolved for this? We are having same problem.

thx,

found this one in Microsoft site.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256575&wa=wsignin1.0

Thank you.|||

Do these forums ever get checked? I experienced a similar problem, if not the same problem.

Thanks in advance,

Graham

|||

As mentioned above, please create a case with Customer Support so you cna have an engineer debug your issue, especially if this is blocking or hindering your production system.

Common Generation Watermark error for Merge Replication

Hello,

We have a sqlexpress subscriber that is receiving the error below. SQL Server 2005

We have tried:

Generate new snapshot.

Reinitialize the subscription.

Dropped the subscription.

Reboot the web server box.

Reboot the sql server box.

Yet we still get the errors for these specific subscribers. Any ideas?

Error in replication monitor....

Error messages:

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Did you manage to resolve this issue in the end? I'm getting the same error periodically here, so I'd be interested to know if you managed to get it fixed.

Cheers

Nick Hoggard

|||

The same problems occurs in my situation. 9 identical notebooks, each of them synchronizing using filters with SUSER_SNAME() rule. 2 of them stops with the same message. Have no idea what to do.

|||

Same problem here!

Some subscriptions fail with this error.
I really need a way to resolve this at the server as the subscribers are in the field.

Some more Info:
We have about 200 subscriptions and at the moment this problem appears at two of them.

At the client the error is 29006, which is not helping.

|||Unfortunately these issues are very very difficult to debug on a forum, I highly suggest opening a support case so you can have an engineer dig deeper into the problem.|||

Did anything ever get resolved for this? We are having same problem.

thx,

found this one in Microsoft site.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256575&wa=wsignin1.0

Thank you.|||

Do these forums ever get checked? I experienced a similar problem, if not the same problem.

Thanks in advance,

Graham

|||

As mentioned above, please create a case with Customer Support so you cna have an engineer debug your issue, especially if this is blocking or hindering your production system.

Sunday, March 11, 2012

Compare Script

Friday, February 24, 2012

common UPDATE syntax for SqlServer and Oracle

common temp tables in procedures

Common Table Expressions!

Common Table Expressions

Common Table Expressions

Common Table Expression?

Common Table Expression Issue

Common SQL errors

Common Password

Common Paging Practice

Common Objects in SQL Server 2005

Common Objects in Management Studio?

Common Image in Reports

common header - AGAIN -

Common Generation Watermark error for Merge Replication

Common Generation Watermark error for Merge Replication

Common Generation Watermark error for Merge Replication

Common Generation Watermark error for Merge Replication

Common Generation Watermark error for Merge Replication

Comma Delimited Fields

Blog Archive

About Me