Problem in IDENTITY COLUMNS

Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?Originally posted by TALAT
Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?

You will have to find the max value and do something like this.

DBCC CHECKIDENT('table',RESEED,@.max_value)|||Howdy!
Running: DBCC CHECKIDENT('table', RESEED). when i run it second time for the table; the identity value gets normal, i.e. it gets the same as the last value in the column. But it's rather painful to run it for each table. I never had this problem at the publisher it's only at the subscriber. Is there a permanent solution?

Thanx for the reply.

Problem in deployment of packages into 2 different servers

I have 2 servers. DWTEST and DWSQL1. I developed my packages connecting to DWTEST in the design time. I have 32 packages, one of them is the master one and the others are child packages.

package PM calls P1, P2, P3,.......P31

i have 2 data source in my project. RDS and S0, both setup to connect DWTEST in the design. i defined rds_connection_string and s0_connection_string variables in my master package and created an XML config file and put those variables into that config file.

C:\PROJECTS\DEVELOPMENT\SSIS Configurations\SERVER.dtsConfig

<DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">

<ConfiguredValue>Data Source=DWTEST;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">

<ConfiguredValue>Data Source=DWTEST;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

built the project into directory C:\PROJECTS\DEVELOPMENT\S1_REUSABLE_DATA_STORE\S1_REUSABLE_DATA_STORE\bin\Deployment

when i deploy my packages into DWTEST and run them as a SQL Server Agent job they work because data sources are signing to DWTEST already.

when i am deploying them(using the same manifest file in the deployment directory i wrote above) into the server DWSQL1, i select config file source as:

C:\PROJECTS\TESTING\SSIS Configurations\SERVER.dtsConfig

and update data source strings in it to DWSQL1:

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">

<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">

<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

When i run SQL Server Agent Job i defined in DWSQL1(forcing it to use DWSQL1 data source in step definition), my master package PM connects to DWSQL1, and my child packages don't use my config definitions and connect to DWTEST which is defined in the design time.

How can i force all my packages to connect DWSQL1 server using the same built files in deployment directory WITHOUT editing my packages(recreating config files or changing the definition of Data Sources and then built the project again) in Visual Studio?

Thanks to you for reading my long question...

I got a solution for my problem from a friend.

Now, i am using XML configuration file in my Master package, and Parent Package Variables in child packages. Child packages gets their data source connection strings from these variables.

Then i am keeping two different copies of XML config file in different servers(DWTEST and DWSQL1) physically.

In design environment my data source conenctions setup to connect to DWTEST. I build my project, then using the Deployment Manifest i deploy my packages into server DWTEST.

In SQL Server Agent Job Definition, in Step definition, at Configurations Tab page i add a config file and navigate to the config file stored in DWTEST(which is using DWTEST in values) and it works in DWTEST. all my child packages also connects to DWTEST.

Then i deploy my packages into DWSQL1 server using the same Deployment Manifest without editing anything in visual studio an building the project again.

in DWSQL1, in SQL Server Agent Job Definition, in Step definition, at Data Sources Tab page i check the checkboxes and edit the strings to connect to DWSQL1, and at Configurations Tab Page, i add a config file and navigate to the config files stored in server DWSQL1(which is using DWSQL1 in values). Then my packages are all working well and connecting to DWSQL1.

So it seems my problem has been solved.

|||

I was just thinking, why not make all the packages p1,p2...p30 point to the same configuration file as that's used by PM?

When you deploy using deploymentmanifest, you'll only change the config file once, and since all the packages are looking at the same package, they'll pick up the new settings.

That way, in case you make changes to the config file on DWTEST during development, you don't need to worry about forgetting to update the xml file in dwsql.

Just thinking out loud. :)

sql

Problem in configuring Replication

Hi to all!
i m tryiing to establish replication between two servers but when i
start to configure replication it gives me a message "SQL Server agent"
on "Computer Name" currently using system account,which causes
replication between servers to fail" . which account i should use to
configure it.
Thanx
Farid.
*** Sent via Developersdex http://www.codecomments.com ***
Farid,
the simplest way is to use the same account for the publisher and
subscriber's sql server service and sql agent service. Use a domain account
that is in the local admin group of each server. This is not very
sophisticated, but it should get you going.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

problem in - replication

Two Servers,
1st 2nd
database a --> after replication a'
b' <-- after replication b
on 1st server a is replicated to 2nd
on 2nd server b is replicated to 1st
I tried with merge replication it is working fine.But if i tried to use
transactional replication it give some error like "access
violation".Using tansactional replication is it possible.plz help me to
slove this problem.
Regards,
Senthil prabu R
When you used so_addsubscription, did you set @.loopback_detection=true?
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
"senthilprabu" <senthilpraburaju@.gmail.com> wrote in message
news:1125118308.314373.138490@.f14g2000cwb.googlegr oups.com...
> Two Servers,
> 1st 2nd
> database a --> after replication a'
> b' <-- after replication b
> on 1st server a is replicated to 2nd
> on 2nd server b is replicated to 1st
> I tried with merge replication it is working fine.But if i tried to use
> transactional replication it give some error like "access
> violation".Using tansactional replication is it possible.plz help me to
> slove this problem.
> Regards,
> Senthil prabu R
>

Problem Having Old TRN Files Deleted

