|
i have written a trigger as followed:
<br />
-- ================================================<br />
-- Template generated from Template Explorer using:<br />
-- Create Trigger (New Menu).SQL<br />
--<br />
-- Use the Specify Values for Template Parameters <br />
-- command (Ctrl-Shift-M) to fill in the parameter <br />
-- values below.<br />
--<br />
-- See additional Create Trigger templates for more<br />
-- examples of different Trigger statements.<br />
--<br />
-- This block of comments will not be included in<br />
-- the definition of the function.<br />
-- ================================================<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
-- =============================================<br />
-- Author: <Author,,Name><br />
-- Create date: <Create Date,,><br />
-- Description: <Description,,><br />
-- =============================================<br />
CREATE TRIGGER tr_stagingEmployer_Update<br />
ON Employer<br />
AFTER UPDATE<br />
AS<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
INSERT INTO stagingEmployer<br />
SELECT<br />
*, 'U' as [Action]<br />
FROM<br />
Inserted<br />
GO
BUT i get error stating:
<br />
Msg 311, Level 16, State 1, Procedure tr_stagingEmployer_Update, Line 14<br />
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.<br />
what is wrong with this query?
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
From a Microsoft article on the subject:
SQL Server 2005 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Okay,
so am i wrong when i say that the query is fine the way it is... it is due to the fact that one of the columns is of image type in my table? If it wasn't for that the query would have worked???
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
This article[^] provides more information on the subject.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
 I created one View in my SQL Database on which i set an filter using following way's to fill Dataset at runtime. In which first way take less time then second way but using first way i can not filter the data using Between SQL Keyword (i.e. Between 03/01/2008 and 04/01/2008), is there any other way to filter the data for datatime column?
