Managed Identity Ranges in Merge Replication

Hi,
Over the weekend I was taking advantage of system down time and made some
changes to my merge replication database.
The changes were all successful until this morning I get users who are
getting duplicate key error messages. I have verified that the duplicate
key is causing the error.
The remote locations have all been assigned there identity ranges, but it
appears that some cross-over has occurred from the previous ranges and
values. I thought SQL would look in the range assigned for the next
available number and use that. However, it appears that each subscriber is
using the next incremental number within their respective range. That is
the problem and I don't know what to do next.....Suggestions?
WB
What changes did you make to your merge replication database?
"WB" <none> wrote in message news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Over the weekend I was taking advantage of system down time and made some
> changes to my merge replication database.
> The changes were all successful until this morning I get users who are
> getting duplicate key error messages. I have verified that the duplicate
> key is causing the error.
> The remote locations have all been assigned there identity ranges, but it
> appears that some cross-over has occurred from the previous ranges and
> values. I thought SQL would look in the range assigned for the next
> available number and use that. However, it appears that each subscriber
is
> using the next incremental number within their respective range. That is
> the problem and I don't know what to do next.....Suggestions?
> WB
>
|||I added a new column to one table and changed the PK on the same table.
other changes included changing the field length on a few columns and
creating a new table
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:e9YK$79MFHA.2680@.TK2MSFTNGP09.phx.gbl...
> What changes did you make to your merge replication database?
>
> "WB" <none> wrote in message
news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
some[vbcol=seagreen]
duplicate[vbcol=seagreen]
it[vbcol=seagreen]
> is
is
>
sql

Manage server in single user mode

