Field msrepl_tran_version added

Hello there
I've create transacional replication from two databases. At the publisher
new field added to all the tables i mark as replicated: msrepl_tran_version
It cause damage to my database. What i need to do to ged rid of them? and
what i need to do so they won't be created again?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
You have to run a script to drop them. These columns are using by immediate
updating or queued updating publications. Have you need of these
publications?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've create transacional replication from two databases. At the publisher
> new field added to all the tables i mark as replicated:
> msrepl_tran_version
> It cause damage to my database. What i need to do to ged rid of them? and
> what i need to do so they won't be created again?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary:
When i tried to delete the field by this code?
ALTER TABLE Client
DROP COLUMN msrepl_tran_version
I got an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__Client__msrepl_t__02FED618' is dependent on column
'msrepl_tran_version'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
objects access this column.
how can i delete these records?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
> You have to run a script to drop them. These columns are using by
> immediate updating or queued updating publications. Have you need of these
> publications?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
>
|||You will have to drop the constraints before dropping the column.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23ePeTNMAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
|||I'm sorry with my language. it's one constraint per msrepl_tran_version
column, not constraints...
Sorry.
|||There are default contraints on that field. You'll have to drop those
constraints bfore dropping the column itself. you should do a select on
sysobjects to find the default constraints like '%df_%_msrepl%'. i would
assume, replication is not in place(immediate updating or queued updating).
Once you drop the constraint, it lets u drop the column.
HTH
Tejas
"Roy Goldhammer" wrote:

> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
>
|||Whell Tejas:
It gives me new error: ALTER TABLE DROP COLUMN failed because
'msrepl_tran_version' is currently replicated.
Now what i need to get rid of that, or what i need to cause it not be
created again?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...[vbcol=seagreen]
> There are default contraints on that field. You'll have to drop those
> constraints bfore dropping the column itself. you should do a select on
> sysobjects to find the default constraints like '%df_%_msrepl%'. i would
> assume, replication is not in place(immediate updating or queued
> updating).
> Once you drop the constraint, it lets u drop the column.
> HTH
> Tejas
> "Roy Goldhammer" wrote:
|||You will have to drop your subscription before trying to make these changes.
Is this on the publisher or subscriber?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Whell Tejas:
> It gives me new error: ALTER TABLE DROP COLUMN failed because
> 'msrepl_tran_version' is currently replicated.
> Now what i need to get rid of that, or what i need to cause it not be
> created again?
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...
>
|||Whell Hilary:
It is on the Publisher
And it also addes me new tables Conflict...
I've chosed to use Transactional replication, and it act like merge
replication.
This is realy bad.
What i need to do to use replication without changing the database
Publisher?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#K1WEuYAGHA.3140@.TK2MSFTNGP14.phx.gbl...
> You will have to drop your subscription before trying to make these
changes.[vbcol=seagreen]
> Is this on the publisher or subscriber?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
would[vbcol=seagreen]
them?
>

Few SSAS 2008 Issues

Few notes from my testing

No problems restoring databases that were archived in SSAS 2005. I like new dimension wizard and can see myself using it more, as I have more control on how attributes are created. June CTP can be used to test how my database is holding to "best design practices", as I can see all the warnings after database is restored. Adventure works database that is shipped with Katmai June CTP has a lot of warnings. I know that this is just a warning and not error, but when I open Adventure Works DW cube, I keep getting distracted by all these blue lines in almost every dimension. I could not find how to disable warning for a single case - example just for a single dimension. Can I do that? If so, I need an option to select any object and see what warnings are disabled and ability to re-enable them. Just then I'll start to turning warnings off. But now I noticed that I have so many warnings that I am starting to ignore them - not good. When attribute/cube has a warning, it shows up for for a few seconds. Somehow would be nice to see option saving warning into clipboard. I had 16 warning on my cube, cannot read them in 2-3 seconds that warning description is visible When creating new dimension using wizard "Generate a non-time table in the data source" using any (I believe, I tried Customer, Department) template, even dimension name is specified, actual name assigned by wizard is "Dimension" Dimensions generated from template has a lot of warnings and attribute relationship is not properly defined (example on everything default "Customer" dimension). I would expect that template would not have such problems. When creating new dimension using wizard option "Generate a non-time table in the data source" very last dialog box has an option "Generate Schema now". This check-box not very visible and if you do not select it, dimensions do not have tables created for them. If you select, all dimensions created in previous steps are included in the generation. This is not very intuitive. Some warnings are very confusing. Example "Set the dimension type to match the 'Account' or 'Time' attribute types" or "Set the dimension type to match the attribute type.". As these warnings appear quite too often, I started to ignore them, so I'll miss cases when they are really warning me.

Vidas Matelis

Hello Vidas,

Thanks for taking the time to test out the build and submit this feedback.

