Error inside trigger

hi

there is a programing flow that I want to know exactly what will happen

1- A database which has 3 table T1,T2,T3

2-a Storeprocedure called STP

3- Inside stp we insert to T1 and T2

4-T2 has 2 triggers TRG1,TRG2

5 - TRG2 check some thing and raise error .

so, I want to know at this case all rows which inserted will rollback or no

You are inserting in more than one table, so if you want to rollback the whole transaction inside the trigger, you have to initiate an explicit transaction using "begin transaction" (before start inserting), if not, just the insert on T2 will be rolled back.

AMB

|||

TRG2 will ROLLBACK the INSERT into T2 ONLY.

As Alejandro indicated, if you wish to ROLLBACK ALL activites, you need to start a TRANSACTION inside STP.

|||

I did think like u said befor but !!! you try this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Cola] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Colb] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

go

exec sys.sp_addmessage @.msgnum= 50001, @.severity= 16, @.msgtext= N'User error', @.replace=N'replace'

go

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

createTRIGGER dbo.trg1

ON dbo.table_1

AFTER INSERT,UPDATE

AS

BEGIN

SETNOCOUNTON;

raiserror(50001,16,1)

END

GO

they make u a table (Table_1) and a trigger (Trg1)

as u can see in Trg1 an error will raise so if we insert any row it will be rollbacked. so try to insert :

INSERTINTO [dbo].[Table_1]

([Cola]

,[Colb])

VALUES

('aaa'

,'bbb')

then u can see a row added !!!

sql

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

Error Including null value in a Numeric field

Hi All,

I'm migrating some SQL 2000 DTS to SSIS.

I am transfering data from a DB2 table to a SQL 2005 table using the OLE DB Source, Data Converstion then the OLE DB Destionation.

So, I have a numeric (Precision 3, Scale 2) field with NULL value in the DB2 table.

I'm trying to transfer these data to a SQL2005 table and I am receiving this error message below:

"[Destination Table TFACIL [18]] Error: There was an error with input column "COMB_OPPT_PRCT" (2865) on input "OLE DB Destination Input" (31). The column status returned was: "The value violated the integrity constraints for the column.". "

The field must accept null because of the APPLICATION ( i can't change it, im not the owner ).

Could someone help me?

Thanks in advance.

Regards,

Thiago

Check that the SQL table TFACIL.COMB_OPPT_PRCT

1) Has no CHECK constraints on it that would prevent NULL being loaded

2) is not defined as NOT NULL

error in WHERE statement

dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...

I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.

what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...

regards,
kanishkit's because this sql is perfectly valid in terms of syntax:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

The only reason it fails is because there is no column named smallint in that table. That's not a syntax error.

Now this query, on the other hand, works, because both columns exist, and has exactly the same form as yours:

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = LOCAL_TYPE_NAME

is that what you are asking?|||not actually, i was saying that i was looking for a value in the column TYPE_NAME named smallint

I was giving the WHERE clause to look for a value called smallint in the column TYPE_NAME. the column has many vaules in it...smallint is one of them...

maybe i should have written it as:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

Since i am new to SQL language....what were you refering to...??...Although i quite got my answer...

Can you please tell me...what is the intersection of the column and row called??....for example in ms Excel when the row 7 and column D meet then a cell is formed...called D7

But in SQl server...what is the intersection of the Column and Row called...?? Like the cell in ms Excel...??

regards,
kanishk|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?|||the intersection of row and column is called "the value of column Y for row X"

:cool:

i realize that sounds somewhat flippant, but relational database theory is based upon primary keys, so "row X" means "the row where the primary key value is X" since that's how you tell rows apart, and "column Y" means "the column with 'Y' as the column name" since that's how you tell columns apart

simple, innit ;)|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?

thanks, i was wondering that when i give the command that you gave up...viz,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

when i run it, then automatically the TYPE_NAME column takes brackets,,,why is that??...i have shown it below...

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

regards,
kanishk|||That is because you are running this query in the SQL section of enterprise manager. Enterprise manager will try to bracket all where conditions
so if your query is something like

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint' OR TYPE_NAME = 'int'

Then on running it you will get

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint') OR (TYPE_NAME = 'int')

Try running the query in SQL Analyzer and you will not get the brackets..... however i guess the brackets are purely for readability and in the end finally makes no difference.....|||dear sir, i have started using the SQL query Analyzer, i have another question,
i just put in the LIKE condition...