First way:
SELECT * FROM View WHERE (CONVERT(nvarchar, [Date], 103) = N'04/01/2008')
its take Execution Time = 0:00:01
Second way:
SELECT * FROM View WHERE ([Date] = CONVERT(DATETIME, '2008-01-04 00:00:00', 102))
its take Execution Time = 0:00:16
please help me thank's in advanced.
thanks and regards
Sasmi
|
|
|
|
|
I don't really see why the times could be significantly different for these two queries. Is the time difference repeatably consistent? (i.e. have you run this through several iterations to ensure that it isn't something like data being cached in memory, etc.)
What happens if you put the conversion of the string outside the SELECT in the second example:
DECLARE @date DATETIME
SELECT @date = CONVERT(DATETIME, '2008-01-04 00:00:00', 102)
SELECT * FROM View WHERE ([Date] = @date)
|
|
|
|
|
The first query is converting a DATETIME value to a string. The date is already valid and needs no validation.
I suspect the second version is first converting the string to a DATETIME with validation. I have never used convert in this way since the 3rd parameter '102' is useless when not converting to a string. I wouldn't be surprised if the orignal string is converted back and forth a couple of times during the process. Try it using CAST('2008-01-04 00:00:00' AS DATETIME) instead of CONVERT.
|
|
|
|
|
Hi all.
I have this SSIS package. It is running on my computer without any problems... not even warnings. When i try to run it on the server... i get this:
Message<br />
Executed as user: TEST\TESTadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:45:04 PM Progress: 2008-01-03 15:45:16.77 Source: Create File and insert header Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.78 Source: Create File and insert header Validating: 50% complete End Progress Progress: 2008-01-03 15:45:16.78 Source: Create File and insert header Validating: 100% complete End Progress Progress: 2008-01-03 15:45:16.80 Source: DataInfo Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.83 Source: DataInfo Validating: 50% complete End Progress Progress: 2008-01-03 15:45:16.88 Source: DataInfo Validating: 100% complete End Progress Progress: 2008-01-03 15:45:16.88 Source: HeaderInfo Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.89 Source: HeaderInfo Va... The package execution fa... The step failed.
i dont know why... but it is failing and i cant determine why ??? any help would be greatly appreciated 
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
You haven't provided enough information to determine what the problem is. Look at the contents of the output window. The details of the error should be listed there.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i dont know where you see output window??? what i have posted to you is what i can see in "Right Click on a job in sql > View History" then you see the log file viewer...
But thank you any way... it was a security issue... i managed to get past the problem by changing the path in a file from \\servername\a$\...
to a:\..... and now it is working fine... (the servername was the name of the server that the job was executing on.) thank you any way.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Support123 wrote: i dont know where you see output window???
Sorry, I assumed that you were using Visual Studio to run the package. If you were, the output window would have provided details of the error.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
you must debug your SSIS package on Server exactly with Business Intelligence Development Studio
This is because there are some problems and error on SSIS packages that you don't aware of them before debugging your package on server, same as pinging an IP from Server that you can't see IP from server but when you try this from your PC this may be correct.
Human knowlege belongs to the world
|
|
|
|
|
It would make more sense if you posted this reply to the OP instead of myself.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Dear all i have a querry,
i want to know how report is generated in mssql 2005,there is something called as report/summry
when we click on report it is generating report like memory uses ,processor uses ,querry execution time.. and all...
can anybody tell me which technology is used there to generate such reports...
thanks in advance
T@SU
|
|
|
|
|
|
Here is what I want to do. I have two separate Access tables.
tbl_One:
RecID primary key for tbl_One
PartNo
Description
<br>
tbl_Two:
ID primary key for tbl_Two
RecID
TypeID</br>
tbl_Two's RecID is taken from RecID in tbl_One. I am doing an INSERT INTO tbl_One (PartNo,Description) VALUES (1111,"First Item").
tbl_One and tbl_Two share a relationship, One-Many, (NO enforcement of Referential Integrity).
How can I get the primary key value (tbl_One's RecID) so that I can also use it when I update (INSERT INTO tbl_Two)?
I'm doing this programmatically from a form that supports tbl_One, but the SQL statement for doing this will definitely help.
Thanks!
Johnny
|
|
|
|
|
You cannot insert into two tables at the same time. You have to execute the INSERT on the first table, get the key generated for that record, then execute the second INSERT on the second table.
|
|
|
|
|
would it be wise to use a trigger in this instance? i haven't worked with triggers as yet but i have to in a project that is coming up... so i would just like to know if triggers would be a good idea in this instance?
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
If he was using SQL Server, I'd say maybe. A stored procedure doing the insert would probably be more prudent, but a trigger is also possible, yes.
Since he's using Access, this isn't possible. Access (Jet database engine) does not support triggers, nor do is there support for "true" stored procedures, so this isn't an option here.
|
|
|
|
|
Thanks for the help/suggestions.
Is there a quick way to get the Key created by the INSERT so that I can readily use it for my field in the second table?
V/R,
John
|
|
|
|
|
|
Thanks. Here is what I ended up doing (similar to what you proposed/found).
I used a recordset pointer to open tbl_One and get the primary key of the last entry. I then incremented this primary key and KEPT A RECORD OF IT so that I can use it in both tables. Since I am working in a loop and doing multiple INSERT INTO statements, I just increment primary key each time and I get the results I desire.
Access VBA code
Dim rstLookupMainTable As New ADODB.Recordset
rstLookupMainTable.Open "tbl_One", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rstLookupMainTable.MoveLast
If rstLookupMainTable.EOF <> True Then
nPrimaryKey = rstLookupMainTable.Fields(0).Value 'primary key field
Else
nPrimaryKey = 0
End If
rstLookupMainTable.Close
I then proceed with nPrimaryKey and perform my INSERT INTO tbl_One making sure to include the primary key field
Thanks,
John John
|
|
|
|
|
john john mackey wrote: Since I am working in a loop and doing multiple INSERT INTO statements, I just increment primary key each time and I get the results I desire.
That's a really, really bad idea. The database doesn't keep records in any particular order in the table, so the key in the last record may not be the actual last key used. You ALWAYS let the database assign the keys and get it from the database when it's assigned. NEVER assume that the (last key you got + 1) is going to be the next key you're able to use.
This concept will also work even worse when the number of clients using the database exceeds 1.
|
|
|
|
|
hi guys
i have some problem regarding sql
i want to print the column names of a table,i know table name i dont know columns.
thanks in advance
regards
|
|
|
|
|
AVI51 wrote: i want to print the column names of a table,i know table name i dont know columns.
Since you posted this in the SQL forum, I am assuming that what you actually want is a way to get the column names out of the database rather than any particular presentation of the column names.
If you are using SQL Server (which you don't say) you can use the views in INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
