How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

how to save xml document in database table?

I have a db in Sql Server Express 2005. Into this db I have a table with an XML field and I want to save an XML document into this field through SQL.
Any suggestion to do this operation?
Thank you

Mirko

There is a nice article about XML fields and SQL server.

http://www.developer.com/db/article.php/3565996

However there is a problem on that article I'll paste the insert statements here:

INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')

|||

Thank you!

Mirko

how to save xml document in database table?

I have a db in Sql Server Express 2005. Into this db I have a table with an XML field and I want to save an XML document into this field through SQL.
Any suggestion to do this operation?
Thank you

Mirko

There is a nice article about XML fields and SQL server.

http://www.developer.com/db/article.php/3565996

However there is a problem on that article I'll paste the insert statements here:

INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')

|||

Thank you!

Mirko

How to save image to SQL Server 2000

Hi,

I have to store images in database. I have a table which contains field picture which is an image.

How can I do this using C# ?

In Visual Studio .NET i found a code how to obtain BLOB values from the database but I do not know how to do upload an image to the database.

Thanks in advance for your help.

RafiIf you go to theData Access forum and search for BLOB you should be able to find the information you need.

Terri|||Thank you for information. I think I found there everything I needed

How to Save Image from VB 6 to Sql DB

Sir,
I Want To Save My Picture From Vb 6 To Sql Data Base In Picture Field...plz Help Me What Code I Shlould Use..
Waiting Fro Ur Hopefull ReplayRefer these
http://www.aspfaq.com/show.asp?id=2149
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

how to save a rtf file into a field

Can you show me how to save a rtf file into a field in the SQL Server 2K
table? In Access, I am able to do that by assigning the data type OLE Object
and I go to Insert -> Oject and browse to the file I want to store in the
field. But I can not do that way in SQL Server. I guess I would declare the
SQL server data type Image. However, I am stuck on how to store an object in
this field.
Also, assume I can store such an object like that (i.e., a rtf file), can I
declare a record set and manupulate that field to my way?
Thank you for your help in advance.you have to convert to and from a byte[]
This will help
http://www.eggheadcafe.com/PrintSea...asp?LINKID=799
Robbe Morris - 2004/2005 Microsoft MVP C#
http://robbemorris.blogspot.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:E3B6B2BA-5B31-4A4C-95DF-C7250E7BE49A@.microsoft.com...
> Can you show me how to save a rtf file into a field in the SQL Server 2K
> table? In Access, I am able to do that by assigning the data type OLE
> Object
> and I go to Insert -> Oject and browse to the file I want to store in the
> field. But I can not do that way in SQL Server. I guess I would declare
> the
> SQL server data type Image. However, I am stuck on how to store an object
> in
> this field.
> Also, assume I can store such an object like that (i.e., a rtf file), can
> I
> declare a record set and manupulate that field to my way?
> Thank you for your help in advance.|||Hi Robbe:
Thank you for your response. Can you please tell me more about this? I am a
newbie and I don't understand what you mean, i.e., What is the byte array?
Also, I am familiar with VB. Can I use VB for this issue?
Thank you for your help.
"Robbe Morris [C# MVP]" wrote:

> you have to convert to and from a byte[]
> This will help
> http://www.eggheadcafe.com/PrintSea...asp?LINKID=799
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://robbemorris.blogspot.com
>
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:E3B6B2BA-5B31-4A4C-95DF-C7250E7BE49A@.microsoft.com...
>
>sql

How to right justify table field

How do i right justify data in a nchar or nvarchar field? e.g.

now:

2bbb

3bbb

desired :

bbb2

bbb3

where bbb is either blank or null, not certain.

I would appreciate any help

Pauley

Hi,

see if you come around with that:

CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars_drkw(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hi Pauley,

actually I'd not, as this only seems to me a "presentation/redering" problem that should be managed at the presentation level, thus your app...

I'm asking: are you really sure you want to "pad" data on the right? this could require handling query statements differently if your user just entries "2" instead of " 2" ...

regards

|||

Andrea, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?

Pauley

10128

10129

1013

10130

10131

10132

10133

10134

10135

10136

10137

10138

10139

1014

10140

|||

Jens, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?

Pauley

10128

10129

1013

10130

10131

10132

10133

10134

10135

10136

10137

10138

10139

1014

10140

|||

hi Pauley,

in your case it seems to me your choice for an nchar datatype is not correct as your attribute's values are all numeric in the integer domain.. the integer datatype should be the best choice...

but you could argue that, in the future, your attribute could also be set with a mix of alpha + numeric values, like a trailing letter and some digits: "A110", "C99" and the result should be ordered according to alpha + numeric rules so that A110 sorts before B1..
in this cases you can play a little with the ORDER BY clause, casting or alligning part of the attribute's value like the 3rd sample enclosed..

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE #test (

Id nchar(5) NOT NULL PRIMARY KEY

);

