Why This Trigger Doesn't Work In Sql 2005
Mar 28, 2008
hi eveyrone,
I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!
CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)
select @UPSID = cast(inserted.UPSID as varchar)
from inserted
commit
set @Cmmd = 'c:TasksUpdTrackingUpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
Thanks in advance.
lyw
View 1 Replies
ADVERTISEMENT
Mar 28, 2008
hi eveyrone,
I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!
CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)
select @UPSID = cast(inserted.UPSID as varchar)
from inserted
commit
set @Cmmd = 'c:TasksUpdTrackingUpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
Thanks in advance.
lyw
View 8 Replies
View Related
Mar 31, 2003
Hi,
I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .
Any hints :confused: ?
TIA,
-Gernot
Here is the statement (line 28 is marked with ***):
CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int
SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED
BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
View 4 Replies
View Related
Jan 18, 2006
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger:
------------------------------------------------------------
ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END
SET NOCOUNT ON
View 5 Replies
View Related
Oct 3, 2007
Hi Everyone,
I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter.
What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:
BouleID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0
Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
I am using SQL Management Studio Express, and SQL Server Express.
Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?
Code Block
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of
-- the row being inserted>
-- =============================================
CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger
ON dbo.dbCG_Disposition
AFTER INSERT AS
BEGIN
DECLARE @rowsAffected int,
@msg varchar(2000), --for error message
@BouleID varchar(6) -- and do I need to enter more than one?
SET @rowsAffected = @@rowcount
IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time.
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SET ROWCOUNT 0
BEGIN TRY
--VALIDATION BLOCK Leave this alone for now
SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.
UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1
SET Iter = ( Iter + 1 ) --
WHERE BouleID = @BouleID --
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRANSACTION
--or this will get rolled back
EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.
DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE, 16, 1)
END CATCH
View 6 Replies
View Related
Feb 12, 2007
I have an active x script written in vb that just counts the records in a table and fails the step if there are not enough records. It is simple and effective. But when I transfered this task to my SSIS package I get a function not found error.
I am not familiar with active x or vb script so I have no idea what is wrong. I can do without while I build the package but I need it in the end to prevent possible problems....
Here is the script...
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim myConn
dim myRecordset
dim iRowCount
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")
' Init ADO Connection object
With myConn
.ConnectionString = "File Name=D:Data Linksmusic_load.udl;" 'Full path to the UDL
.Open
End With
mySQLCmdText = "Select 'totalcount' = Count(*) from fulltext"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("totalcount")
If iRowCount.Value < 1000000 then
' MsgBox "The number of products is: " & iRowCount.Value & ". The total number is wrong"
Main = DTSTaskExecResult_Failure
Else
' MsgBox "The number of products is: " & iRowCount.Value & ". The total number is good"
Main = DTSTaskExecResult_Success
End If
End Function
View 2 Replies
View Related
Sep 22, 2007
Hi,
Can anybody help me with the following problem:
I have Vista Ultimate installed and within Vista .NET Framework 3.0 is installed as a part of the operating system so I can't remove 3.0
Now for MS SQL Server 2005 Express I need .NET Framework 2.0 and the SQL Server 2005 doesn't work with .NET Framework 3.0
Can't install 2.0, get the message that 3.0 is already installed.
Is there another version of SQL Server that works with .NET Framework 3.0 and runs under Vista Ultimate?
Please help me!!
René
View 3 Replies
View Related
Feb 29, 2008
Ok, I'm a long time reader of this forum and just about everytime I come here looking for an answer its allready been posted and answered. But I can't seem to find this one anywhere. Sorry if its allready been posted...Just point me in the right direction.
Ok, Ive been coding in VB2005 for a while but i'm new to SQL stored procedures. This is prolly going to sound stupid but I don't know where to start. Heres what I need to do.
I have a stored procedure that requires a parameter. I can pass that parameter to the stored procedure and execute it just fine. But I need the result thats returned when the stored procedure is executed. I can't seem to find anyway to return the Result back to me. DataReaders keep blowing up and I have no Idea Why????? Here is how i'm passing the param to the stored proc.
I'm running MS SQL BTW.
Dim myConnection As New SqlClient.SqlConnection("yadayada;Database=BlaBlaBla;Trusted_Connection=True;")
Dim cmd As New SqlClient.SqlCommand("NameOfStoredProc")
cmd.Connection = myConnection
cmd.CommandType = CommandType.StoredProcedure
Dim sqlParam As SqlClient.SqlParameter
sqlParam = cmd.Parameters.Add("Variable", SqlDbType.VarChar, 22)
sqlParam.Value = Address 'declared higher up.....its just an int
myConnection.Open()
cmd.ExecuteNonQuery()
myConnection.Close()
Thats all great, but I need to read back the results the stored proc returns. If I execute this in server management studio, it returns a result. But nothing I try in my asp page works. Any help would be Great. Code samples are good too.
View 12 Replies
View Related
Jul 5, 2007
Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).
Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"
NB: if using the local drives, it work
Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder
SQL Statement:
exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)
Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
View 1 Replies
View Related
Jul 23, 2007
When I try to add a SQL Server Database as a new item into the App_Data folder of my Web Project, I get this error demanding that SQL Server Express has to be installed. That is the stupidest thing I have ever heard of. Is there some setting or something to make VS 2005 Pro work with a standard SS database? I don't want to use the express edition. I can't use SS's management studio with that.
Does Microsoft monitor this forum? I thought they were going to be taking technology forward; not backwards. I'm losing a lot of time having to install and uninstall and reinstall to get the environment into a mode that works lke I want it to work. If they didn't intend for SS standard edition to be used with VS 2005 Pro, then why did they include SS developers edition in the Pro package?
View 2 Replies
View Related
Dec 14, 2007
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
What am I doing wrong?
View 4 Replies
View Related
Mar 3, 2007
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010
eibcactive.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
View 12 Replies
View Related
May 13, 2008
I have had the same Error 29506 that a lot of people are having when installing SP2 for SQL 2005. I've tried the install with myself (a Domain Admin), local Administrator, cascaded full rights down the entire file system structure and still not luck. One thing I'm wondering if it is hanging me up is that all of my databases and logs are not on C:. They are on LUNS on a NetApp SAN (Data is on M: and Logs is on L:). Even the system databases (Master, Model, etc.) are on the LUNs. The error logs referenced permissions to the data directory under the default installation path on C:. Anyone else have this problem? Got a fix? I really don't want to migrate all of my data back to the local machine, apply the patch, them migrate back. Surely this SP should be able to read the data location from the SQL engine. And surely others have their databases on SANs.... I'm at a loss.
Thanks.
View 3 Replies
View Related
May 6, 2008
OK I am really going nuts here. I have a trigger that will work if I insert a row using SQL MGMT Studio or VS DB explorer, but it will not insert using the tableadapter.update() command.
What is also strange is that I have to execute the query in the VS DB Explorer or SSMSE to get them to fill in the default values (trigger).
No matter what they don't work in the debug application:
Below is the trigger:
Code Snippet
ALTER TRIGGER [dbo].[weight_train_log_InsertTrigger]
ON [dbo].[weight_train_log]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE [weight_train_log]
SET [CreationDate] = GETDATE()
FROM inserted
WHERE inserted.[GUID] = [weight_train_log].[GUID]
END
View 1 Replies
View Related
Sep 15, 2005
I'm pretty new to DTS, so forgive me if this is basic. I created a simple DTS package to run a query and export it to a text file. I can execute the package fine from my workstation through EM, but when I try to execute the job to run the package I get this error:
Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied.
I think that maybe SQL Agent doesn't have the right permissions to write to that network drive. What should the permissions be?
View 3 Replies
View Related
Nov 10, 2004
This is probably very simple, but I can't get passed this problem.
I have a report in MS Access that uses info generated by a query. One of the text fields in the query contains either the word 'Select' or the name of a course.
The report should display a space if the value is 'Select', or the actual value of the field in any other case. The field can never contain a null value.
I've used:
=IIf([optVoc1]="Select","",[optVoc1])
in the text box on the report, but this only returns #error regardless of the actual content of the field.
What am I doing wrong?
Regards,
BD
View 5 Replies
View Related
Aug 27, 2004
Hi all,
I create and schedule a SQL job to run every minute to update a table base on certain condition but it doesn't work. Job history says successful every time but the table doesn't get updated.
However if I move it to Query Analyzer and run it under dba, it will work. Thinking that it may have to do with the user the job run as, I then change run as user from self to dba. But still SQL job won't update my table.
Anything about user permission or security that I can check? Or it there any other possibility?
TIA
View 1 Replies
View Related
Apr 26, 2007
When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing
select
eqnow.empnumber,
eqnow_names.empnumber,
eqnow_names.names
--delete
from
eqnow
inner join eqnow_names
on eqnow.empnumber = eqnow_names.empnumber
where
eqnow_names.names is null
i get this error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.
View 3 Replies
View Related
Oct 21, 2007
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'user instances enabled' does not exist, or it may be an advanced option.
Valid configuration options are
View 1 Replies
View Related
Apr 19, 2008
Hi to all, I'm building (and learn) an application with VB Express. In "edit dataset with designer" I've build this sql query:
SELECT tbl_soggetto.[ID Soggetto], tbl_soggetto_tipo.Tipo, tbl_soggetto.[Cognome/Denominazione], tbl_soggetto.Nome, tbl_soggetto.Indirizzo, tbl_soggetto.CAP, tbl_soggetto.Città , tbl_soggetto.Provincia, tbl_soggetto.[Telefono 1], tbl_soggetto.[Telefono 2], tbl_soggetto.[Telefono 3], tbl_soggetto.[Telefono 4], tbl_soggetto.[eM@il 1], tbl_soggetto.[eM@il 2], tbl_soggetto.Note
FROM tbl_soggetto INNER JOIN tbl_soggetto_tipo ON tbl_soggetto.[ID Tipo] = tbl_soggetto_tipo.[ID Tipo]
WHERE (tbl_soggetto.[Cognome/Denominazione] LIKE '%' + @Testo + '%')
The LIKE doesn't work!
I call the query with Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome(Me.txt_trova.Text.Trim)
But with LIKE '%ABC%' work!
Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome()
Someone can help me? Thanks...
View 12 Replies
View Related
Dec 11, 2006
When I try to install the problem I get the following error.
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The log tells me nothing useful. I can't start the thing manually because after clicking cancel on the error message, the installer proceeds to roll back the installation.
How do I fix this problem?
View 3 Replies
View Related
Jun 8, 2005
This is the autogenerated code from the SelectCommand of my DataAdapter, except the red text. This DataAdapter is used to fill a DataGrid. What I want to do, is to calculate the total memory (4 slots) / PC.This code makes the sum of all memory of all PC's together.I'm not sure if the group by clause is needed here ...Me.OleDbSelectCommand1.CommandText = "SELECT PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type AS Case_Type, Processor_T" & _"ype.Type AS Processor_Type, Processor_Snelheid.Snelheid AS Processor_Snelheid, " & _"(SELECT SUM(Memory) FROM Memory, PC, RAM WHERE RAM.PcID = PC.ID AND RAM.GrootteID = Memory.ID)" & _"AS Memory, OS.Naam AS OS, OS_SP.Nummer AS OS_SP, Gebru" & _"iker.Naam AS Gebruiker_Naam, Status.Status, PC.Tagged FROM (Status RIGHT OUTER J" & _"OIN ((((((((PC LEFT OUTER JOIN (RAM LEFT OUTER JOIN Geheugen ON RAM.GrootteID = " & _"Geheugen.ID) ON PC.ID = RAM.PcID) LEFT OUTER JOIN Case_Type ON PC.Case_TypeID = " & _"Case_Type.ID) LEFT OUTER JOIN OS_SP ON PC.OS_SpID = OS_SP.ID) LEFT OUTER JOIN Ge" & _"bruiker ON PC.GebruikersID = Gebruiker.ID) LEFT OUTER JOIN Processor_Snelheid ON" & _" PC.Processor_SnelheidID = Processor_Snelheid.ID) LEFT OUTER JOIN Processor_Type" & _" ON PC.Processor_TypeID = Processor_Type.ID) LEFT OUTER JOIN OS ON PC.OsID = OS." & _"ID) LEFT OUTER JOIN Switchbox_Details ON PC.ID = Switchbox_Details.PcID) ON Stat" & _"us.ID = PC.StatusID) GROUP BY PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type, " & _"Processor_Type.Type, Processor_Snelheid.Snelheid, OS.Naam, OS_" & _"SP.Nummer, Gebruiker.Naam, Status.Status, PC.Tagged"I would like to know how to calculate the total memory for each separate PC.Hope you can help me.
View 5 Replies
View Related
Sep 20, 2000
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error
Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119
Connection Broken
View 1 Replies
View Related
Mar 1, 2001
I try to copy a DB from one server to another. On the target server an older version of the DB has been deleted and I now try to attach the new version using "sp_attach_db DBname, Filelocation", but I always get an error "Device Activation error. The physical file name 'D:mssql7dataAgency_log.ldf' may be incorrect"
"Database 'Agency' cannot be Created"
To me it seems that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create a new database and replace the mdf file, but nothing works.
View 3 Replies
View Related
Nov 11, 1999
I'm working with mssql 6.5
I have an primary key column with Identity property.
And at the moment server doesn't insert the proper value to this column.
Error message is following
Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'Spot' with unique index 'XPKSpot'
Command has been aborted.
The datatype of this column is int, and number of rows ~17000.
If I execute select @@identity it returns null.
View 4 Replies
View Related
Aug 2, 2004
I'm combining first name, last name, middle name, and an ID number together into an alias. Then I need to match that alias with a variable passed to the page (its a search results page). The problem is it claims that there is no table with the name of my alias. Anyone know what I'm doing wrong?
A mockup of the SQL looks like this:
SELECT UserID, Last_Name + ', ' + First_Name + ' ' + Middle_Name + '.' AS name
FROM Table
WHERE name LIKE 'variable%'
Everything looks right with the results, if I take out the WHERE clause it has name displayed properly and joined together with the rest of the data in the results properly.
Thanks in advance for any help that can be provided!
View 3 Replies
View Related
Oct 12, 2007
I have a query that doesn't work when i use 4 name convention instead of a openquery. The msg is below. Anyone know what is going on?
Both queries are the same but one doesn't work.
-- works
SELECT TOP 1 * FROM OPENQUERY(AS400_PROD, 'SELECT * FROM PPTREASUSA.ORDDET')
-- doesnt work
SELECT TOP 1 * FROM AS400_PROD.S1030Y3M.PPTREASUSA.ORDDET
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]CPF5715 - File ORDDET01 in library QTEMP not found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80004005: ].
http://www.sqlserverstudy.com
View 2 Replies
View Related
Jan 17, 2008
hi all,
i made a stored procedure that uses the sp_send_dbmail to send mails. SQL server dislays the message "mail queued" but nothing is recieved
here is the code of the stored procedure i made
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Exams',
@recipients = 'me@domain.com',
@Body_format = 'HTML' ,
@subject = 'Room Preparation' ,
@body='hi there';
so can anyone help with this issue
thanks in advance
View 6 Replies
View Related
Feb 7, 2007
I have a drill through that passes four parameters. Three are passed from the current selections in that reports parameters and the fourth needs to be the customer name they click on in the body of the report so it's passed as Fields!fieldname.Value. When I click on the customer name, the drillthough fires but the report simply doesn't load. If I remove the parameter from the field clicked on and just pass the three parameters, it goes to the drill through correctly and that fourth parameter just sets to the default for that parameter in that report.
I can then simply check that parameter and select the value from the list that is exactly the same as the value I was attempting to pass it in the drill through and report refreshes correctly.
Whatever it is, is something in the manner that the value is passed in the drill through specifically.
Any ideas?
View 1 Replies
View Related
Mar 15, 2007
Hello,
I've rescued a MDF and LDF files off a client's old server, and I wanted to attach it to our own, but I can't seem to get the command to work, basically I have these two files, which I've dropped on our server:
C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF
C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.LDF
So when I do a
SP_ATTACH_SINGLE_FILE_DB 'somedb','C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF'
It says the LDF path my be incorrect, and that there's two other files that are missing:
MYCLIENTNAME_LOG (no extension)
extra_log (no extension)
I thought the whole point of the command is that you only need a single file?
Its very hard to go back to the client's old server and try to find these two files, and it doesn't really matter if we loose a bit of data, so long as the bulk of it is available.
Update: I think I've found the answer...its not possible to do this, it really needs all the log files.
Any workarounds?
View 1 Replies
View Related
Jun 25, 2007
This works:
-- authenticator
CREATE FUNCTION [dbo].[cc2_Helper]( @SecretData VARBINARY(256), @cId INT )
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, convert(varbinary, @cId) ) )
END
go
CREATE VIEW [dbo].[cc2View2]
AS
SELECT CardID as CardID, [dbo].[cc2_Helper](CardNumber, CardID) as CardNumber FROM [dbo].[cc2]
go
GRANT SELECT ON [dbo].[cc2View2] TO [user_low_priv]
This doesn't:
-- auth2/view3
CREATE FUNCTION [dbo].[cc2_Helper2]( @SecretData VARBINARY(256), @vBin VARBINARY )
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, @vBin ) )
END
go
CREATE VIEW [dbo].[cc2View3]
AS
SELECT CardID as CardID, [dbo].[cc2_Helper2](CardNumber, convert(varbinary, CardID)) as CardNumber FROM [dbo].[cc2]
go
GRANT SELECT ON [dbo].[cc2View3] TO [user_low_priv]
WHY? Note that the conversion to VARBINARY was moved from the call to DecryptByKeyAutoCert to the call to cc2_Helper2. That is the only change...
But if I declare @vBin as VARBINARY(256) then it does work! Guess I'm a little confused on declaring vars...anyone can elucidate? Thanks.
View 1 Replies
View Related
Dec 25, 2005
In the Data Source Wizard the "insert,delete,update" advanced options are greyed out.
The table's PK is not an identity, but a uniqueidentifier.
View 2 Replies
View Related
Jan 16, 2006
I have been looking at this for over a day now. I cannot see why this procedure does not work, its so simple.
No matter what happens it always returns 0. If it locates a record, it doesnt update it, yet it still returns 0.
It should not be returning 0 if its not updating so I can't figure out why it does.
Why does this always return 0?
[pre]Create Procedure CreateNewCategory @title nvarchar(100), @description nvarchar(1000), @displayOrder intAS DECLARE @Result as int
IF EXISTS(SELECT categoryTitle FROM categories WHERE categoryTitle = @title) BEGIN SELECT @Result = 1 ENDELSE BEGIN INSERT INTO categories(categoryTitle, categoryDescription, displayOrder) VALUES(@title, @description, @displayOrder) /* If no error was encountered, 0 will be returned. */ SELECT @Result = @@Error ENDGO[/pre]
Thanks!
View 2 Replies
View Related