SELECT * FROM spt_datatype_info WHERE TYPE_NAME LIKE '%s'

now this will give all the values in the TYPE_NAME column which start with any characters but end with 's' , thats why i have put '%s' , now suppose i want all the values that have 2 letters as starting and the 3rd letter as 's' , so what do symbol do i use??

Like in windows when we used to give in command prompt the ! , exclamation mark for the characters we did not know but were sure of the count of them, and the star '*' for the characters we did not know and were not sure of their count..

Similarly in SQL what would we give for the LIKE condition..??

regards,
kanishk|||... LIKE '__s%'this is all explained nicely in the manual

Error in Updating records using Openquery

Hi,

I am trying to update a DB2 table from Sql Server using Openquery but it gives me an

error, I tried selecting rows from the DB2 table using Openquery and it works fine.

Following is the update query:

UPDATE OPENQUERY(DB2_DB2T, 'SELECT STATUS FROM $ZUDBA01.TPT20_VOLS

WHERE AUD_NBR=10000004 AND POLL_NUM_ACTUAL=''999456789'' AND

REC_TYP=''SYM''') SET STATUS='RETURNED'

This query when executed gives following error:

Server: Msg 7399, Level 16, State 1, Line 35
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Insufficient base table information for updating or

refreshing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:: SetData returned

0x80004005: ].

Kindly help me out.

Thanks and Regards,

Pranjal

I am not sure how much this helps...but check it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=113747&SiteID=1

sql

error in updating field thru stor_proc

I'm trying to update 'password' field in a table thru a stored procedure which is called from my program.

I'm passing the values for password and the key fields.

While debugging I can see the values in each parameter, but I get an error that the 'value for password was not passed'.

Here's the program snippet for calling the stored proc.

All three parameters are 'varchar' type

dbConn.Open();

dbConn.CreateParameters(3);

dbConn.AddParameters(0, Pwd, newPwd);

dbConn.AddParameters(1, Login, pwd_Login);

dbConn.AddParameters(2, IdNum, pwd_IdNum);

result = dbMgr.ExecuteNonQuery(CommandType.StoredProcedure, SP_UPDPWD);

--

EXEC @.return_value = [dbo].[usp_UsrMstUpdPwdParaPwdEmailCaseNum]

@.Pwd = N'3Sg7vqowIBRdfgdfgrgdjykFTjTFt5hfHfhFtFghzIG1haWRliBuYW1lPw==',

@.Login = N'xyz@.abc.com',

@.IdNumber = N'00009'

--

What am I missing?

Is Pwd a variable or constant set to "@.Pwd", login to "@.Login", IdNum to "@.IdNum", and "SP_UPDPWD" to the appropriate string for your stored procedure?

Also, is this using an IDBManager pattern?

Thanks,

John

|||

Never mind. found the problem

The parameter for Password was not spelt right!

Error in Transfer SQL Server Object Task

Hi all,

I am facing problem while using Transfer SQL Server Object Task in SSIS, it is not importing table & its attributes.

The details settings of Transfer SQL Server Object Task as follows:
clicking edit - Objects(left pane)
Right pane
Connection
Source Connection existing server name 1
Source Database ganny1
Destination Connection existing server name 1
Destination Database ganny2
Destination copy objects
Objects to copy - copy all tables - true
Table Objects
CopyPrimaryKeys - True
CopyForiegnKeys - True

After setting all this configurations, Executing task, I am not able to get table in my destination database.
Please suggest me to solve the issue.

Thanks in advance
Karna

What is the value of CopySchema? If it's false, trying setting it to true.

Error in the Stored Procedure

I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ???

Thanks friends..

Procedure 'UpdateRowsReorderUp' expects parameter '@.nextlowestsortID', which was not supplied.

CREATE PROCEDURE [dbo].[UpdateRowsReorderUp]

(

@.intsortID int,

@.nextlowestsortID int,

@.MemberID int

)

AS

Select @.nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@.intsortID order by SortID DESC)

UPDATE SelectedCredits SET SortID= CASE

WHEN SortID = @.nextlowestsortID then @.intsortID

WHEN SortID = @.intsortID then @.nextlowestsortID ELSE SortID End

WHERE MemberID = @.MemberID

SELECT * FROM SelectedCredits WHERE MemberID= @.MemberID ORDER BY SortID

GO

**************

// this is my script on the page

void moveup(Object s, DataListCommandEventArgs e) {

objcmd= new SqlCommand("UpdateRowsReorderUp",objConn);

objcmd.CommandType = CommandType.StoredProcedure;

objcmd.Parameters.Add("@.intsortID",intsortID);

objcmd.Parameters.Add("@.MemberID",Session["MemberID"]);

objRdr= objcmd.ExecuteReader();

dlSelCredits.DataSource = objRdr;

dlSelCredits.DataBind();

objRdr.Close();

objConn.Close();

BindData();

}

You are missing the @.nextlowestsortid parameter in your code. Add it between the other two.
|||

Thanks for your reply . I'm storing a value in the @.nextlowestsortid using a SELECT statement.I am not assigning any value outside so that i can pass it into the parameter. for example @.intsortid i'm assiging intsortid through the code but what can i assign to this? Sorry to ask like this i am a newbie in this field..

Can i write the SELECT statement in different way so that there is no need add any parameter through the code ??

|||If the @.nextlowestsortid parameter is an internal parameter, remove itfrom the parameter list and declare it after the AS, like this:
DECLARE @.nextlowestsortid int
Then you stored procedure will only have two parameters that you supply, and the error will go away.
Sam
|||

Thank you very much Sam .. I am really grateful to you ..

You solved my problem .. that was the perfect solution ..

cheers mate

Error in Stored Procedure while working with temp. table

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :

String or binary data would be truncated.The statement has been terminated.

The procedure i created is as :

ALTER PROCEDUREfetchpersondetails

AS

CREATE Table#tempperson (personIDint,FirstNamenvarchar(200),LastNamenvarchar(250),titlenvarchar(150),Professionnvarchar(200),StreetAddressnvarchar(300),

StateAddressnvarchar(200),CityAddressnvarchar(200),CountryAddressnvarchar(200),ZipAddressnvarchar(200),Telephonenvarchar(200),Mobilenvarchar(200),

Faxnvarchar(200),Emailnvarchar(250),NotesPubntext,Affiliationnvarchar(200),Categorynvarchar(200))

Insert into#tempperson

SELECTdbo.tblperson.personID,ISNULL(dbo.tblperson.fName, N'') +' '+ISNULL(dbo.tblperson.mName, N'')ASFirstName, dbo.tblperson.lnameASLastName,

dbo.tblperson.honorASTitle, dbo.tblperson.titleASProfession, dbo.tblperson.street +' '+ISNULL(dbo.tblperson.suite, N'')ASStreetAddress,

dbo.tblperson.cityAScityaddress, dbo.tblperson.stateASstateaddress, dbo.tblperson.postalCodeASzipaddress,

dbo.tblperson.Phone1 +','+ISNULL(dbo.tblperson.Phone2, N'') +','+ISNULL(dbo.tblperson.Phone3, N'')ASTelephone,

dbo.tblperson.mobilePhoneASmobile, dbo.tblperson.officeFax +','+ISNULL(dbo.tblperson.altOfficeFax, N'') +','+ISNULL(dbo.tblperson.altOfficeFax2,

N'')ASFax,ISNULL(dbo.tblperson.Email1, N'') +','+ISNULL(dbo.tblperson.Email2, N'') +','+ISNULL(dbo.tblperson.Email3, N'')ASEmail,

dbo.tblperson.notesASNotesPub, dbo.tblOrganizations.orgNameASAffiliation, dbo.tblOrganizations.orgCategoryASCategory,

dbo.tblCountry.countryNameFullAScountryaddress

FROMdbo.tblpersonINNER JOIN

dbo.tblOrganizationsONdbo.tblperson.orgID = dbo.tblOrganizations.orgIDINNER JOIN

dbo.tblCountryONdbo.tblperson.countryCode = dbo.tblCountry.ISOCode

please let me know the solurion of this error.

What the error msg means is that you are trying to insert a value that is larger than what the column can take. Check your column lengths in the temp table and the data coming in from your SELECT statment. Apparently, you have crossed the limit somewhere.

|||

I made it working.Its not the column length that creating problem.

Error in stored procedure

I have a stored procedure that will copy data from one table to another, but
I am having problem doing so from a 'text' field from table 1 to table 2.
Here is my SP:
<%
Dim Addtemplates__FirmId
Addtemplates__FirmId = "4"
if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
Dim Addtemplates__CaseId
Addtemplates__CaseId = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
Request.Querystring("caseid")
Dim Addtemplates__SecLtr
Addtemplates__SecLtr = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
Request.Querystring("caseid")
Dim Addtemplates__LtrName
Addtemplates__LtrName = "0"
if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
Templatesfiltered("LtrName")
Dim Addtemplates__LtrBody
Addtemplates__LtrBody = "0"
if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
Templatesfiltered("LtrBody")
%>
<%
set Addtemplates = Server.CreateObject("ADODB.Command")
Addtemplates.ActiveConnection = MM_eimmigration_STRING
Addtemplates.CommandText = "dbo.Addtemplatestocase"
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.RETURN_VALUE",
3, 4)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
1,4,Addtemplates__FirmId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
1,4,Addtemplates__CaseId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr", 200,
1,50,Addtemplates__SecLtr)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName", 200,
1,100,Addtemplates__LtrName)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody", 200,
1,16000,Addtemplates__LtrBody)
Addtemplates.CommandType = 4
Addtemplates.CommandTimeout = 0
Addtemplates.Prepared = true
Addtemplates.Execute()
%>
LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
works fine, but I need more space that is why I need it to be text.
This is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Line # 128 is: Addtemplates.Execute()
Any clues with the information above ?
AleksBlob must be handled differently than regular string. See these for some
info.
HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/d_efau...b;en-us;1949_75
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/d_efau...;EN-US;q258_038
--
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that will copy data from one table to another,
>but I am having problem doing so from a 'text' field from table 1 to table
>2.
> Here is my SP:
> <%
> Dim Addtemplates__FirmId
> Addtemplates__FirmId = "4"
> if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
> Dim Addtemplates__CaseId
> Addtemplates__CaseId = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
> Request.Querystring("caseid")
> Dim Addtemplates__SecLtr
> Addtemplates__SecLtr = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
> Request.Querystring("caseid")
> Dim Addtemplates__LtrName
> Addtemplates__LtrName = "0"
> if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
> Templatesfiltered("LtrName")
> Dim Addtemplates__LtrBody
> Addtemplates__LtrBody = "0"
> if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
> Templatesfiltered("LtrBody")
> %>
> <%
> set Addtemplates = Server.CreateObject("ADODB.Command")
> Addtemplates.ActiveConnection = MM_eimmigration_STRING
> Addtemplates.CommandText = "dbo.Addtemplatestocase"
> Addtemplates.Parameters.Append
> Addtemplates.CreateParameter("@.RETURN_VALUE", 3, 4)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
> 1,4,Addtemplates__FirmId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
> 1,4,Addtemplates__CaseId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr",
> 200, 1,50,Addtemplates__SecLtr)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName",
> 200, 1,100,Addtemplates__LtrName)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody",
> 200, 1,16000,Addtemplates__LtrBody)
> Addtemplates.CommandType = 4
> Addtemplates.CommandTimeout = 0
> Addtemplates.Prepared = true
> Addtemplates.Execute()
> %>
>
> --
> LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
> works fine, but I need more space that is why I need it to be text.
> This is the error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Line # 128 is: Addtemplates.Execute()
> Any clues with the information above ?
> Aleks
>|||I get this with your link: The system cannot find the file specified.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||Also there is nothing on how to handle it with a stored procedure.
A
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||hmmm...blame it on copy & paste from archive via google. ;)
here are the corrected urls.
http://support.microsoft.com/kb/194975
http://support.microsoft.com/kb/258_038
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eo1h9AKUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>I get this with your link: The system cannot find the file specified.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>|||If you want to manipulate blob inside a sql stored proc, you would want to
use updatetext/writetext.
http://msdn.microsoft.com/library/e..._ua-uz_6i2c.asp
9" target="_blank">http://groups.google.co.uk/group/co...fbdf01
9
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eL9prBKUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Also there is nothing on how to handle it with a stored procedure.
> A
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in sample Northwind?

When I was doing DAO in Access I made sure that my table names did not =
contain any spaces for if they did I had to write my "SQL" statements a =
little differently.
I was following an exmple and had a SQL statment:
sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2" This is in ASP with =
Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. =
But if you go to the tables in the Northwind database in SQL 2000 it is =
shown as Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. =
So what I am wondering is when tables in SQL 2000 contain spaces like =
the above Order Deatails, is the SQL statement as I have written it =
irrelevant? Shouldn't it be:
sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2"
?
Thanks.
--=20
George Hester
__________________________________
Hi,
Use SQARE brackets [] incase if you have space inbetwen the object names.
sSQL1 = "UPDATE [Order Details] SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
Thanks
Hari
MCDBA
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:e#lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not
contain any spaces for if they did I had to write my "SQL" statements a
little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
This is in ASP with Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But
if you go to the tables in the Northwind database in SQL 2000 it is shown as
Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. So
what I am wondering is when tables in SQL 2000 contain spaces like the above
Order Deatails, is the SQL statement as I have written it irrelevant?
Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
?
Thanks.
George Hester
__________________________________
|||The ANSI SQL compliant way is to enclose the name inside double-quotes. SQL Server also support square
brackets. I prefer the ANSI SQL Compliant way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not contain any spaces for if they did I
had to write my "SQL" statements a little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2" This is in ASP with
Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But if you go to the tables in the
Northwind database in SQL 2000 it is shown as Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. So what I am wondering is when tables
in SQL 2000 contain spaces like the above Order Deatails, is the SQL statement as I have written it
irrelevant? Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
?
Thanks.
George Hester
__________________________________
|||OK then it would be:
sSQL1 =3D "UPDATE ""Order Details"" SET Quantity=3D5 WHERE OrderID=3D3 =
AND ProductID=3D2"
for VBScript?
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside =
double-quotes. SQL Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message =
news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not =
contain any spaces for if they did I
> had to write my "SQL" statements a little differently.
>=20
> I was following an exmple and had a SQL statment:
>=20
> sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
>=20
> Anyway if you look at this statement the table is called OrderDetails. =
But if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the =
space.
>=20
> This is the only table in the Northwind database that conatins a =
space. So what I am wondering is when tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL =
statement as I have written it
> irrelevant? Shouldn't it be:
>=20
> sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 =
AND ProductID=3D2"
> ?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>
|||Yep. Better yet, always quality the object name with the owner. :-)
UPDATE dbo."Order Details" SET...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ujt2XRFQEHA.3220@.TK2MSFTNGP09.phx.gbl...
OK then it would be:
sSQL1 = "UPDATE ""Order Details"" SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
for VBScript?
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside double-quotes. SQL Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not contain any spaces for if they did I
> had to write my "SQL" statements a little differently.
> I was following an exmple and had a SQL statment:
> sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
> Anyway if you look at this statement the table is called OrderDetails. But if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the space.
> This is the only table in the Northwind database that conatins a space. So what I am wondering is when
tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL statement as I have written it
> irrelevant? Shouldn't it be:
> sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
> ?
> Thanks.
> --
> George Hester
> __________________________________
>

Error in sample Northwind?

When I was doing DAO in Access I made sure that my table names did not = contain any spaces for if they did I had to write my "SQL" statements a = little differently.
I was following an exmple and had a SQL statment:
sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND = ProductID=3D2" This is in ASP with = Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. = But if you go to the tables in the Northwind database in SQL 2000 it is = shown as Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. = So what I am wondering is when tables in SQL 2000 contain spaces like = the above Order Deatails, is the SQL statement as I have written it = irrelevant? Shouldn't it be:
sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 AND = ProductID=3D2"
?
Thanks.
-- George Hester
__________________________________Hi,
Use SQARE brackets [] incase if you have space inbetwen the object names.
sSQL1 = "UPDATE [Order Details] SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
Thanks
Hari
MCDBA
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:e#lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not
contain any spaces for if they did I had to write my "SQL" statements a
little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
This is in ASP with Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But
if you go to the tables in the Northwind database in SQL 2000 it is shown as
Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. So
what I am wondering is when tables in SQL 2000 contain spaces like the above
Order Deatails, is the SQL statement as I have written it irrelevant?
Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
?
Thanks.
--
George Hester
__________________________________|||The ANSI SQL compliant way is to enclose the name inside double-quotes. SQL Server also support square
brackets. I prefer the ANSI SQL Compliant way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not contain any spaces for if they did I
had to write my "SQL" statements a little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2" This is in ASP with
Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But if you go to the tables in the
Northwind database in SQL 2000 it is shown as Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. So what I am wondering is when tables
in SQL 2000 contain spaces like the above Order Deatails, is the SQL statement as I have written it
irrelevant? Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
?
Thanks.
--
George Hester
__________________________________|||OK then it would be:
sSQL1 =3D "UPDATE ""Order Details"" SET Quantity=3D5 WHERE OrderID=3D3 =AND ProductID=3D2"
for VBScript?
-- George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =in message news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside =double-quotes. SQL Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
> > -- > Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message =news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not =contain any spaces for if they did I
> had to write my "SQL" statements a little differently.
> > I was following an exmple and had a SQL statment:
> > sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND =ProductID=3D2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
> > Anyway if you look at this statement the table is called OrderDetails. = But if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the =space.
> > This is the only table in the Northwind database that conatins a =space. So what I am wondering is when tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL =statement as I have written it
> irrelevant? Shouldn't it be:
> > sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 =AND ProductID=3D2"
> ?
> Thanks.
> > -- > George Hester
> __________________________________
> >|||Yep. Better yet, always quality the object name with the owner. :-)
UPDATE dbo."Order Details" SET...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ujt2XRFQEHA.3220@.TK2MSFTNGP09.phx.gbl...
OK then it would be:
sSQL1 = "UPDATE ""Order Details"" SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
for VBScript?
--
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside double-quotes. SQL Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not contain any spaces for if they did I
> had to write my "SQL" statements a little differently.
> I was following an exmple and had a SQL statment:
> sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
> Anyway if you look at this statement the table is called OrderDetails. But if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the space.
> This is the only table in the Northwind database that conatins a space. So what I am wondering is when
tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL statement as I have written it
> irrelevant? Shouldn't it be:
> sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
> ?
> Thanks.
> --
> George Hester
> __________________________________
>

Error in sample Northwind?

When I was doing DAO in Access I made sure that my table names did not =
contain any spaces for if they did I had to write my "SQL" statements a =
little differently.
I was following an exmple and had a SQL statment:
sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2" This is in ASP with =
Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. =
But if you go to the tables in the Northwind database in SQL 2000 it is =
shown as Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. =
So what I am wondering is when tables in SQL 2000 contain spaces like =
the above Order Deatails, is the SQL statement as I have written it =
irrelevant? Shouldn't it be:
sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2"
?
Thanks.
--=20
George Hester
__________________________________Hi,
Use SQARE brackets [] incase if you have space inbetwen the object names
.
sSQL1 = "UPDATE [Order Details] SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
Thanks
Hari
MCDBA
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:e#lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not
contain any spaces for if they did I had to write my "SQL" statements a
little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
This is in ASP with Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But
if you go to the tables in the Northwind database in SQL 2000 it is shown as
Order Details. Note the space.
This is the only table in the Northwind database that conatins a space. So
what I am wondering is when tables in SQL 2000 contain spaces like the above
Order Deatails, is the SQL statement as I have written it irrelevant?
Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND
ProductID=2"
?
Thanks.
George Hester
__________________________________|||The ANSI SQL compliant way is to enclose the name inside double-quotes. SQL
Server also support square
brackets. I prefer the ANSI SQL Compliant way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPEHA
.2128@.TK2MSFTNGP11.phx.gbl...
When I was doing DAO in Access I made sure that my table names did not conta
in any spaces for if they did I
had to write my "SQL" statements a little differently.
I was following an exmple and had a SQL statment:
sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=2"
This is in ASP with
Server.CreateObject("ADODB.Connection")
Anyway if you look at this statement the table is called OrderDetails. But
if you go to the tables in the
Northwind database in SQL 2000 it is shown as Order Details. Note the space
.
This is the only table in the Northwind database that conatins a space. So
what I am wondering is when tables
in SQL 2000 contain spaces like the above Order Deatails, is the SQL stateme
nt as I have written it
irrelevant? Shouldn't it be:
sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND ProductID
=2"
?
Thanks.
George Hester
__________________________________|||OK then it would be:
sSQL1 =3D "UPDATE ""Order Details"" SET Quantity=3D5 WHERE OrderID=3D3 =
AND ProductID=3D2"
for VBScript?
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside =
double-quotes. SQL Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message =
news:e%23lSISoPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not =
contain any spaces for if they did I
> had to write my "SQL" statements a little differently.
>=20
> I was following an exmple and had a SQL statment:
>=20
> sSQL1 =3D "UPDATE OrderDetails SET Quantity=3D5 WHERE OrderID=3D3 AND =
ProductID=3D2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
>=20
> Anyway if you look at this statement the table is called OrderDetails. =
But if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the =
space.
>=20
> This is the only table in the Northwind database that conatins a =
space. So what I am wondering is when tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL =
statement as I have written it
> irrelevant? Shouldn't it be:
>=20
> sSQL1 =3D "UPDATE 'Order Details' SET Quantity=3D5 WHERE OrderID=3D3 =
AND ProductID=3D2"
> ?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||Yep. Better yet, always quality the object name with the owner. :-)
UPDATE dbo."Order Details" SET...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ujt2XRFQEHA.3
220@.TK2MSFTNGP09.phx.gbl...
OK then it would be:
sSQL1 = "UPDATE ""Order Details"" SET Quantity=5 WHERE OrderID=3 AND Product
ID=2"
for VBScript?
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:e519lOxPEHA.1644@.TK2MSFTNGP09.phx.gbl...
> The ANSI SQL compliant way is to enclose the name inside double-quotes. SQ
L Server also support square
> brackets. I prefer the ANSI SQL Compliant way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message news:e%23lSISoPE
HA.2128@.TK2MSFTNGP11.phx.gbl...
> When I was doing DAO in Access I made sure that my table names did not con
tain any spaces for if they did I
> had to write my "SQL" statements a little differently.
> I was following an exmple and had a SQL statment:
> sSQL1 = "UPDATE OrderDetails SET Quantity=5 WHERE OrderID=3 AND ProductID=
2" This is in ASP with
> Server.CreateObject("ADODB.Connection")
> Anyway if you look at this statement the table is called OrderDetails. Bu
t if you go to the tables in the
> Northwind database in SQL 2000 it is shown as Order Details. Note the spa
ce.
> This is the only table in the Northwind database that conatins a space. So what I
am wondering is when
tables
> in SQL 2000 contain spaces like the above Order Deatails, is the SQL state
ment as I have written it
> irrelevant? Shouldn't it be:
> sSQL1 = "UPDATE 'Order Details' SET Quantity=5 WHERE OrderID=3 AND Product
ID=2"
> ?
> Thanks.
> --
> George Hester
> __________________________________
>

Error in Replication how to continue

I have transactional Replication from A to B.

My distribution agent failed becos of a primary key violation error in a table.[i have not included the skip error option].

Then the replication stopped.
The transactions on other tables are also not replicated to B.

IF an error is encountered in one of the tables will the replication stop entirely??.Is there no way for the replication to continue for the rest of the tables..
Pls clarifyI don't think you can skip this error. To use Replication you have to have a primary key in the table you want to replicate.|||The transactions are processed in a serialized fashion, meaning that if one failed nothing else goes through.

Error in Reading Float data

I am using SQL Server 2000, VS 2003

I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.

while (dr.Read())
{
Education e = new Education();
e.EducationId = dr.GetInt32(0);
e.Country = dr.GetInt32(1);
e.InstitutionName = dr.GetString(2);
e.Grade = dr.GetString(3);
e.CGPA = dr.GetFloat(4); // ERROR HERE
e.Percentage = dr.GetFloat(5);
e.PassingYear = dr.GetString(6);
}

where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server

GPA can be handled with Decimal or Numric data type, there are some tasks like complex calculus that require Float data type because the T-SQL functions are in Float but student grade is simple Arithmetic so you can use Decimal instead of Float. The reason Float is very unstable, long time SQL Server users use it for calculations but convert the value to Decimal for storage because you can set precision and scale with Decimal. Hope this helps.|||

Avoid floats, reals, single, doubles if at all possible in the database. They are imprecise numbers and well... They cause all kinds of weird issues. It's not SQL Server issues, it's just issues with those data types in general.

That said, I don't see how it's causing your problem. If you know the column names of the record format, try:

e.CGPA=dr("CGPA")

That assumes the field/column is named CGPA of course.

Or try:

Dim o as object
o=dr("CGPA")
e.CGPA=o

that way you can see what "o" is before you try and convert it to whatever type e.CGPA is.

|||Another option is a simple ANSI ALTER TABLE to change FLOAT to DECIMAL. Run a search for ALTER TABLE in SQL Server BOL (books online). Hope this helps.