GO

INSERT INTO #test VALUES ( '10' );

INSERT INTO #test VALUES ( '1' );

INSERT INTO #test VALUES ( '100' );

INSERT INTO #test VALUES ( '11' );

INSERT INTO #test VALUES ( '110' );

INSERT INTO #test VALUES ( '2' );

INSERT INTO #test VALUES ( '9' );

INSERT INTO #test VALUES ( '22' );

INSERT INTO #test VALUES ( '99' );

GO

PRINT 'Default sort for alphabetic columns';

SELECT * FROM #test ORDER BY Id;

GO

PRINT 'simple solution casting to integer';

SELECT * FROM #test ORDER BY CONVERT(int,Id);

GO

PRINT 'rigth aligning so that " 10" sorts after " 2"';

SELECT *

FROM #test ORDER BY SPACE((DATALENGTH(Id)/2 - LEN(Id))) + Id;

GO

TRUNCATE TABLE #test;

GO

INSERT INTO #test VALUES ( 'A10' );

INSERT INTO #test VALUES ( 'B1' );

INSERT INTO #test VALUES ( 'C100' );

INSERT INTO #test VALUES ( 'A11' );

INSERT INTO #test VALUES ( 'A110' );

INSERT INTO #test VALUES ( 'A2' );

INSERT INTO #test VALUES ( 'A9' );

INSERT INTO #test VALUES ( 'B22' );

INSERT INTO #test VALUES ( 'B99' );

GO

PRINT 'trailing Alpha + numeric';

SELECT Id AS [Id sorted by current collation]

FROM #test ORDER BY LEFT(Id,1), SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1);

PRINT 'specifying a specific collation';

PRINT 'for the particular/required sort rules';

SELECT Id AS [Id sorted by Latin1_General_BIN]

FROM #test ORDER BY LEFT(Id,1) + SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1) COLLATE Latin1_General_BIN;

GO

DROP TABLE #test;

--<--

Default sort for alphabetic columns

Id

--

1

10

100

11

110

2

22

9

99

simple solution casting to integer

Id

--

1

2

9

10

11

22

99

100

110

rigth aligning so that " 10" sorts after " 2"

Id

--

1

2

9

10

11

22

99

100

110

trailing Alpha + numeric

Id sorted by current collation

A2

A9

A10

A11

A110

B1

B22

B99

C100

specifying a specific collation

for the particular/required sort rules

Id sorted by Latin1_General_BIN

-

A2

A9

A10

A11

A110

B1

B22

B99

C100

obviously, you should be aware that when "sorting" values you have to consider the collation you are dealing with, as sort rules can be quite different among different collations.. my results depends (except the last one where a specific sort rule has been set via an explicit COLLATE [collation_name] clause) on my collation settings,

SELECT SERVERPROPERTY('Collation') AS [My default collation];

--<-

My default collation

Latin1_General_CI_AS

regards

|||Ok, you don′t need numeric fields as you don′t wan to have decimal values within your table in this column, right ? So you better use integer or any smaller integer data type. If you just want to use it for adhoc reporting and you cannot change the data type due to the consuming application, you could either use a cast within your order ORDER BY CAST(Thecolumn as int) or use another column to maintain the data, as the on-the-fly approach would not be very performant on heavy queries.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Andrea, thank u very much. That resolved my question.

Pauley

|||

Jens, thank u very much. I will use the data type properly.

pauley

How to right justify table field

How do i right justify data in a nchar or nvarchar field? e.g.

now:

2bbb

3bbb

desired :

bbb2

bbb3

where bbb is either blank or null, not certain.

I would appreciate any help

Pauley

Hi,

see if you come around with that:

CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars_drkw(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hi Pauley,

actually I'd not, as this only seems to me a "presentation/redering" problem that should be managed at the presentation level, thus your app...

I'm asking: are you really sure you want to "pad" data on the right? this could require handling query statements differently if your user just entries "2" instead of " 2" ...

regards

|||

Andrea, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?

Pauley

10128

10129

1013

10130

10131

10132

10133

10134

10135

10136

10137

10138

10139

1014

10140

|||

Jens, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?

Pauley

10128

10129

1013

10130

10131

10132

10133

10134

10135

10136

10137

10138

10139

1014

10140

|||

hi Pauley,

in your case it seems to me your choice for an nchar datatype is not correct as your attribute's values are all numeric in the integer domain.. the integer datatype should be the best choice...

but you could argue that, in the future, your attribute could also be set with a mix of alpha + numeric values, like a trailing letter and some digits: "A110", "C99" and the result should be ordered according to alpha + numeric rules so that A110 sorts before B1..
in this cases you can play a little with the ORDER BY clause, casting or alligning part of the attribute's value like the 3rd sample enclosed..

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE #test (

Id nchar(5) NOT NULL PRIMARY KEY

);

GO

INSERT INTO #test VALUES ( '10' );

INSERT INTO #test VALUES ( '1' );

INSERT INTO #test VALUES ( '100' );

INSERT INTO #test VALUES ( '11' );

INSERT INTO #test VALUES ( '110' );

INSERT INTO #test VALUES ( '2' );

INSERT INTO #test VALUES ( '9' );

INSERT INTO #test VALUES ( '22' );

INSERT INTO #test VALUES ( '99' );

GO

PRINT 'Default sort for alphabetic columns';

SELECT * FROM #test ORDER BY Id;

GO

PRINT 'simple solution casting to integer';

SELECT * FROM #test ORDER BY CONVERT(int,Id);

GO

PRINT 'rigth aligning so that " 10" sorts after " 2"';

SELECT *

FROM #test ORDER BY SPACE((DATALENGTH(Id)/2 - LEN(Id))) + Id;

GO

TRUNCATE TABLE #test;

GO

INSERT INTO #test VALUES ( 'A10' );

INSERT INTO #test VALUES ( 'B1' );

INSERT INTO #test VALUES ( 'C100' );

INSERT INTO #test VALUES ( 'A11' );

INSERT INTO #test VALUES ( 'A110' );

INSERT INTO #test VALUES ( 'A2' );

INSERT INTO #test VALUES ( 'A9' );

INSERT INTO #test VALUES ( 'B22' );

INSERT INTO #test VALUES ( 'B99' );

GO

PRINT 'trailing Alpha + numeric';

SELECT Id AS [Id sorted by current collation]

FROM #test ORDER BY LEFT(Id,1), SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1);

PRINT 'specifying a specific collation';

PRINT 'for the particular/required sort rules';

SELECT Id AS [Id sorted by Latin1_General_BIN]

FROM #test ORDER BY LEFT(Id,1) + SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1) COLLATE Latin1_General_BIN;

GO

DROP TABLE #test;

--<--

Default sort for alphabetic columns

Id

--

1

10

100

11

110

2

22

9

99

simple solution casting to integer

Id

--

1

2

9

10

11

22

99

100

110

rigth aligning so that " 10" sorts after " 2"

Id

--

1

2

9

10

11

22

99

100

110

trailing Alpha + numeric

Id sorted by current collation

A2

A9

A10

A11

A110

B1

B22

B99

C100

specifying a specific collation

for the particular/required sort rules

Id sorted by Latin1_General_BIN

-

A2

A9

A10

A11

A110

B1

B22

B99

C100

obviously, you should be aware that when "sorting" values you have to consider the collation you are dealing with, as sort rules can be quite different among different collations.. my results depends (except the last one where a specific sort rule has been set via an explicit COLLATE [collation_name] clause) on my collation settings,

SELECT SERVERPROPERTY('Collation') AS [My default collation];

--<-

My default collation

Latin1_General_CI_AS

regards

|||Ok, you don′t need numeric fields as you don′t wan to have decimal values within your table in this column, right ? So you better use integer or any smaller integer data type. If you just want to use it for adhoc reporting and you cannot change the data type due to the consuming application, you could either use a cast within your order ORDER BY CAST(Thecolumn as int) or use another column to maintain the data, as the on-the-fly approach would not be very performant on heavy queries.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Andrea, thank u very much. That resolved my question.

Pauley

|||

Jens, thank u very much. I will use the data type properly.

pauley

How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

how to return UTF 8 string from nvarchar field

hi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q232/5/80.ASP&NoWebContent=1 information about storing UTF8 data.|||hi

thanx for u r help but it did not solve my problem
i want to retrive the utf 8 stirngs stored in nvarchar
but this does not help

thanx hope u can help me|||In SQL you can use READTEXt/WRITETEXT/UPDATETEXT to retrieve such data and refre to books online for more information.|||hi
thanx a lot
can u tell me which book to look for and

what i am saying is that the UTF 8 data that i have stored in the nvarchar field and retrieved it from C and store in a txt file
it should give ?? it only gives me ? which means it is not reading the whole data
so can u help in that plzzz
thanx a lot
looking forward to u r reply

thanx|||IN which language you're trying to retrieve C or SQL?
Ensure the windows locale does match to the collation set on SQL server.

Books online is the utility installed alongwith SQL server.
If not get the latest from here (http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)|||i am retrieving the data from MS SQL(back end) through ODBC using C language(front end)
in win2k envoironment and storing the data in a text file
the field is a nvarchar field which has UTF 8 chars in it
and i want to retrieve the data from it and but it is not working accordingly
what should i do ??

how to return truncated field

Hi,
I'd like to select a field of my table and also a truncated version of
that field:
SELECT Field1, Field1_truncated FROM myTable
This field is of type nvarchar. Field1 should contain the entire string
valule whereas Field1_truncated should only contain the first 10
characters of the original field, followed by 3 dots (...)
So I would get this kind of result:
Field1 Field1_truncated
some long string some long ...
Can you help?SELECT Field1, left(Field1,10) + '...' as Field1_truncated FROM myTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi, Samuel
Try something like this:
SELECT Col1,
CASE
WHEN Len(Col1)>13
THEN LEFT(Col1,10)+'...'
ELSE Col1
END as Col1_truncated
FROM YourTable
Razvan|||Brilliant.
Thanks to you guys !

How to return large amount of data in the XML format

I have SQL 2000 and need to retrieve fairly large amout of data (~
50.000 characters) in XML format and then insert it into the field of
the text type.
As 'FOR XML' can't be used with either local variables, INSERT INTO or
SELECT INTO this makes "XML support" quite useless in many aspects.

Can anyone please help me in solving this.
Thanks a lot for your help and time.

PavelPavel (p.golobokov@.ausbulk.com.au) writes:
> I have SQL 2000 and need to retrieve fairly large amout of data (~
> 50.000 characters) in XML format and then insert it into the field of
> the text type.
> As 'FOR XML' can't be used with either local variables, INSERT INTO or
> SELECT INTO this makes "XML support" quite useless in many aspects.

You can try:

INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT ... FOR XML')

Where LOCALSVR has been created as

EXEC sp_addlinkedserver
@.server = 'LOCALVR',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'LocalServer'

That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)

If this does not work out, you will have a find a client to pick up the
XML and send it back.

In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

How to return Carriage Returns

Hi,
A field in my query returns the address formatted with carriage returns
(using CHAR(13)) like so:
Address = Name + CHAR(13)
+ Address1 + CHAR(13)
+ Address2 + CHAR(13)
+ City + SPACE(1) + State + SPACE(1) + PostCode
This field is placed in a table.
In the report, it appears that these carriage returns are ignored. Is this
the case?
How can I get this to work? Can I not do it in the query?
thanks
MattTurns out caching was the issue.
thanks
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:e8TrirQpEHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi,
> A field in my query returns the address formatted with carriage returns
> (using CHAR(13)) like so:
> Address = Name + CHAR(13)
> + Address1 + CHAR(13)
> + Address2 + CHAR(13)
> + City + SPACE(1) + State + SPACE(1) + PostCode
> This field is placed in a table.
> In the report, it appears that these carriage returns are ignored. Is
this
> the case?
> How can I get this to work? Can I not do it in the query?
> thanks
> Matt
>

How to return a partial string based on a particular character?

