INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanksHow about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net...
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>sql

INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanksHow about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net...
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>

INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanks
How about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net.. .
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>

Invalide object name 'INSERTED'

In an after insert/update trigger I have the following:
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
..
END
It compiles but when I run it, I get a message: "Invalide object name
'INSERTED'"
But if I take the SET out of the IF like this, it works fine:
SET @.iCustID = (SELECT CustID FROM INSERTED)
IF UPDATE(CustName)
BEGIN
..
END
Can someone explain why?
Thanks,
KeithSorry. My mistake. It doesn't work either way. What does work is if I do
this (I mean it runs without errors):
SELECT CustID FROM INSERTED
IF UPDATE(CustName)
BEGIN
..
END
I need to get CustID into a variable so that I can pass it to a stored
procedure as follows:
Keith
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
EXEC @.bSomeVar = spTest @.iCustID
..
END|||Strange, never had that. I would have suggested that the problem was case
sensitivity (BOL lists the table as 'inserted', not 'INSERTED') but you say
it works when you move the SET out of the IF block
Even if this worked, you would have a problem anyway if more than 1 row was
updated in one go, because you'd be trying to set a numbers of rows to a
scalar variable.
Dan
Keith wrote on Wed, 26 Apr 2006 11:33:59 -0400:

> In an after insert/update trigger I have the following:
> IF UPDATE(CustName)
> BEGIN
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> ...
> END
> It compiles but when I run it, I get a message: "Invalide object name
> 'INSERTED'"
> But if I take the SET out of the IF like this, it works fine:
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> IF UPDATE(CustName)
> BEGIN
> ...
> END
> Can someone explain why?
> Thanks,
> Keith
>|||Geeze. Never mind. Not enough sleep last night. I moved some code from the
trigger to a stored procedure and didnt' change "INSERTED" to the actual
table name in the stored procedure. The error was there, not in the trigger.
Keithsql

Invalid use of INSERT within a function.

Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.You cannot INSERT, UPDATE, or DELETE from tables in a FUNCTION. The examples you probably saw did so to locally defined variables of type TABLE.

You can return a value from a stored procedure to get the new ID. You can do this with an output parameter or with a RETURN in the stored procedure. To get the value of the RETURN, execute the stored procedure like this:

DECLARE @.ResultCode int
EXEC @.ResultCode = uspGetMyGroup

Originally posted by saderax
Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.

Invalid Object Name

I am getting an error 'Invalid Object Name' when I try and insert a row yet I can query the same table and get results??
Please Help!Your login probably does not have permission to access the table, either because it has not been set specifically, or the table is not owned by either dbo or the login you are using.

If these are not the problems, then post your code so someone online can review it.|||I am logged in as SA, so I assumed I have permission. But I have also tried running the scripts below when logged in as 'sysdba' user.

Here is a line of code that gives the Invalid Object Name:-

insert into sysdba.hot_list ('HOT_LISTID', 'Jan' , 'type_of_activity') values ('TEMPHOT00JAN' ,'0' ,'Meeting')

This line executes without error:-

truncate table sysdba.hot_list

I must be missing something really obvious!

Thanks
Graham|||I'd suggest:insert into sysdba.hot_list ([HOTLISTID], [Jan] , [type_of_activity])
values ('TEMPHOT00JAN' ,'0' ,'Meeting')...or do without column name quoting at all.

-PatP|||trying...

insert into sysdba.hot_list ([HOTLISTID], [Jan] , [type_of_activity])
values ('TEMPHOT00JAN' ,'0' ,'Meeting')

...didn't work.

I need the column headers as there are more fields in the table than I am inserting with this script.

Thanks
Graham|||Please cut and paste the error message and post it for us.

-PatP|||Here it is:-

Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'sysdba.TEMP_HOT_LIST'.|||Does the TEMP_HOT_LIST table exist, or just the HOT_LIST table? Could the "problem child" be addressed in a trigger perhaps?

-PatP

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
DanSounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
DanSounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
--
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan
Sounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid connection string on SQLXMLBulkLoad

Hi.

I'm using SQLXMLBulkLoad to insert data into the SQL database. It worked with the test database on my PC but when I changed it into a VPS it throws an error: Invalid Connection String. This connection string that I used worked in my program before the addition of 'provider = sqloledb'.

Here's the connection string:

@."Provider = sqloledb; Data Source=CRM;Initial Catalog=Works;Persist Security Info=True;User ID=userid;Password=password;Asynchronous Processing=True";

Anyone?

heres the error code:
-2147217887If anyone is experiencing the same problem, I just edited my connection string manually to the VPS and it worked.

Invalid connection string on SQLXMLBulkLoad

Hi.

I'm using SQLXMLBulkLoad to insert data into the SQL database. It worked with the test database on my PC but when I changed it into a VPS it throws an error: Invalid Connection String. This connection string that I used worked in my program before the addition of 'provider = sqloledb'.

Here's the connection string:

@."Provider = sqloledb; Data Source=CRM;Initial Catalog=Works;Persist Security Info=True;User ID=userid;Password=password;Asynchronous Processing=True";

Anyone?

heres the error code:
-2147217887
If anyone is experiencing the same problem, I just edited my connection string manually to the VPS and it worked.

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid character value for cast specification

Hi: gurus,
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>