add user to stand by database

I have one db which i restore form other db server in standby mode. Now user
exist on the new server but they r not mapped to the standby db.
How can i assign the user to standby dbsp_help_revlogin will resolve you problem - remap logins
read "How to transfer logins and passwords between instances of SQL Server"
at http://support.microsoft.com/kb/246133
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"VSS" wrote:
> I have one db which i restore form other db server in standby mode. Now user
> exist on the new server but they r not mapped to the standby db.
> How can i assign the user to standby db
>
>|||Hi,
You can create DTS package which will import users from one server to another
Amol Lembhe
"Aleksandar Grbic" wrote:
> sp_help_revlogin will resolve you problem - remap logins
> read "How to transfer logins and passwords between instances of SQL Server"
> at http://support.microsoft.com/kb/246133
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "VSS" wrote:
> > I have one db which i restore form other db server in standby mode. Now user
> > exist on the new server but they r not mapped to the standby db.
> > How can i assign the user to standby db
> >
> >
> >

add user to stand by database

I have one db which i restore form other db server in standby mode. Now user
exist on the new server but they r not mapped to the standby db.
How can i assign the user to standby dbsp_help_revlogin will resolve you problem - remap logins
read "How to transfer logins and passwords between instances of SQL Server"
at http://support.microsoft.com/kb/246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"VSS" wrote:

> I have one db which i restore form other db server in standby mode. Now us
er
> exist on the new server but they r not mapped to the standby db.
> How can i assign the user to standby db
>
>|||Hi,
You can create DTS package which will import users from one server to anothe
r
Amol Lembhe
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> sp_help_revlogin will resolve you problem - remap logins
> read "How to transfer logins and passwords between instances of SQL Server
"
> at http://support.microsoft.com/kb/246133
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "VSS" wrote:
>

Add mode problem on field setup with Foreign Key

Hello,

I have two tables (one-to-many relation) related thru a common key. I have setup the Relation using the Database Designer. The Form is populated using Drag-and-drop which automatically adds the BindingSource, TableAdapter, and Binding Navigator. The Relation works great in the change mode, and life is good.

In the Add mode the field setup with the Foreign Key is not automatically populated as it is in Change mode. The field can be updated manually, but that defeats the purpose of setting up the Relation.

Any ideas how to fix this?

I know this is difficult to visualize without seeing it.

Thank you for any ideas,

Tom

The detail grid (or some other graphic control) should be linked to the relation itself not the underlying detail database table. Master's PK should be FK in Detail.

See here on MSDN2:

One BindingSource is bound to the parent Customers table in the data set. This data is displayed in the master DataGridView control. The other BindingSource is bound to the first data connector. The DataMember property of the second BindingSource is set to the DataRelation name. This causes the associated detail DataGridView control to display the rows of the child Orders table that correspond to the current row in the master DataGridView control.

Add consecutive Id in Insert mode - followup

Hi,
This is a buthering subject i raised up 2 days ago with the good help of Paul and rnealejr.
My problem is that i need to fill a table that has an LabelId column from another table.
If I will use IDENTITY for the LabelId column, each time i will clear the table and refill it, the LabelId will start with a new high value. The LabelId column does not represent just an index, it represent an LabelId of electronic label (our product) and we are limited to the numbers of Id's we can supply.
Its good to use IDENTITY but with the condition that after clearing the table and refilling it the starting number of LabelId would be set by me (generaly 1).
Can i do that?

using the following:

insert into Label (Product_Num,Label_Type)
Select LanTable.ProductNum,LanTable.LabelType
From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null

with LabelId using IDENTITY Id will reach the limit (Hex-FFFFF) very fast if I am clearing a table (from any reason) and insert all rows into the table again.

Paul sugested the following:

Declare @.Product_Num varchar(60)
Declare @.LabelType varchar(60)

BEGIN

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.LabelType = LabelType From LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null)
begin
insert into Label (LabelId,Product_Num, Label_Type)
SELECT isnull(max(LabelId) + 1,1),@.Product_Num,@.LabelType from Label
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.LabelType = LabelType from LanTable where ProductNum = @.Product_Num
end
END

Its working but for thousends of rows its very slow.

The above Insert into command is fast but as i said i cannot set the starting value of LabelId and its critical for me.

Please advise what best to do.

Thanks
YossiYou can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.|||Originally posted by Coolberg
You can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.

Thanks alot mate.
i used DBCC CHECKIDENT (Label, RESEED, 1) and it works great.
cheers|||Originally posted by Coolberg
You can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.

I have another small qestion.

Can i set a column to be temporarly Identity and after using the Identity, to set the coloumn not to be Identity type?

Thanks again|||Yes, using for instance Enterprise Mgr och through T-SQL (Alter Table).|||Originally posted by Coolberg
Yes, using for instance Enterprise Mgr och through T-SQL (Alter Table).
I could'nt understand really.
How can i use Alter Table to change the field type?|||Try this:

SET IDENTITY_INSERT DB_NAME ON

--Do the INSERT

SET IDENTITY_INSERT DB_NAME OFF

This only works on one table in a session at a time and allows you to insert any value into an IDENTITY column (assuming it's of the correct data type).

Does that help?|||The above assumes you have an IDENTITY column that you wish to diasable temporarily, but it adds up to the same thing as enabling a column temporarily.|||Originally posted by mattmoodie
The above assumes you have an IDENTITY column that you wish to diasable temporarily, but it adds up to the same thing as enabling a column temporarily.
Yes,
Does that mean that after setting IDENTITY_INSERT to Off, that this field is no longer Identity typed?
The thing is i preffer is possible to set the type not to Identity, In code to chage the type to Identity and them change back.
Is it possible ?
Thaks for the help mate|||No, it keeps its IDENTITY characteristics when IDENTITY_INSERT is set OFF.

What happens is that when IDENTITY_INSERT is set to ON SQL Server no longer automatically updates the column, but still remembers it as an IDENTITY for other sessions and users.

Consider the following table:

CREATE TABLE DBFORUM (
ID INT IDENTITY,
UserName VARCHAR(50) NOT NULL,
)

Normally you can do the following INSERT:

INSERT INTO DBFORUM (UserName) VALUES('mattmoodie')

and SQL Server will add the next ID for you. However, with IDENTITY_INSERT set to ON, you must explicitly add the IDENTITY value:

INSERT INTO DBFORUM (ID, UserName) VALUES('123', 'mattmoodie')

Set IDENTITY_INSERT back to OFF and we can do it the old-fashioned way:

INSERT INTO DBFORUM (UserName) VALUES('garfild')

and SQL Server automatically adds the value, counting from the original mark, assuming you haven't added a higher value in your query. In that case, the new high value is used.|||Originally posted by mattmoodie
No, it keeps its IDENTITY characteristics when IDENTITY_INSERT is set OFF.

What happens is that when IDENTITY_INSERT is set to ON SQL Server no longer automatically updates the column, but still remembers it as an IDENTITY for other sessions and users.

Consider the following table:

CREATE TABLE DBFORUM (
ID INT IDENTITY,
UserName VARCHAR(50) NOT NULL,
)

Normally you can do the following INSERT:

INSERT INTO DBFORUM (UserName) VALUES('mattmoodie')

and SQL Server will add the next ID for you. However, with IDENTITY_INSERT set to ON, you must explicitly add the IDENTITY value:

INSERT INTO DBFORUM (ID, UserName) VALUES('123', 'mattmoodie')

Set IDENTITY_INSERT back to OFF and we can do it the old-fashioned way:

INSERT INTO DBFORUM (UserName) VALUES('garfild')

and SQL Server automatically adds the value, counting from the original mark, assuming you haven't added a higher value in your query. In that case, the new high value is used.
I understand now (finally).
Do you have an answer for the non identity field?
Thanks|||No, I'm afraid I've never come across that situation. Sorry. I'll investigate a bit though.

Add consecutive Id in Insert mode

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
YossiCould you set your ID attribute to an IDENTITY and let the system sort it out?|||Originally posted by Paul Young
Could you set your ID attribute to an IDENTITY and let the system sort it out?
Thanks for your replay.
The Id column has a meaning.
Not every time I will use the Insert routine the Id should get the consecutive value. Thats why i need to know the current Id and from that value to work on. with Identity the values will raise up to the roof.
Yours
Yossi|||Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.

I will work on it....

Thanks a bunch mate.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.
Hi Paul,
I have tried that and I have the following problems:
1.Product_Num is a barcode string, min function cannot help us here.
2.ID_Column can be with no value at first (when the table is empty there is no value) but it cannot be nulled because its the PK so I got an Error trying to draw the Max value. (i can deal with that).

The main problem is 1.
Can you help with that?
Thanks|||Sorry,

1. Yes, min will work with strings. You will need to adjust the local variable datatypes to match your table attributes. Of course if ProductNum is not unique this approach probably wont work well but then I suspect you will have other problems as well. if this is still giving you fits, post the ddl from the Product and LanTable tables.

2. The quick fix on the ID_Column is to test for a null i.e. isnull(max(ID_COLUMN) + 1,1)|||Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.|||Originally posted by Paul Young
Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.

I will do that.
cheers|||I am glad to see that you are following Paul's advice. While I was reading your post, the description was a primary key field that needed to be incremented - and I was curious why you said you did not want to use IDENTITY (I could only think of the gaps as Paul mentioned - as a down side). Anyway, you changed your mind - so good luck.

Actual size of Getdate()

what is the actual length of the result returned by getdate()?
When you set the mode Results in text in QA and run this ,
declare @.s1 varchar(20)
declare @.s2 varchar(30)
set @.s1='no'
set @.s2='the'
select @.s1,@.s2
select getdate()
you get the following result
-- --
no the
(1 row(s) affected)
---
2006-03-16 14:51:26.140
(1 row(s) affected)
The number of '-' is the length of the column or varialbe which
correctly matches with the size of @.s1 and @.s2
But for getdate(), why does it display 54 '-'s? Does it mean the
maximum length is 54?
MadhivananGETDATE returns datetime, not a string. It is the client that does the forma
tting from datetime
(binary information) to something which is readable for us humans. The clien
t application can adapt
to regional settings on the client machine. SQL Server has no control of how
this is presented at
the client. I imagine that some locales can end up with pretty long strings
to represent a datetime,
hence the rather long meta-data definition of such a column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1142950451.878083.249060@.u72g2000cwu.googlegroups.com...
> what is the actual length of the result returned by getdate()?
> When you set the mode Results in text in QA and run this ,
> declare @.s1 varchar(20)
> declare @.s2 varchar(30)
> set @.s1='no'
> set @.s2='the'
> select @.s1,@.s2
> select getdate()
> you get the following result
>
> -- --
> no the
> (1 row(s) affected)
>
> ---
> 2006-03-16 14:51:26.140
> (1 row(s) affected)
> The number of '-' is the length of the column or varialbe which
> correctly matches with the size of @.s1 and @.s2
> But for getdate(), why does it display 54 '-'s? Does it mean the
> maximum length is 54?
> Madhivanan
>|||That is interesting
run these 2
select convert(varchar,getdate(),109)
select getdate()
first one is longer but has less -
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> select convert(varchar,getdate(),109)
You didn't specify a length for the varchar. In all but two cases, that will
result in varchar(1).
The two exceptions are inside CAST and CONVERT where you get varchar(30). Th
is is why you see a
length of 30 for this column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142951590.221838.143270@.g10g2000cwb.googlegroups.com...
> That is interesting
> run these 2
> select convert(varchar,getdate(),109)
> select getdate()
> first one is longer but has less -
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||> You didn't specify a length for the varchar. In all but two cases, that
> will result in varchar(1). The two exceptions are inside CAST and CONVERT
> where you get varchar(30). This is why you see a length of 30 for this
> column.
Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default length consistent across all methods?|||> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at least make the
default
> length consistent across all methods?
I wish it would be deprecated, Aaron. We all se how much trouble it causes.
I'd prefer an error if
omitting length. Next best would be consistent length of 1 (easier to catch
the mistakes).
I checked ANSI SQL a while back, and for char, you should get 1 if you omit.
But for varchar, you
have to specify a length. (http://www.karaszi.com/SQLServer/in.../>
tatypes.asp)
I haven't vented this with MS, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e6o6KfQTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default
> length consistent across all methods?
>