Greetings!
I am working with two different SQL installations on two different servers.
Both have Enterprise Mgr Maintenance Plans that specify that both the
BAK and TRN files should be deleted after 1 day. On the one server,
both deletions occur just fine. On the second, the old BAK's get deleted,
but not the old TRN's. I have reviewed the maintenance plan settings
several times, but can't see anything amiss. Anyone have any ideas?
Would Be Grateful,
Tom
As long as the MP's are not too complex, I would try deleting and rebuilding
them. I have run into similar anomalies before inwhich this was the
solution.
HTH,
Chris
"Tom Glasser" <TomGlasser@.discussions.microsoft.com> wrote in message
news:74302972-33C1-470A-A18A-F273B84B449F@.microsoft.com...
> Greetings!
> I am working with two different SQL installations on two different
servers.
> Both have Enterprise Mgr Maintenance Plans that specify that both the
> BAK and TRN files should be deleted after 1 day. On the one server,
> both deletions occur just fine. On the second, the old BAK's get deleted,
> but not the old TRN's. I have reviewed the maintenance plan settings
> several times, but can't see anything amiss. Anyone have any ideas?
> Would Be Grateful,
> Tom
>

Problem discovering local SQL Server

I found that SMO sometimes does not return the correct list of available SQL servers.

1. I have SQL development installed in my machine. When I use SMO to retrieve list of server, it does not return anything. But when I use "OSQL -L" command, it returns (local) server.

This is my code.

Cursor.Current = Cursors.WaitCursor

cmbServerName.Items.Clear()

Dim dtSQLServers As DataTable = SmoApplication.EnumAvailableSqlServers()

Dim I As Integer

For I = 0 To dtSQLServers.Rows.Count - 1

Dim ServerName As String = dtSQLServers.Rows(I)("Server").ToString()

If (dtSQLServers.Rows(I)("Instance") IsNot Nothing And dtSQLServers.Rows(I)("Instance").ToString().Length > 0) Then

ServerName = ServerName + "\" + dtSQLServers.Rows(I)("Instance").ToString()

cmbServerName.Items.Add(ServerName)

End If

Next I

Cursor.Current = Cursors.Default

2. When I install SQL 2005 Express in any machine, I cannot retreive its existance via SMO with the same code (on local and remote machine).

Anyone experiencing this situation before? Is there something wrong in my code? How can I fix this?

Thank you,

Hi,

consider the differences with Server and InstanceName, I once wrote an Article about that:

http://sqlserver2005.de/Articles/1/

I saw that you wanted to add the data of ServerName to instance name which would be wrong in your case.

2. Is SQL Server brwoser running ? Otherwise it could be that instances running on other server can′t be retrieved if hosted on different ports.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem creating indexed views with linked servers

Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.

Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A

The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!Ariel (ap@.data54.com) writes:
> Hi, I have a problem, maybe someone can help me.
> I'm traing to create a view with a Linked Server

Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem connecting to SQL-Server

Hello and thanks for reading.
This is my environment:
3 CITRIX Servers with anonymous users to login
Win2K Server with MS-SQL2K and the databases
Users logging in from the first server can connect to the sql-server, the
users logging in on the two other servers can´t. I think the reason is that
the anoymous users from the latter 2 servers have no right to connect to the
DB-Server. I think it is a problem with the windows users security, not with
the SQL-server´s. But I can´t figure out how to change this.
Any idea?
Thank you!
BerndI could tell you how to do this but then I would have to kill you...
But seriously
You will need to add some sort of security in your SQL Server database.
This can be in two forms.
Either Server 2000 security or SQL Security.
So the question is this.
When your CITRIX Servers users log into the Windows server do they have a particualr domain, or are they assigned any sort of proxy ID for use on the Server 2000 ?
If the answer is yes then this domain or ID can be used is SQL Server Secrurity.
If the answer is no then you will need to create each user (or CITRIX Servers application) individually within SQL Server security, assigning it User ID and passwords.
If you have any further questions then please do not hesitate to ask.
Peter Nolan
MCP
>--Original Message--
>Hello and thanks for reading.
>This is my environment:
>3 CITRIX Servers with anonymous users to login
>Win2K Server with MS-SQL2K and the databases
>Users logging in from the first server can connect to the sql-server, the
>users logging in on the two other servers can=B4t. I think the reason is that
>the anoymous users from the latter 2 servers have no right to connect to the
>DB-Server. I think it is a problem with the windows users security, not with
>the SQL-server=B4s. But I can=B4t figure out how to change this.
>Any idea?
>Thank you!
>Bernd
>
>.
>|||Maybe I was not clear enough:
1) My application connects to the MS-SQL instance using SQL security.
User/Password inside SQL-Sever and does not exist outside.
2) MS-SQL Instance is located on Server DB
3) CITRIX Terminalserver is located on SERVER CT1, CT2, CT3
4) Users log on CT1 - CT3 using windows anonymous users and start the
application (wich tries to connect to the db using the SQL-user)
5) On CT1 this succeeds, CT2-CT3 this fails.
Cheers
Bernd
---
This can be in two forms.
Either Server 2000 security or SQL Security.
So the question is this.
When your CITRIX Servers users log into the Windows server
do they have a particualr domain, or are they assigned any
sort of proxy ID for use on the Server 2000 ?
If the answer is yes then this domain or ID can be used is
SQL Server Secrurity.
If the answer is no then you will need to create each user
(or CITRIX Servers application) individually within SQL
Server security, assigning it User ID and passwords.
If you have any further questions then please do not
hesitate to ask.
Peter Nolan
MCP
>--Original Message--
>Hello and thanks for reading.
>This is my environment:
>3 CITRIX Servers with anonymous users to login
>Win2K Server with MS-SQL2K and the databases
>Users logging in from the first server can connect to the
sql-server, the
>users logging in on the two other servers can´t. I think
the reason is that
>the anoymous users from the latter 2 servers have no
right to connect to the
>DB-Server. I think it is a problem with the windows users
security, not with
>the SQL-server´s. But I can´t figure out how to change
this.
>Any idea?
>Thank you!
>Bernd
>
>.
>