Stored Proc Crashes At Select Into With Error 18456 When Run From Another Machine
Mar 27, 2007
Hi,
Finally found what is causing my .net c# service application to return with Error 18456 (NT Authority/anonymous logon" severity 14 State 11.
It is connecting to a sql server db in another machine and running a stored procedure in that db. I found out that if I remove that two statements that create temp tables (with select INTO), the stored procedure executes without a problem.
Note that this stored procedure updates the tables does not have problems updated tables that are in the DB. I gets upset when creating temp tables though.
I cannot do away with those temp tables as they are needed in the calculation. How can I fix this problem?
Why would creating temp tables remotely cause this error? What am I missing in my set up. My service application connects with the integrated security = true. My service process installer has the account set to "User". What am I missing.
Hullo all I have two machines, One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ? running the procedure as a Domain user results in a failed job/stored proc. also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ? please e-mail me at wayde@sunnygrp.com if you have any thoughts...
Select hours from GetBaseStoredProc '10/01/2004' Where ReasonId = 1
where GetBaseStoredProc is a stored procedure that takes a date parameter and contains a select sql statement like: select reasonId, hours from my table where mydate = @myDate
(in reality, it's a much larger and more complicated statement, just using this as an example)
I guess I'm treating my storedProc like a view. If worse comes to worse, I suppose I could create a view (would rather not), but I'm wondering if what I want to do is possible, and I'm just not using the right syntax. Many thanks - (a former Oracle dev)
i have a vb app that is retriving data from an sql db using ado. i have a qry save in my db. i want to select from this qry where x in (1,2,3,4). i don't know how many values i will be putting into my where in statment. it could be one value or 100 values. below is my code. what i would like to do is pass one paramater to my stored proc and then break it up and use it in my select. my desired result is as follows
----------------------------------------------- declare cnt int declare TempFulLString nvarchar(5000) declare TempStyleFID int
TempFulLString = @str_TempString
select * from QryPicking_Slip_Fill_Listview1 where stylefid in (
I'm trying to call a stored proc with parameters without using EXEC statement and with only using a SELECT statement. I have the stored proc and need to call it from a 3rd party application which provides an interface to SQL server and does not support EXEC statements, but only SELECT statements. Is there a way this can be done?
Hey, I create a Select Statement in stored proc and I have printed the variable and it has the correct Select statement. My problem is now that I have the string I want how do I run it. Thanks
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?
What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?
I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.
stored procedure example: NorthWind database:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
Create PROCEDURE dbo.getShippersAndEmployeesXML AS
select * from Shippers for xml auto, elements select * from Employees for xml auto, elements
RETURN @@ERROR
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test @ContractCode varchar(10), @dtFrom datetime, @dtTo datetime, @Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0 set @option = 'c.contractcode' else set @option = 'cb.employercode'
select c.claimsno, c.attenddoctor, c.patientcode, p.sex, cb.employercode into #SavingsData1 from claimsa c inner join Patient p on c.patientcode = p.patientcode inner join claimsb cb on c.claimsno = cb.claimno where @option = @ContractCode and c.dateentered between @dtFrom and @dtTo and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.
In the data tab I'm currently using "text" for command type and :
exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?
I have 3 paramaters@value1, @value2,@value3 being passed into a stored proc and each of these parameters can be blank. If one of them is blank and the rest of them have some valid values, then I should just exclude the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would do this select * from tblName where column1 like @value1 and column2 like @value2 and column3 like @value3
else if I have one of the parameter being passed as empty, I should ignore that parameter like if@value1 is empty then my sql should be
select * from tblName where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue. I want it through a stored procedure only.
Also, all the three columns can have null values in the table. Please let me know what is the best possible way to do this. Thanks in advance !.
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too. Basically those are SELECT statements. I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report. If possible, can anyone show me the format?
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'. The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong. CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL) AS EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName') GO
Can anyone see an error here? I am getting a general exception when call this and it is driving me crazy. What am I missing? The error is.....{"Incorrect syntax near 'UpdateStaffDept'." ... Any help is greatly appreciated! Below is the code calling the proc Thank you! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[UpdateStaffDept] @Co_Id INT, @CardNo VARCHAR(50), @DeptNo VARCHAR(50)
AS SET NOCOUNT ON
DECLARE @Error INT
SELECT @Error = @@Error IF @Error = 0 BEGIN
BEGIN TRANSACTION UPDATE Staff SET Dept_No = @DeptNo WHERE Co_Id = @Co_Id AND PC_Id IN (SELECT PC_ID FROM PC_Number WHERE PC_Number = @CardNo)
SELECT @Error = @@Error IF @Error <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Error END ELSE COMMIT TRANSACTION
END
Dim seldeptno As String seldeptno = ddlEditPosDept.SelectedValue Dim cmdChnDept As SqlCommand cmdChnDept = New SqlCommand("UpdateStaffDept", conEditPos) cmdChnDept.Parameters.Add(New SqlParameter("@Co_Id", SqlDbType.Int 10)) cmdChnDept.Parameters("@Co_Id").Value = compid cmdChnDept.Parameters.Add(New SqlParameter("@CardNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@CardNo").Value = lblEditPosCardNo.Text cmdChnDept.Parameters.Add(New SqlParameter("@DeptNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@DeptNo").Value = seldeptno cmdChnDept.ExecuteNonQuery()
I come from an Access/VB background where in an error trapping routine or testing for an error your statement "exit sub" ended the process. WHen using a stored procedure and you come across an error what is the best method to exit/end the stored proc?
I have a stored proc that loops through records using a cursor. If during the loop an error occurs i need to trap the error and write it to a log file. Error handling needs to be placed in the update and insert sections and the error that occurs needs to get recorded along current row from the cursor.
Here is the code: declare @tier_id int, @tier_desctext varchar(50), @tier_shortdesc varchar(50), @long_name varchar(50), @short_name varchar(50), @rec_count int
--Cursor for UES data DECLARE cr_ues_tier INSENSITIVE CURSOR FOR SELECT DISTINCT tier_id, tier_desctext, tier_shortdesc FROM "dbsourcedayoldprod".ues.dbo.Tiers
OPEN cr_ues_tier
--Select cursor values into local variables FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc WHILE (@@FETCH_STATUS <> -1) BEGIN
--Set Data Mart variables SELECT @long_name = long_name, @short_name = short_name FROM uesrpt..coverage_level WHERE ues_tier_id = @tier_id
--Set the record counter SET @rec_count = (select ues_tier_id from uesrpt..coverage_level where ues_tier_id = @tier_id)
--Are there exsisting records if so go to the update section IF @rec_count <> 0
--Update Data Mart values if they have changed BEGIN IF @long_name <> @tier_desctext OR (@long_name IS NULL AND @tier_desctext IS NOT NULL) OR (@long_name IS NOT NULL AND @tier_desctext IS NULL)
OR @short_name <> @tier_shortdesc OR (@short_name IS NULL AND @tier_shortdesc IS NOT NULL) OR (@short_name IS NOT NULL AND @tier_shortdesc IS NULL)
UPDATE uesrpt..coverage_level SET long_name = @tier_desctext, short_name = @tier_shortdesc WHERE ues_tier_id = @tier_id END
--Rows don't exsist in the Data Mart - Insert new rows ELSE INSERT INTO uesrpt..coverage_level (ues_tier_id, long_name, short_name) SELECT @tier_id, @tier_desctext, @tier_shortdesc
--Get next row from UES cursor FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc END
This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?
truncate table tbl_rtv_cosmetic_dif go insert tbl_RTV_COSMETIC_DIF select d.fisc_yr ,d.fisc_pd ,d.fisc_wk ,d.fisc_dy ,substring(a.long_sku,1,4) as DeptNo ,substring(a.long_sku,7,3) as VendorNo ,substring(a.long_sku,10,5) as MarkStyleNo ,a.Loc as LocNo ,convert(int,a.RsnCd) as ReasonCode ,min(a.EAN_Nbr) as UPCEANNo ,coalesce(i.own_rtl,0) as OwnedCur ,sum(convert(int,a.units)) as UPCUnits
from november a inner join generalinfo..tbl_fisc_date d on a.date = d.cal_date
left outer join itemdata..tbl_item_import_history i on substring(a.long_sku,1,4) = i.dept and substring(a.long_sku,7,3) = i.vendor and substring(a.long_sku,10,5) = i.mrk_style and d.fisc_yr = i.amc_yr and d.fisc_pd = i.amc_pd and d.fisc_wk = i.amc_wk
group by fisc_yr,fisc_pd,fisc_wk,fisc_dy ,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5) ,loc,rsncd,own_rtl
I have a table I insert a record into to give access to a user. It usesprimary keys so duplicates are not allowed, so trying to add a recordfor a user more than once is not allowed.In my .NET programs, sometimes it's easier to let the user select agroup of people to give access to eben if some of them may already haveit.Of course this throws an exception an an error message. Now I couldcatch and ignore the message in .NET for this operation but then I'mstuck if something is genuinely wrong.So is there a way to do this? :In my stored procedure determine if an error occured because of aduplicate key and somehow not cause an exception to be returned toADO.NET in that case?
Hi all,I have a sproc that uses OpenRowset to an Oracle db. If OpenRowseterrors, it terminates the procedure. I need it to continueprocessing. Is there any workaround for this?ThanksPachydermitis
When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
i try create this example:http://www.codeproject.com/KB/webforms/ReportViewer.aspxI have Northwind database added in the SQL server management and i select Northwind databse in drop box and I push Execute!ALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )ASSELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice, Products.UnitsInStockFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE CategoryName=@CategoryNameRETURNbut error is:Msg 208, Level 16, State 6, Procedure ShowProductByCategory, Line 11Invalid object name 'ShowProductByCategory'.on web not so clear what is issue pls. help
I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?
I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.