Hello-
I am trying to use SQL server in single user mode to move the system
databases to a different drive as described in KB article
http://support.microsoft.com/kb/224071/
I have sql server started up in single user mode with the other startup
options configured as required. However I can not seem to find a way that it
will let me log in in order to run the procedures. The SQL server agent is
stopped as described in order to not use up the available connection.
Whenever I try to connect with SQL Server management studio it says "Login
failed for user xxxx. Reason: Server is in single user mode. Only one
administrator can connect at this time (Error 18461)."
I am the only person who even knows about this server so I know no one else
is conecting to it. I thought it would work to try and do this procedure
through the command line but i cant figure out how to connect to the server
using the command line (im doing this all local on the server).
Can anyone give me any suggestions on how to go about this or what I could
be doing wrong?
Thanks!It's probably from Object Explorer - you probably have SSMS
configured to open Object Explorer at startup. Close object
explorer and try to connect. You can hit cancel when the
Login dialog comes up and and SSMS opens with no connection.
Then close object explorer. Then connect using New Query.
-Sue
On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
<Ehren@.discussions.microsoft.com> wrote:
>Hello-
>I am trying to use SQL server in single user mode to move the system
>databases to a different drive as described in KB article
>http://support.microsoft.com/kb/224071/
>I have sql server started up in single user mode with the other startup
>options configured as required. However I can not seem to find a way that it
>will let me log in in order to run the procedures. The SQL server agent is
>stopped as described in order to not use up the available connection.
>Whenever I try to connect with SQL Server management studio it says "Login
>failed for user xxxx. Reason: Server is in single user mode. Only one
>administrator can connect at this time (Error 18461)."
>I am the only person who even knows about this server so I know no one else
>is conecting to it. I thought it would work to try and do this procedure
>through the command line but i cant figure out how to connect to the server
>using the command line (im doing this all local on the server).
>Can anyone give me any suggestions on how to go about this or what I could
>be doing wrong?
>Thanks!|||On Jul 24, 2:25 am, Sue Hoegemeier <Su...@.nomail.please> wrote:
> It's probably from Object Explorer - you probably have SSMS
> configured to open Object Explorer at startup. Close object
> explorer and try toconnect. Youcanhit cancel when the
> Login dialog comes up and and SSMS opens with no connection.
> Then close object explorer. Thenconnectusing New Query.
> -Sue
> On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
>
> <Eh...@.discussions.microsoft.com> wrote:
> >Hello-
> >I am trying to use SQLserverinsingleusermodeto move the system
> >databases to a different drive as described in KB article
> >http://support.microsoft.com/kb/224071/
> >I have sqlserverstarted up insingleusermodewith the other startup
> >options configured as required. However Icannot seem to find a way that it
> >will let me log in in order to run the procedures. The SQLserveragent is
> >stopped as described in order to not use up the available connection.
> >Whenever I try toconnectwith SQLServermanagement studio it says "Login
> >failed foruserxxxx. Reason:Serveris insingleusermode. Onlyone
> >administratorcanconnectat thistime(Error 18461)."
> >I am theonlyperson who even knows about thisserverso I know nooneelse
> >is conecting to it. I thought it would work to try and do this procedure
> >through the command line but i cant figure out how toconnectto theserver
> >using the command line (im doing this all local on theserver).
> >Cananyone give me any suggestions on how to go about this or what I could
> >be doing wrong?
> >Thanks!- Hide quoted text -
> - Show quoted text -
The process is
NET START MSSQLSERVER /f /T3608 (as per MS article)
Make sure no other SQL tools are running (config manager etc)
Start SQL Server management studio
When it prompts you to connect to the server press cancel (if you
press connect it is THIS connection which prevents you from running
the alter database query)
At this point you are not connected to anything (it will say "No
Server Connection" in big letters)
Press the New Query button
Paste your amended alter database code into thew query pane and
execute
Continue as per article
Cheers
Barry

Making a database auditable

Hi,
I’m doing a database project and I need to make it an auditable system. I
mean, I need to know "the time and the date" when a user inserts a record, o
r
modify it, or delete it in some tables.
I’m working with Visual Studio 2005 and SQL Server 2000.
Any idea about it '
TanksRobe,
How about SQL triggers? You are able to capture when a record is
inserted, updated, or deleted.
You could create another simple history table, that logged that
information for you...just write a few SQL triggers to populate it.
Reuben
Robe wrote:
> Hi,
> I'm doing a database project and I need to make it an auditable system. I
> mean, I need to know "the time and the date" when a user inserts a record,
or
> modify it, or delete it in some tables.
> I'm working with Visual Studio 2005 and SQL Server 2000.
> Any idea about it '
> Tanks|||I have a convention where any table thats holding business
transactions has these additional fields:
Created, CreatedBy, LastUpdated, LastUpdatedBy, blnDeleted
The stored procedures for Add , Update, Delete (Update) use the
variables GetDate() and SYSTEM_USER - thus taking the audit atsk away
from the client application.
I thought about using triggers and a history table, it can store many
more facts, but this works for what we need.
HTH
reuben.ahmed@.gmail.com wrote:
[vbcol=seagreen]
> Robe,
> How about SQL triggers? You are able to capture when a record is
> inserted, updated, or deleted.
> You could create another simple history table, that logged that
> information for you...just write a few SQL triggers to populate it.
> Reuben
> Robe wrote:

Making a copy of an SQL 7 database into an MSDE environment

We have developed an application for Small Business
Server (SQL) on a system that is running MSDE. All
programming and testing was conducted on a remote,
standalone system, and when completed, we were able to
move appropriate copies to the LAN via an external 80GB
Western Digital USB attached portable drive.
Is it possible to copy one or more or the SQL databases
(all tables) from the SBS SQL server to an MSDE system?
(ie a network attached laptop).
I would appreciate your assistance and comments.
Regards...Dave K.
hi Dave,
"dkalling" <dkalling@.cnx2.com> ha scritto nel messaggio
news:04c001c4eddc$579de490$a401280a@.phx.gbl
> We have developed an application for Small Business
> Server (SQL) on a system that is running MSDE. All
> programming and testing was conducted on a remote,
> standalone system, and when completed, we were able to
> move appropriate copies to the LAN via an external 80GB
> Western Digital USB attached portable drive.
> Is it possible to copy one or more or the SQL databases
> (all tables) from the SBS SQL server to an MSDE system?
> (ie a network attached laptop).
> I would appreciate your assistance and comments.
> Regards...Dave K.
as you can't use DTS to manage MSDE instances (and more you say that your
SQL Server is a version 7.0 server), you have to rely on backup/restore
and/or sp_detach_db/sp_attach_db solutions
please keep in mind you will probably experiencing orphaned users problems
after that operation(s), you can resolve using system stored procedure
sp_change_users_login
(http://www.sqlservercentral.com/colu...okenlogins.asp)
you can move users databases this way only from 7.0 version to 2000 version,
and not vice versa
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Make Page in Report Invisible

Hi all,
I have a report which lists all transactions in a system. This report has
the parameters called From Date and To Date. Each transaction has a amount
value.
What I need is... if the transactions between the selected dates amounts sum
up to 0, then dont generate a page in the report..
What I receive at the moment is a whole heap of pages with 0 totals..
Any help would be greatly appreciated.
I have tried the hidden attribute... but as I am using the page footer this
does not get hidden.Can't you do this in the dataset by saying where total <> 0? You may want
to add a calculated field to the dataset then you can also apply a filter on
it.
"Clint" <ccpatriot12@.yahoo.com> wrote in message
news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a report which lists all transactions in a system. This report has
> the parameters called From Date and To Date. Each transaction has a
> amount value.
> What I need is... if the transactions between the selected dates amounts
> sum up to 0, then dont generate a page in the report..
> What I receive at the moment is a whole heap of pages with 0 totals..
> Any help would be greatly appreciated.
> I have tried the hidden attribute... but as I am using the page footer
> this does not get hidden.
>|||As craig said, do it in query itself. If its not possible within query go to
the filters for the dataset and specify the condition there.
Bye
Sumit Pilankar
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:%23XC4DapNGHA.3924@.TK2MSFTNGP14.phx.gbl...
> Can't you do this in the dataset by saying where total <> 0? You may want
> to add a calculated field to the dataset then you can also apply a filter
> on it.
> "Clint" <ccpatriot12@.yahoo.com> wrote in message
> news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report which lists all transactions in a system. This report
>> has the parameters called From Date and To Date. Each transaction has a
>> amount value.
>> What I need is... if the transactions between the selected dates amounts
>> sum up to 0, then dont generate a page in the report..
>> What I receive at the moment is a whole heap of pages with 0 totals..
>> Any help would be greatly appreciated.
>> I have tried the hidden attribute... but as I am using the page footer
>> this does not get hidden.
>|||I cant do it in the query as that would involve grouping the dataset... as
the total is a summation of all the transactions. I have many case
statements as well..
I will look at the calculated field option.
Thanks
"Sumit Pilankar" <sumit.pilankar@.gmail.com> wrote in message
news:uJTxH0pNGHA.208@.tk2msftngp13.phx.gbl...
> As craig said, do it in query itself. If its not possible within query go
> to the filters for the dataset and specify the condition there.
> Bye
> Sumit Pilankar
>
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:%23XC4DapNGHA.3924@.TK2MSFTNGP14.phx.gbl...
>> Can't you do this in the dataset by saying where total <> 0? You may
>> want to add a calculated field to the dataset then you can also apply a
>> filter on it.
>> "Clint" <ccpatriot12@.yahoo.com> wrote in message
>> news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report which lists all transactions in a system. This report
>> has the parameters called From Date and To Date. Each transaction has a
>> amount value.
>> What I need is... if the transactions between the selected dates amounts
>> sum up to 0, then dont generate a page in the report..
>> What I receive at the moment is a whole heap of pages with 0 totals..
>> Any help would be greatly appreciated.
>> I have tried the hidden attribute... but as I am using the page footer
>> this does not get hidden.
>>
>

Major Formatting Make Over

As part of destroying a legacy system, we imported some data into our
SQL 2005 Archive. Now, some of the numeric data is stored as
nvarchar. Previously if I was running a report against Oracle or DB2 I
would put the following script in the cell to format it properly:
=format((Fields!data1.Value + 0) ,"N2")
That would go in the actual cell not in the format expression. Now I
have a major problem, this new report has like 50 cells and I cannot
go into each one and write that formula out! I've tried to put this
[=format((me.value + 0),"N2")] formula into the format property so
that I can just do it for all the cells but it doesn't work.
Any suggestions? I need to do a major make over to this report.
Thanks!On Dec 7, 9:35 am, SQL Guy <ayma...@.gmail.com> wrote:
> As part of destroying a legacy system, we imported some data into our
> SQL 2005 Archive. Now, some of the numeric data is stored as
> nvarchar. Previously if I was running a report against Oracle or DB2 I
> would put the following script in the cell to format it properly:
> =format((Fields!data1.Value + 0) ,"N2")
> That would go in the actual cell not in the format expression. Now I
> have a major problem, this new report has like 50 cells and I cannot
> go into each one and write that formula out! I've tried to put this
> [=format((me.value + 0),"N2")] formula into the format property so
> that I can just do it for all the cells but it doesn't work.
> Any suggestions? I need to do a major make over to this report.
> Thanks!
1. If you are converting to numerics, you should probably use the
CInt or CDbl VB commands instead of adding a zero. That's just me.
1a. If the data is really supposed to be a numeric, I would suggest
using CAST( fieldname AS FLOAT ) in the SQL itself, so the data is
returned to SSRS as a numeric and not a string.
2. In the Textbox Properties, go to the Format property and put in
N2. It will then format whatever numeric is in the Expression of the
Box. So, you can drag =Fields!data1.Value into the text box, set the
Format property to N2, and you wont need to explicitly change the
Expression.
3. I think you can select many TextBoxes by holding down the control
key and clicking them, then apply Format = N2 to all of them by
typeing N2 (no quotes) into the Format property.
-- Scott|||Thanks for your reply. I cannot do a cast or convert on 50 different
columns (maybe they're 45!) but there is a lot! If I put N2 in the
format property nothing will change since it's still a "string"
technically. This is why each one needs to be explicitly changed.
Since each cell is pulling from a different column, I would need to
format the values in each cell, but I wanted to use the format
property to just do a select all like you mentioned and change them
all at once.|||On Dec 7, 10:55 am, SQL Guy <ayma...@.gmail.com> wrote:
> Thanks for your reply. I cannot do a cast or convert on 50 different
> columns (maybe they're 45!) but there is a lot! If I put N2 in the
> format property nothing will change since it's still a "string"
> technically. This is why each one needs to be explicitly changed.
> Since each cell is pulling from a different column, I would need to
> format the values in each cell, but I wanted to use the format
> property to just do a select all like you mentioned and change them
> all at once.
Yuck. Shame you can't fix the database table, since that's where the
true problem lies. Then again, in the time that it takes to craft an
elegant solution, you probably could have just manually gone one by
one and fixed the SQL.
Step 1, make a copy of your report file.
Step 2, In the solution explorer, right click on the Report.rdl file,
and go to Open Code. Here I would suggest doing a Find-and-replace
of:
1. Replace all 'Fields!' with 'format((Fields!'
2. Replace all '.Value' with '.Value + 0) ,"N2") '
Step 3, Save and reopen the Report. Hopefully, it will now show all
of your fields wrapped with Format commands
-- Scott|||On Dec 7, 12:04 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 7, 10:55 am, SQL Guy <ayma...@.gmail.com> wrote:
> > Thanks for your reply. I cannot do a cast or convert on 50 different
> > columns (maybe they're 45!) but there is a lot! If I put N2 in the
> > format property nothing will change since it's still a "string"
> > technically. This is why each one needs to be explicitly changed.
> > Since each cell is pulling from a different column, I would need to
> > format the values in each cell, but I wanted to use the format
> > property to just do a select all like you mentioned and change them
> > all at once.
> Yuck. Shame you can't fix the database table, since that's where the
> true problem lies. Then again, in the time that it takes to craft an
> elegant solution, you probably could have just manually gone one by
> one and fixed the SQL.
> Step 1, make a copy of your report file.
> Step 2, In the solution explorer, right click on the Report.rdl file,
> and go to Open Code. Here I would suggest doing a Find-and-replace
> of:
> 1. Replace all 'Fields!' with 'format((Fields!'
> 2. Replace all '.Value' with '.Value + 0) ,"N2") '
> Step 3, Save and reopen the Report. Hopefully, it will now show all
> of your fields wrapped with Format commands
> -- Scott
I'm gonna try that, thanks. Unfortunately more work got piled on the
top so hopefully I can try it sometime this week.

Maintenance suggestions

Hi,
I recently upgraded a help desk ticket tracking system from Access to MSDE.
I was wondering if there are any suggestions I should consider about
maintaining a database within MSDE?
Thanks in Advance,
JeffH
hi,
JeffH wrote:
> Hi,
> I recently upgraded a help desk ticket tracking system from Access to
> MSDE. I was wondering if there are any suggestions I should consider
> about maintaining a database within MSDE?
start reading at
http://msdn.microsoft.com/library/de...kprst_63eh.asp
...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply