Sql Update command: views

Showing posts with label views. Show all posts

Wednesday, March 28, 2012

Problems in Creating Views in Sql

Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
> 3.Column example 3:
> IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> > 3.Column example 3:
> > IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
> Take a look at the CASE statement in the online help.
Thanks Freddy.
This one works.

Problems in Creating Views in Sql

quote:

> 3.Column example 3:
> IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)

Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...

quote:

> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> Take a look at the CASE statement in the online help.

Thanks Freddy.
This one works.

Monday, March 12, 2012

Problematic Stored Procedures using Views with Triggers

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@.StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber

FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @.CompanyName VARCHAR(128)
DECLARE @.AddressID INT
DECLARE @.Street VARCHAR(256)
DECLARE @.City VARCHAR(128)
DECLARE @.StateID INT
DECLARE @.StateName VARCHAR(128)
DECLARE @.ZipCode INT
DECLARE @.Country VARCHAR(128)
DECLARE @.PhoneID INT
DECLARE @.PhoneNumber VARCHAR(32)
--DECLARE @.CompanyID INT

SELECT
--@.CompanyID = CompanyID,
@.CompanyName = CompanyName,
@.Street = Street,
@.City = City,
@.StateName = StateName,
@.ZipCode = ZipCode,
@.Country = Country,
@.PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@.StateName)

SET @.StateID = @.@.IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@.Street, @.City, @.StateID, @.ZipCode, @.Country)

SET @.AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@.PhoneNumber)

SET @.PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@.CompanyName, @.AddressID, @.PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@.CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @.CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@.CompanyName VARCHAR(128),
@.Street VARCHAR(256),
@.City VARCHAR(128),
@.StateName VARCHAR(128),
@.ZipCode VARCHAR(128),
@.Country VARCHAR(128),
@.PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@.CompanyName, @.Street, @.City, @.StateName,
@.ZipCode, @.Country, @.PhoneNumber)
END
GO

Hi,

first of all you have a misdesign in your trigger. Triggers occur per statement not per row, so inserting 50 rows in a table will fire the trigger only once not 50 times. In your case the trigger is only executed once which means that it would ingnore the 49 other inserted rows. I would first fix that and perhaps come back with the error line where the error you are describing occurs, marking it with something like -- <<-- Error occurs here.

HTH; Jens Suessmeyer.

http:/www.sqlserver2005.de

|||

I am only needing to add one company at a time. In testing the AddCompany stored procedure with Query Analyzer, it appears to work with the correct parameters. For example,

-- Insert First Company
EXEC AddCompany '', '', '', ...

-- Insert Second Company
EXEC AddCompany '', '', '', ...

With actual values, the records are inserted in the tables.

But, my delimma is in the AddCompany stored procedure I don't want to have to pass in a value for the identity column and I don't want to remove it from the view because I need it for the GetCompany stored procedure. And, I don't want to have to create a seperate view for each scenerio; so, my question is there anyway to achieve the desired goal.

I have been looking at the "check" and "no check" options, but I am not sure how they function.

Saturday, February 25, 2012

Problem with Views

I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
Brij
When you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.co m...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>

Problem with Views

A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?
Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?
|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:

> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>
|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:

>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:

> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.freenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!small.ne ws.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:

>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=e...aszi&safe=off&
qt_s=Search

>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:

> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=e...araszi&safe=of
f&qt_s=Search
>
|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with Views

A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the ol
d
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:

> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different versio
n
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:

>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that thes
e
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:

> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.f
reenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!smal
l.news.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.s
upernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:

>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=...r />
safe=off&
qt_s=Search

>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views
,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste th
e
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:

> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=...Karaszi&safe=of
f&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste t
he
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with Views

A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
> >A while back I ran into a problem where changes to views (such as renaming
> > them) was not being reflected. Somehow I was able to get past this
> > problem.
> >
> > Now I want to generate a script that will create my views. When I look at
> > the script that was generated has old information; the old names, and the
> > old
> > queries.
> >
> > Is there something I'm overlooking when I make changes to views?
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
> >DBO is the only user for this DB. Each create statement in the generated
> >script looks like this: CREATE VIEW dbo.someview. The problem is that these
> >view names and associated querys are old, the way the were before I renamed
> >them and made changes to the queries.
> >
> >Thanks for your quick reply,
> >-Tracy
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with Views

