SELECT A Field From Another Database (on Same Server) From Stored Proc
Jul 12, 2007Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
ADVERTISEMENT
Parsing A Field Into Another Field Using Stored Proc
Sep 28, 2005Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
View 3 Replies View RelatedHttp Endpoints For SOAP In SQL Server 2005 - Can The Stored Proc Referenced Do More Than Select?
Feb 17, 2008I 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 look forward to any response. Thanks!
How To Optionally Update Field In Stored Proc?
Mar 22, 2008Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate]
(
@ID int,
@Field1 nvarchar(50),
@Field2 nvarchar(50)=null,
@Field3 nvarchar(50)=null,
@Field4 nvarchar(50)
)
AS
UPDATE MyTable
SET Field1 = @Field1,
if (@Field2<>null) Field2 = @Field2,
if (@Field2<>null) Field3 = @Field3,
Field4 = @Field4
WHERE ID = @ID
I saw example before but i can't remember where.
Thank you in advance
STORED PROC To UPDATE Ntext Field -- PLEASE!
Jul 20, 2005Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
View 2 Replies View RelatedIn Stored Proc - How Do I Find The Next Key Value (integer) And Use It To Populate A Field In The New Record.
Nov 27, 2006Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies View RelatedIs It Possible To Select From A Stored Proc?
Dec 31, 2004I just want to do this (shortened example):
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)
Using A Where In Select In A Stored Proc
Mar 11, 2004i 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 (
While TempFulLString <> ''
begin
cnt = InStr(1, TempFulLString, ',')
TempStyleFID = Left(TempFulLString, (cnt))
TempFulLString = Right(TempFulLString, (Len(TempFulLString) - cnt))
TempStyleFID + ','
end
)
-----------------------------------------------
Run Stored Proc With SELECT
Aug 23, 2007I'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?
Any enlightenment is appreciated.
Stored Proc Select Problem
May 22, 2006hi all
i have a simple problem that i cant get through.
i am writing a stored procedure and i want to make this select statement
SELECT TOP 100 PERCENT dbo.tPA00175.chrJobNumber, dbo.tPA20802.dteDocumentDate, dbo.tPA00002.chrPhaseName, dbo.tPA00007.chrEmployeeNumber,
dbo.tPA20802.numActualQuantity, dbo.tPA20802.numChargeOutRate, dbo.tPA20801.numTotalCharges, dbo.tPA00125.numQTYInvoiced
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0)
ORDER BY dbo.tPA00175.chrJobNumber DESC
how would i go about setting my variables, if at all possible, from the values im calling for in the select statement?
is there another way of doing it rather than this
set @Phase=(select dbo.tPA00002.chrPhaseName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @Resource=(select dbo.tPA00007.chrEmployeeName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @hours=(select dbo.tPA20802.numActualQuantity
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @rate=(select dbo.tPA20802.numChargeOutRate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @amount=(select dbo.tPA20801.numTotalCharges
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @date=(select dteDocumentDate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
SET @invDate=(select(getdate()))
thanks alot
tibor
Select As A Variable In A Stored Proc
Sep 25, 2006Hey,
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
SELECT Followed By INSERT All In One Stored Proc
Mar 27, 2008Hi, 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. :)
Cant Add Stored Proc With Select Top @varname...
Oct 15, 2007I'm getting an error trying to add an sp that selects top @varname...I'm surprised since variables are typically allowed in t-sql selects.
Is this by design or am I doing something wrong? Do I have to build the select dynamically to accomplish this?
Multiple Select Statement In A Stored-proc?
Mar 1, 2005I am using SQL sever 2k and C#.
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
C# code example:
//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());
Thanks for your help.
Stored Proc Using Variable As Fieldname In Select Statment
Apr 20, 2001Using 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'
select * from #SavingsData1
Select Statement Returns Null In Stored Proc
Feb 22, 2006If 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
View 2 Replies View RelatedSELECT Returning Multiple Values In A Stored Proc
Jul 20, 2005HiI'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
View 6 Replies View RelatedDataset Using Stored Proc With Multi Select Params
Aug 7, 2007I 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 :
Code Snippet
declare @sql nvarchar(2000)
set @sql = '
EXEC [Monitor] '' + @p_OfferStatus + '''
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?
Select Query From A Stored Proc When The Values Can Be Blank.
Oct 10, 2006Hi All,
I think what am trying to do is quite basic.
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 !.
.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}
.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}
.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}
.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}
.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}
Multiple Select Statements In Single Stored Proc
May 19, 2008
Hi,
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?
Thanks
Stored Proc Crashes At Select Into With Error 18456 When Run From Another Machine
Mar 27, 2007Hi,
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.
Please please help.
Ahrvie
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I 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
Creating Database From Stored Proc With Variable Holding The Database Name
Aug 16, 2007Here is my code
ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Thanks
Ganesh
Want The Database Name From Stored Proc
Feb 18, 1999From within the stored procdure, I would like to find out the name of the database in which the stored procedure is located. Is this possible?
Thanks,
Judith
Stored PROC Is In Which Database
Jun 23, 2008Hi Gurus,
I have partiular stored procedure.Now i want to know as this PROC belongs to which DATABASE.On a server we may be having several databases from those how can we know as this PROC belongs to particular database.Is there ay systable for this..?
Thanks,
ServerTeam
Exec Stored Proc In Different Database
Apr 17, 2001I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?
Stored Proc To Create Database?
Mar 23, 2006I was wondering if it is possible to create a stored procedure to create databases. The only parameter would need to be the name of the database. Do I have to use a bunch of EXEC statements, or is there a better way?
I've created basic stored procs before, but never one for something like this.
Thanks.
Stored Proc Error By Northwind Database
Jan 14, 2008i 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
FWIW: Database Space Used (stored Proc)
Aug 2, 2004For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:
1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)
Place the code into an admin database or (more risky and less "best practice") directly into your master database.
Usage:
USE MyDatabase
GO
EXEC AdminDatabase.dbo.sp_dbspaceused
GO
CREATE PROCEDURE sp_dbspaceused
as
declare @idint-- The object id of @objname.
declare@pagesint-- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
set nocount on
/*
** If @id is null, then we want summary data.
*/
/*Space used calculated in the following way
**@dbsize = Pages used
**@bytesperpage = d.low (where d = master.dbo.spt_values) is
**the # of bytes per page when d.type = 'E' and
**d.number = 1.
**Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
**+ sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end
return (0) -- sp_spaceused
GO
Search Database For Any Terms Stored Proc Help
May 28, 2008hey all,
basically, what I am trying to achieve to 2 types of search functions...
Search for All terms (easy and complete) and search for Any Terms...
the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.
this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.
What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.
Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...
or any other hints/tips/tricks would also be helpful.
Thanks Everyone!
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedStored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View Related