Hi,
I am looking through books on-line but an not finding what I am looking for.
In my stored proc, I am being passed a varchar field, 20 long. It looks
something like, '103098-1'
I need to split the characters on the left side of the '-' into one field,
and the characters on the right side of the '-' into another field.
How do I do this?
Thanks,
Steve
This is how I did it, does this make sense, or is there an easier way?
Declare @.strOrder varchar(20)
set @.strOrder = '38372-1'
set @.charIndex = CHARINDEX('-', @.strOrder)
set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
@.charIndex))
Thanks again.
"SteveInBeloit" wrote:

> Hi,
> I am looking through books on-line but an not finding what I am looking for.
> In my stored proc, I am being passed a varchar field, 20 long. It looks
> something like, '103098-1'
> I need to split the characters on the left side of the '-' into one field,
> and the characters on the right side of the '-' into another field.
> How do I do this?
> Thanks,
> Steve
|||yes, it could be done in a single line though.
Declare @.strOrder varchar(20)
declare @.left varchar(10)
declare @.right varchar(10)
set @.strOrder = '38372-1'
select @.left = left(@.strOrder, CHARINDEX('-', @.strOrder)-1),
@.right=substring(@.strOrder,
charindex('-',@.strOrder)+1,len(@.strOrder)-charindex('-',@.strOrder)+1)
print @.left
print @.right
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:A9247AFD-686A-46BE-AE7B-225E813FBCA1@.microsoft.com...[vbcol=seagreen]
> This is how I did it, does this make sense, or is there an easier way?
> Declare @.strOrder varchar(20)
> set @.strOrder = '38372-1'
> set @.charIndex = CHARINDEX('-', @.strOrder)
> set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
> Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
> @.charIndex))
> Thanks again.
> "SteveInBeloit" wrote:
for.[vbcol=seagreen]
looks[vbcol=seagreen]
field,[vbcol=seagreen]

how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

How to retrieve Top 3 records in the group level

Hi all,

I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.

Please let me know if some body has any idea.

Thx.

Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to Retrieve Next Value from Field?

Hi All,

I have a table with a column called SRGTE_KEY_1 which is also referenced as an index with an Index name of EMDET_i0. The Data Type is Varbinary.

I have a trigger when activated inserts data into this table. However my problem is that I need to determine what is the next available Varbinary value in the SRGTE_KEY_1 column so I can use this value with my Insert otherwise the trigger will Fail if I dont specify a Unique Value.

What SQL statement/s can I write to be able to obtain the next Unique Value from this Column.

A Step by Step procedure would be great as well as syntax. Thanks for your help.

Regards
AnthonyWhat do you mean by the next available value?
If you mean treat it like an integer and add 1 then maybe it shouldn't be a varbinary.
Or do you have a tabe which gives the available values?|||By the next available value I mean any value that is not being used(thats what I mean by next available) so duplication wont occur. I'm not sure how to obtain this as its varbinary. I wouldn't have a problem if it was a numeric value, but I don't know about this data type.

how to retrieve data from sql then put it into a label or textbox

i wanna know is there a way to retrieve data from the sql database and then
instead of putting it in the datagrid, can i put a specific field of data to a textbox?

i mean just a data (for example, a username where the password match the username) in a textbox.

thanks

sherlynyes you can use a datareader/dataadapter etc to fill a dataset and then into a textbox or pretty much any control you want..

hth

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image

Ok, the problem is that , i have a field called "Attach" in sql of type image, when selecting it , the field is getting data of type BYTE(). which am being unable to display them on an Image on the panel.

using the following vb.net code:

'Dim sel2 As String

'Dim myCom As SqlCommand

'Dim conn As New SqlConnection

'Dim drr As SqlDataReader

'Dim image As System.Drawing.Image

'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "

'conn.ConnectionString = ("server=developer01;uid=sa;password=aims;database=DVPSOC;timeout=45")

'myCom = New SqlCommand(sel2, conn)

'conn.Open()

'drr = myCom.ExecuteReader()

'If drr.Read Then

' Me.ImageMap1.ImageUrl = drr.Item("attach")

'End If

'conn.Close()

Am getting an exeption on the following line Me.ImageMap1.ImageUrl = drr.Item("attach")

saying: Conversion from type 'Byte()' to type 'String' is not valid.

knowing that i tried converting using ToString but it's not getting any output then.

thanks for your help.

a example in C# as below:

try

{

con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}

}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();

MessageBox.Show(ex.Message);
}

hope can help u a little

|||

Sorry but that is not the answer to the question. picturebox cannot be used in ASP.Net.