Stored Procedure Assembly Error
Oct 18, 2007
Hello there,
I am having trouble with the following and appreciate any help.Thanks in advanced.
I have created an assembly to delete records in exchange (which works fine when testing it in debug mode). I then create the assembly in SQL server 2005(tried both unsafe and external access btw), and then i create a stored procedure calling the assembly.
Now my error occurs when i try to execute this stored procedure in SQL Server. I get the following...
Msg 6522, Level 16, State 1, Procedure sp_DeleteExchApp, Line 0A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_DeleteExchApp': System.Security.SecurityException: System.Security.Permissions.SecurityPermissionSystem.Security.SecurityException: at App_Delete.eSay.AppointmentDelete.DeleteAppointment(Guid GUID).(1 row(s) affected)
Now when i have looked around the web, i have come accross people saying that it is to do with the assembly trying to access something it does not have permission for. But i cant figure out what that is or why it is throwing an error.
The code for the assembly is as follows:
Public Shared Sub DeleteAppointment(ByVal GUID As System.Guid) Dim CalendarURL As String Dim ItemURL As String Dim Rs As New ADODB.Recordset Dim Rec As New ADODB.Record Dim Conn As New ADODB.Connection Dim iAppt As New Appointment Dim Target As String Dim strCRMGUID As String Dim strGUID As String Dim sSQL As String Dim Check As String strCRMGUID = GUID.ToString 'Using the Exchange OLE DB provider CalendarURL = "file://./backofficestorage/esay-solutions.co.uk/public folders/Office Diary" 'Open a recordset for the items in the calendar folder Rec.Open(CalendarURL) Rs.ActiveConnection = Rec.ActiveConnection sSQL = "SELECT ""DAV:href"", " & _ " ""urn:schemas:calendar:location"", " & _ " ""urn:schemas:calendar:dtstart"", " & _ " ""urn:schemas:calendar:dtend"", " & _ " ""urn:schemas:httpmail:textdescription"" " & _ "from scope('shallow traversal of """ & CalendarURL & """')" Rs.Open(sSQL, Rec.ActiveConnection, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockOptimistic, 1) 'Enumerate the recordset, checking each item's location If Rs.RecordCount > 0 Then Rs.MoveFirst() Do Until Rs.EOF 'get the location of each item Check = Rs.Fields(CdoHTTPMail.cdoTextDescription).Value strGUID = StripGUID(Check) 'test for desired location If (strGUID = strCRMGUID) Then Rs.Delete() End If Rs.MoveNext() Loop End If End Sub
Private Shared Function StripGUID(ByVal i_strAppDescription As String) As String
'Function to strip the GUID from the message field Dim r As Integer Dim SearchChar As String Dim testPos As Integer Dim GUID As String SearchChar = "**¦" testPos = InStr(i_strAppDescription, SearchChar) testPos = testPos + 3 If testPos > 0 Then GUID = Mid(i_strAppDescription, testPos) End If StripGUID = GUID End Function End ClassEnd Namespace
Thanks again for any help.
View 5 Replies
ADVERTISEMENT
Feb 22, 2008
I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
· The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
· The signatures of methods in the assembly that are called from other assemblies.
· The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
· The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
· The FillRow method name attribute for CLR table-valued functions.
· The Accumulate and Terminate method signature for user-defined aggregates.
· System assemblies.
· Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
· Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
· Adding new public methods.
· Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
View 9 Replies
View Related
Feb 24, 2006
I was playing around with the CLR in writing assemblies for the sql server 2005 stored procedure. I guess the example i found was for the beta version
This line is from the beta but no longer works. Any ideas what will fix this. There is no longer GetCommand property.
SqlCommand cmd = SqlContext.GetCommand();
Example
public partial class StoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedure1()
{
// Put your code here
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText="select firstname + ' ' + lastname + as [name] from person.contact";
SqlDataReader rdr = cmd.ExecuteReader();
SqlPipe sp = SqlContext.GetPipe();
sp.Send(rdr);
}};
View 1 Replies
View Related
Jan 26, 2006
Hi,
Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.
I
have created a stored procedure that needs to load different assemblies
depending on a query and then invokes a method (called execute() passing a string query as a parameter).
From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:
Assembly myAssembly= Assembly.Load()
It
doesnt matter if I attempt to load the assembly from it's byte array or
from a file location, it always gives the following error:
Msg 6522, Level 16, State 1, Procedure runQuery, Line 0A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery': System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark,
Boolean fIntrospection) at System.Reflection.Assembly.Load(Byte[] rawAssembly) at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)
It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.
Anybody have any ideas?
View 3 Replies
View Related
May 4, 2006
I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??
View 2 Replies
View Related
Oct 8, 2015
I have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,
I need my stored procedure to return this data as a table.
My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..
View 3 Replies
View Related
May 1, 2008
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but can€™t get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed.
[token 0x02000008] Type load failed.
View 8 Replies
View Related
May 31, 2007
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.
I import €œSystem.IO€? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is €œon€?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators].
2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to €œMy Windows Authentication ID€?.
3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON
4) In Visual Studio .NET 2005 Set the permission levels to €˜external€™
5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated.
Regards Steve
View 8 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Feb 29, 2008
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
View 20 Replies
View Related
Oct 4, 2007
I've created an assembly in Adventureworks, and created a sproc that references a function inside the assembly. All works well with the asssembly and proc.
I know that I can't drop the assembly without first dropping the sproc, but I can't find where the dependency information is stored. sp_depends, sys.sql_dependencies, etc, all come up empty for the assembly, and when I run sp_depends on the SQL sproc object, I get:
"Object does not reference any object, and no objects reference it."
Clearly that's not the case, since if I attempt to drop the assembly, I get the expected error 6590: "DROP ASSEMBLY failed because 'ProcTest' is referenced by object 'uspGetStuffFromAssembly' "
Any idea where this is being stored?
--
Chris Randall
cfrandall@google'smail.com
blogs.ameriteach.com/chris-randall
View 5 Replies
View Related
Jan 12, 2006
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
View 7 Replies
View Related
Aug 2, 2006
Hi
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
namespace inlineCLRsql{
public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());
}
}
}
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Thanks for your help
M
View 1 Replies
View Related
Oct 17, 2006
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
View 5 Replies
View Related
Jul 29, 2007
hi,
i want to take the first n values from my Categorii table, here is my stored procedure:
SELECT ROW_NUMBER() OVER (ORDER BY CategoryID) AS RowNumber, CategoryID, Name, Description FROM Categorii
WHERE DepartamentID = @DepartamentID AND RowNumber <= 5
i get the error: invalid column name RowNumber
why? what should i do? if i execute the procedure without the AND RowNumber <= 5 i get the RowNumber values 1 to 9 (that means it works)...but what should i do to retrive only the first n?
thank you
View 2 Replies
View Related
Aug 17, 2007
I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod]
@ClientCode VARCHAR(7) = ''
,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0
,@AccountNumber VARCHAR(30) = ''
,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0
,@Status VARCHAR(5) = ''
,@UserID INT
,@Month DateTime = NULL
,@FromDate DateTime = NULL
,@ToDate DateTime = NULL,@OriginalMin decimal = 0
,@OriginalMax decimal = 0,@CurrentMin decimal = 0
,@CurrentMax decimal =0
,@lstAmountSelect VARCHAR(3),@IsActive bit = 1
ASDECLARE
@SQLTier1Select VARCHAR(2000)
,@SQLTier2Select VARCHAR(2000)
,@Criteria VARCHAR(2000)
,@SQL VARCHAR(8000)
,@CRI1 VARCHAR(100)
,@CRI2 VARCHAR(100)
,@CRI3 VARCHAR(100)
,@CRI4 VARCHAR(100)
,@CRI5 VARCHAR(100)
,@CRI6 VARCHAR(200)
,@CRI7 VARCHAR(500)
,@CRI8 VARCHAR(500)
,@CRI9 VARCHAR(500)
SELECT @CRI1 = ''
,@CRI2 = ''
,@CRI3 = ''
,@CRI4 = ''
,@CRI5 = ''
,@CRI6 = ''
,@CRI7 = ''
,@CRI8=''
,@CRI9=''
,@Criteria = ''
SELECT @DebtorName = REPLACE(@DebtorName,'''','''''');
Print @DebtorName
if(SELECT UserTypeID FROM dbo.tbl_Security_Users Where UserID = @UserID) = 3 AND @ClientCode = ''
return (-1)IF LEN(@DebtorName) > 0
SET @CRI1 = ' AND Name like ' + '''%' + @DebtorName + '%'''IF @DebtorNumber > 0
SET @CRI2 = ' AND Number = ' + CAST(@DebtorNumber AS VARCHAR(7))IF LEN(@AccountNumber) > 1
SET @CRI3 = ' AND AccountNumber like ' + '''%' + @AccountNumber + '%'''IF LEN(@ReferenceNumber) > 0
SET @CRI4 = ' AND Account like ' + '''%' + @ReferenceNumber + '%'''IF LEN(@ClientCode) > 1
SET @CRI5 = ' AND Customer = ' + '''' + @ClientCode + ''''
SET @Status = RTRIM(@Status)
IF ((@Status Not IN ('ALL','ALA','ALI')) AND (LEN(@Status)>1))
BEGIN
IF(@Status = 'PAID')
SET @CRI6 = ''
IF(@Status = 'CANC')
SET @CRI6 = ' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'
END
--PRINt @CRI6IF LEN(CONVERT(CHAR(8), @Month, 112)) > 0
BEGIN
IF(LEN(CONVERT(CHAR(8), @FromDate, 112)) > 0 AND LEN(CONVERT(CHAR(8), @ToDate, 112)) > 0 )
BEGIN
SET @CRI7 = ' AND Received BETWEEN ' + '''' + CONVERT(CHAR(8), @FromDate, 112)+ '''' + ' AND ' + '''' + CONVERT(CHAR(8), @ToDate, 112) +''''END
ELSEBEGIN SET @CRI7 = ' AND DATEPART(mm, Received) = DATEPART(mm, ' + '''' + CONVERT(CHAR(8), @Month, 112) + '''' + ') AND DATEPART(yy, Received) = DATEPART(yy, ' + '''' + CONVERT(CHAR(8), @Month, 112) + ''''
END END
IF @lstAmountSelect='ALL'
SET @CRI8=''
else IF @lstAmountSelect = 'DR'
BEGIN
SET @CRI8=' AND OriginalBalance >= '+ convert(Varchar,@OriginalMin) + 'AND OriginalBalance<=' + convert(Varchar,@OriginalMax)+' AND CurrentBalance >= '+ convert(Varchar,@CurrentMin) + 'AND CurrentBalance<=' +convert(Varchar,@CurrentMax)
END
ELSE IF @lstAmountSelect = 'OLC'
BEGIN
SET @CRI8=' AND OriginalBalance < CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OGC'
BEGIN
SET @CRI8=' AND OriginalBalance > CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OEC'
BEGIN
SET @CRI8=' AND OriginalBalance = CurrentBalance '
END
SELECT @Criteria = @CRI1 + @CRI2 + @CRI3 + @CRI4 + @CRI5 + @CRI6 + @CRI7 + @CRI8
--PRINT @Criteria
--PRINT @CRI7
if @Status = 'ALL' OR @Status = 'ALA' OR @Status = 'ALI' --All Period
BEGIN
if(@Status = 'ALL') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'
END
if(@Status = 'ALA') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'
END
if(@Status = 'ALI') --All Inactive
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'
ENDEND
ELSE IF @Status = 'PAID'
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'END
ELSE
BEGINSELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria END
SELECT @SQL = CASE @Tier
WHEN 0 THEN @SQLTier1Select + ' UNION ' + @SQLTier2Select + 'ORDER BY NAME ASC' WHEN 1 THEN @SQLTier1Select + 'ORDER BY NAME ASC'
WHEN 2 THEN @SQLTier2Select + 'ORDER BY NAME ASC 'END
PRINT @SQL
--SELECT @SQL
EXEC (@SQL)
View 5 Replies
View Related
Sep 6, 2007
When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near '@SQL'.
Here is my procedure. Could someone tell me what mistake I'm doing.Alter procedure [dbo].[USP_SearchUsersCustomers_New]
@UserID INT
,@RepName VARCHAR(50)
,@dlStatus VARCHAR(5) = ''
as
Declare
@Criteria VARCHAR(500)
,@SQL VARCHAR(8000)
SELECT @Criteria = ''SET NOCOUNT ON
if (@dlStatus <>'ALL' AND (LEN(@dlStatus)>1))
BEGIN
if(@dlStatus='ALA')
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=1'
else
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=0'
END
--If the user is an Admin, select from all users.
if(dbo.UDF_GetUsersRole(@UserID) = 1)
BEGIN@SQL = 'SELECT U.UserID
--,U.RoleID
,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR
INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleIDWhere UR.UserID = U.UserID), 'Unassigned') as 'RoleName'
,U.UserName
,U.Name
,U.Email
,U.IsActive
,U.Phone
FROM dbo.tbl_Security_Users U
--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID
WHERE U.NAME LIKE @RepName AND U.UserTypeID < 3'+ @Criteria
END
View 6 Replies
View Related
Jan 29, 2008
Hi,I want to use a variable to put a value in a table but it doesn't seems to works. How can i do that? I have bolded and underlined the text that i think is not correct.What syntax can i use to make it work?Thanks----------------------------------------------------------------------dbo._UpdateImage(@ID int,@ImageID int)
ASBegin
Declare @PhotosThumb nvarchar(50)Declare @Photos nvarchar(50)
SET @PhotosThumb = 'PhotosThumb' + convert(nvarchar, @ImageID)SET @Photos = 'Photos' + convert(nvarchar, @ImageID)
SET NOCOUNT ON
IF @ImageID = 1
UPDATE PhotosSET @PhotosThumb = 'Logo_thumb.gif',@Photos = 'Logo320x240.gif'WHERE ID = @ID
ELSE
UPDATE PhotosSET @PhotosThumb = NULL,@Photos = NULLWHERE ID = @ID
SET NOCOUNT OFFEND
View 6 Replies
View Related
Feb 12, 2008
Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors:
Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch
I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure.
CREATE PROCEDURE dbo.SQLDataSource1
Drop Table PersonGo
Create Table Person
(PersonID Int Identity,
PersonEmail Varchar(255),PersonName Varchar(255),
PersonSex Char(1),PersonDOB DateTime,
PersonImage Image,PersonImageType Varchar(255)
)
Drop Proc sp_person_isp
Go
Create Proc sp_person_isp
@PersonEmail Varchar(255),@PersonName Varchar(255),
@PersonSex Char(1),@PersonDOB DateTime,
@PersonImage Image,
@PersonImageType Varchar(255)
As
BeginInsert into Person
(PersonEmail, PersonName, PersonSex,
PersonDOB, PersonImage, PersonImageType)
Values
(@PersonEmail, @PersonName, @PersonSex,
@PersonDOB, @PersonImage, @PersonImageType)
End
Go
View 4 Replies
View Related
Nov 4, 2003
I have the following code in my code behind page:
Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("spCCF_CrossTab", CN)
CM.CommandType = CommandType.StoredProcedure
CM.Parameters.Add(New SqlParameter("@LocationID", "CCFIF"))
CM.Parameters.Add(New SqlParameter("@BeginDate", dtbStart.Text))
CM.Parameters.Add(New SqlParameter("@EndDate", dtbEnd.Text))
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()
A SQL exception is thrown: Incorrect syntax near the keyword 'END'
But I turned on tracing in Enterprise Manager, the following request is sent to SQL:
exec spCCF_CrossTab @LocationID = N'CCFIF', @BeginDate = N'11/3/2003', @EndDate = N'11/4/2003'
In query analyzer the above line executes without error and returns the expected information.
My stored procedure is:
CREATE PROCEDURE spCCF_CrossTab
@LocationID varchar(10),
@BeginDate varchar(10),
@EndDate varchar(10)
AS
declare @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100), @where varchar(1000)
select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @sumfunc= 'COUNT(ActionID)'
select @pivot='UserName'
select @table= 'UserInfo'
select @where='(dbo.UserInfo.LocationID = ''' + @LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null and ' + @where)
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END)
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
GO
I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!
View 19 Replies
View Related
May 27, 2004
I am trying to get a returned value from the stored procedure below
CREATE PROC insert_and_return_id
(
@parameter1 varchar,
@parameter2 varchar
)
AS
DECLARE @newID int
SELECT @newID = 0
INSERT INTO tbltest (field1, field2)
VALUES (@parameter1, @parameter2)
IF(@@ROWCOUNT > 0)
BEGIN
SELECT @newID = @@IDENTITY
END
RETURN @newID
GO
___________________________
My asp Code looks like this
___________________________
Function InserTest(value1, value2)
Dim objConn, objRs, objCmd
' Create a connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DSN=" & CONNECTION_STRING
' Create the query command
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "insert_and_return_id"
objCmd.CommandType = adCmdStoredProc
' Create the parameter for output and returned valueand populate it
objCmd.Parameters.Append objCmd.CreateParameter("parameter1", adVarChar, adParamInput, 255, value1)
objCmd.Parameters.Append objCmd.CreateParameter("parameter2", adVarChar, adParamInput, 255, value2)
objCmd.Parameters.Append objCmd.CreateParameter("newID", adInteger, adParamReturnValue, 4)
objCmd.Execute objCmd0
response.write objCmd.Parameters("newID")
'objCmd.Close
End Function
And I get the following ASP Error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function insert_and_return_id has too many arguments specified.
/netwasp/tester.asp, line 62
I only just started to use sp's hence it might be something really simple, Can anyone help, cheers?
View 6 Replies
View Related
May 19, 2005
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();
}
View 4 Replies
View Related
Sep 12, 2005
I have a stored procedure to which I pass the following parameters @Date smalldatetime, @Amount decimal(15,3) @Exg_Rate decimal(5,3)Inside this stored procedure I call another one passing to it those parameters like thatEXECUTE dbo.[Opening_Balance_AfterInsert] @Date, @Amount*@Exg_RateI receive an error at the above line saying: Incorrect syntax near '*'if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('How can I pass that expression?
View 1 Replies
View Related
Nov 26, 2005
String[1]: the Size property has an invalid size of 0.I am using a Stored Procedure, shown hereCREATE PROCEDURE GetImagePath( @ID INT, @ImagePath VARCHAR(50) OUTPUT) AS SET NOCOUNT ONSELECT @ImagePath = path FROM dbo.docs WHERE table1= @IDRETURNGOHere is the asp.net code that I am usingSqlConnection conn = new SqlConnection(strConnection);conn.Open();SqlParameter param = new SqlParameter("@ImagePath", SqlDbType.VarChar);param.Direction = ParameterDirection.Output;SqlCommand cmd = new SqlCommand("GetImagePath", conn);cmd.Parameters.AddWithValue("@ID", 100);cmd.Parameters.Add(param);cmd.CommandType = CommandType.StoredProcedure;cmd.ExecuteNonQuery();cmd.Dispose();conn.Dispose();TextBox1.Text = param.Value.ToString();When I run it, I get the following error String[1]: the Size property has an invalid size of 0. The stored procedure is correct because I tested it wtih Query Analyzer. I cant seem to figure out what is causing this error.Any help would be appreciated.
View 1 Replies
View Related
Mar 24, 2006
I am using SQL Server 2005 and wrote a stored procedure as shown below.CREATE PROCEDURE [dbo].[GetUsers]( @StartRowIndex INT, @MaximumRows INT, @SortExpression VARCHAR(50))ASBEGIN DECLARE @SQL VARCHAR(1000); DECLARE @Start VARCHAR(10); SET @Start = CONVERT(VARCHAR(10), @StartRowIndex + 1); DECLARE @End VARCHAR(10); SET @End = CONVERT(VARCHAR(10), @StartRowIndex + @MaximumRows); SET @SQL = ' WITH Data AS( SELECT UserID, Username, FirstName, LastName, ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users) SELECT UserID, Username, FirstName, LastName FROM Data WHERE RowNumber BETWEEN ' + @Start + ' AND ' + @End EXEC(@SQL);ENDIn VS 2005, I am using a DataSet. Then I created an ObjectDataSource which binds the GridView control. However, I am getting the following error: Incorrect syntax near ')'After playing around with it, the error is from the line:ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users)If I change the line to ROW_NUMBER() OVER(ORDER BY UserID') AS RowNumber FROM Users) it works fine, except for sorting.I dont understand what I might be doing wrong.
View 1 Replies
View Related
Mar 4, 2003
I am writing a stored procedure where I am adding a column to a table, doing some manipulation using the new column and then deleting the column. The problem is when I try to save the stored procedure, it gives me an error because it cannot find the new field on the table. How can I tell SQL to not compile a section or whole stored procedure?
Thanks,
Ken Nicholson
Sara Lee Corporation
View 1 Replies
View Related
Jan 29, 1999
Periodically, while running a stored procedure from a VB front end, I get the error 08501, General Connection Failure.
This application is not being used by anyone else, but no one else running other apps on the same server has a problem.
Additionally a symptom dump is produced followed by a message that 'The current contents of process'input buffer are' sp_cursoropen'.
Any ideas?
Thanks in advance.
CF
View 2 Replies
View Related
Oct 5, 2006
I'm trying to build a quick stored procedure here but I get error with "BackupId"... Why?
Code:
CREATE PROCEDURE dbo.sp_tblBackupListINSERT
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT OFF
INSERT INTO tblFileInvalid
(
BackupID, AccountNo, CompanyName, StoragePath, PhoneNumber, Active, RelayActive, TimeStamp
)
VALUES
(
'86','0607-2114-0910','zzUnknownCompanyName','E:BACKUPProcessed','0000000000','1','1','10/04/2006 6:30:00 PM'
)
/* RETURN */
View 2 Replies
View Related
Aug 17, 2004
does anyone see anything wrong with this stored procedure?? I keep getting a syntax error.
CREATE PROCEDURE [InsertGCTerms]
AS
INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title])
SELECT a.TM#, a.LASTNAME, a.FIRSTNAME, a.SSN#, a.JOBTITLE, a.HIREDATE, a.DEPT#
FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GO
View 3 Replies
View Related
May 21, 2008
Hi All,
I have a problem with the procedure shown below. When I try saving it I get the following error, can anyone help
Msg 102, Level 15, State 1, Procedure KillPurveyanceProcess, Line 19
Incorrect syntax near '@tSPId'.
ALTER PROCEDURE [dbo].[KillPurveyanceProcess]
AS
--SET NOCOUNT ON;
DECLARE @tSPId smallint;
DECLARE @tblocked smallint;
DECLARE my_cursor CURSOR FOR SELECT spid,blocked from sys.sysprocesses where program_name ='PurveyanceImport'
and status = 'suspended' and cmd = 'UPDATE'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
WHILE @@FETCH_STATUS = 0
BEGIN
Kill @tSPId
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
END
CLOSE my_cursor
DEALLOCATE my_cursor
Thanks in Advance
Neil
View 2 Replies
View Related