I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
BrijWhen you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.com...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>

Problem with View

Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Today I encountered with a view that references other views and each of
them reference some other
> > view. The problem began when I started querying this view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I excluded the condition,
> > I expected to have more rows but the result was about 50,000 rows. I
tried different ways but the
> > condition produced more rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
like that?
> > Any help would be greatly appreciated.
> > Leila
> >
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> In addition to Nigel's commends: Are you using any old-style outer joins
> in any of the view
>> definitions? (*=)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Today I encountered with a view that references other views and each of
> them reference some other
>> > view. The problem began when I started querying this view.
>> > When I used condition(WHERE), the view produced about 67,000 rows. When
> I excluded the condition,
>> > I expected to have more rows but the result was about 50,000 rows. I
> tried different ways but the
>> > condition produced more rows!
>> > Whereas the views were nested, it was very difficult to trace the
> problem.
>> > It may help if I say some views used UNION ALL to concatenate some
> result.
>> > I must find the source of problem. Does anybody have any idea/experience
> like that?
>> > Any help would be greatly appreciated.
>> > Leila
>> >
>>
>|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > I'm not sure if I have undertood your meaning.
> > Do you mean old style joins like:
> > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > ?
> >
> > Leila
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> In addition to Nigel's commends: Are you using any old-style outer
joins
> > in any of the view
> >> definitions? (*=)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Today I encountered with a view that references other views and each
of
> > them reference some other
> >> > view. The problem began when I started querying this view.
> >> > When I used condition(WHERE), the view produced about 67,000 rows.
When
> > I excluded the condition,
> >> > I expected to have more rows but the result was about 50,000 rows. I
> > tried different ways but the
> >> > condition produced more rows!
> >> > Whereas the views were nested, it was very difficult to trace the
> > problem.
> >> > It may help if I say some views used UNION ALL to concatenate some
> > result.
> >> > I must find the source of problem. Does anybody have any
idea/experience
> > like that?
> >> > Any help would be greatly appreciated.
> >> > Leila
> >> >
> >>
> >>
> >
> >
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > Leila,
> >
> > I'm referring to old style *outer* joins, such as:
> >
> > SELECT t1.col1, t2.colZ
> > FROM t1, t2
> > WHERE t1.c1 *= t2.c1
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > I'm not sure if I have undertood your meaning.
> > > Do you mean old style joins like:
> > > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > > ?
> > >
> > > Leila
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > >> In addition to Nigel's commends: Are you using any old-style outer
> joins
> > > in any of the view
> > >> definitions? (*=)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> > Today I encountered with a view that references other views and
each
> of
> > > them reference some other
> > >> > view. The problem began when I started querying this view.
> > >> > When I used condition(WHERE), the view produced about 67,000 rows.
> When
> > > I excluded the condition,
> > >> > I expected to have more rows but the result was about 50,000 rows.
I
> > > tried different ways but the
> > >> > condition produced more rows!
> > >> > Whereas the views were nested, it was very difficult to trace the
> > > problem.
> > >> > It may help if I say some views used UNION ALL to concatenate some
> > > result.
> > >> > I must find the source of problem. Does anybody have any
> idea/experience
> > > like that?
> > >> > Any help would be greatly appreciated.
> > >> > Leila
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>

Monday, February 20, 2012

Problem with user defined funtions

Hi,
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:

> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote:

Wednesday, March 28, 2012

Problems in Creating Views in Sql

Problems in Creating Views in Sql

Monday, March 12, 2012

Problematic Stored Procedures using Views with Triggers

Saturday, February 25, 2012

Problem with Views

Problem with Views

Problem with Views

Problem with Views

Problem with Views

Problem with View

Problem with View

Problem with View

Monday, February 20, 2012

Problem with user defined funtions

Sql Update command

Blog Archive

About Me