We will be updating both Adventure Works and our dimension templates for Katmai. I suspect this won't happen for another two CTPs or so, so please bear with us. Of course, the updated sample and templates will be warning free.

You can view all warnings at a given moment by building and then viewing (if not already visible) the Error List window. This is also the way to dismiss an individual case of a warning--right click on the warning you want to dismiss-->Dismiss...

You're absolutely correct about the confusing text associated to the "type" warnings. We've already fixed this so the text should be much improved for the next CTP. Additionally, we've modified the logic so some common cases (e.g.., general time-related types) will not result in warnings being shown.

Thanks again for the great feedback.

-rob

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx
.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.
Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

FCB::Open failed: Could not open device for virtual dev

Hi, if any one could helpme here,

I am trying to connect to one oof my databases in SQL 2000 server through ASP using similasr coode:

<% @.LANGUAGE = VBSCRIPT %>
<% Option Explicit %>

<html>
<head>
<title>testing our connection</title>
</head>
<body>

<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable

adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' the following connection string is for connecting to a local SQL Server 2000 database
objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=sa;Password=tdmes102;Initial Catalog=CustomerProfile;" & _
"Data Source=dani;" & _
"Initial File Name=D:\DB\TDMECustomer_Data.MDF"
' the following connection string is for connecting to a remote SQL Server 2000 database

objRS.Open "CustomerProfile", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

While Not objRS.EOF
Response.Write objRS("Principal") & "<BR>"
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>

</body>
</html>

I have tried several diferent ways and I keep getting this foollowing messages the same in the log file.

I tried relocating the database,reinstalling SQL , sp2.

HTTP error:
FCB::Open failed: Could not open device D:\DB\TDMECustomer_Data.MDF for virtual device number (VDN) 0.

SQL log file:
udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device D:\DB\TDMECustomer_Data.MDF.
2002-07-15 09:46:59.11 spid51 FCB::Open failed: Could not open device D:\DB\TDMECustomer_Data.MDF for virtual device number (VDN) 0.You don't supply an Initial File Name, maybe something you use for Access but not for SQL Server. At our site we don't even use the Data Source parameter, we add a Server parameter instead.

Here is a website that has connection examples
ConnectionString (http://www.connectionstrings.com/index.htm)

Example

Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;")

Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=sa;Password=tdmes102;Initial Catalog=CustomerProfile;" & _
"Data Source=dani;

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not

exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.
The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.

|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fastest way to convert scripts to use Linked Server?

We recently migrated one of our databases to a new server and now we
have to manually modify all of our T-SQL scripts to use Fully qualified
Linked Server Name in order to reference the new database on the new
server. The problem is that this is a very slow update process because
we have to be very careful on which parts of the SQL statment should be
modified. I wonder if is there fastest way or tool available to do this
kind of update?.
Thanks
Rod"Rodusa" <rclwebdesign@.yahoo.com> wrote in message
news:1133198377.065427.178720@.g49g2000cwa.googlegroups.com...
> We recently migrated one of our databases to a new server and now we
> have to manually modify all of our T-SQL scripts to use Fully qualified
> Linked Server Name in order to reference the new database on the new
> server. The problem is that this is a very slow update process because
> we have to be very careful on which parts of the SQL statment should be
> modified. I wonder if is there fastest way or tool available to do this
> kind of update?.
>
In SQL Server 2005 you can use synonyms to provide transparent naming of
remote objects. This works for objects in other schemas, databases and
servers.
On the local server retain the old database and replace each table with a
synonym pointing to the table on the linked server. You won't have to
change a line of code.
David|||We don't have SQL 2005.. That indicates that I will have to stick to
the same process..
Thanks

Failure to authendicate domain/users

I have several access databases in mind to migrate to SQL server. I installed MS SQL 2005 Express on my machine. I will have procedures to run with authorizations beyond that of a common user, such as database administrative work where server agent is not available, I may rely on users' log-on prompt to do some maintenance work. However, I cannot get the EXEC AS 'Domain\User' to work. The procedures can be created OK. But whenever they are called, the following message shows up:

Msg 15404, Level 16, State 19, Procedure XXX, Line 0
Could not obtain information about Windows NT group/user 'Domain\User', error code 0xea.

I tried to tweak with the account under which the server service is running. There are three options under built-in account: Local system, Local service, and Network Service. My understanding is that Network Service will use the log-on of the current user of the computer. I have admin right of the computer. None of the three options work. Additionally, when I specify an account (my own account), it's the same thing.

The procedure xp_logininfo always fails when I query a specific domain\user.

The ADHelper is configured to run manually.

I could not think of other ways to get a possible solution. Any help is much appreciated.

hi,

try having a look at http://support.microsoft.com/kb/834124/en-us

regards

|||

Hi, there,

I did several times. The problem is that the article does not contain an explanation of state 19. It has explanations for state 21 and above. Do you have any idea what state 19 relates to?

Thanks,