SELECT Followed By INSERT All In One Stored Proc
Mar 27, 2008
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. :)
View 1 Replies
ADVERTISEMENT
May 18, 2004
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.
I created a test in MS Access and it loooks like this:
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country
This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID
ANY SUGGESTIONS ON HOW TO HANDLE THIS?
View 7 Replies
View Related
Jan 10, 2001
given a variable @requestID and @session ID, I need to move requests from
a holding table to the request table using the generated request ID.
In a perfect world...
INSERT INTO Requests(ReqID, field1, field2)
VALUES (SELECT @requestID AS RID, field1, field2
FROM holding WHERE session = '1234')
So that all 5 or so rows from the holding table having a session ID of 1234
get transfered to the request table using the variable value @requestid as
the value satisfying ReqID.
Any ideas?
View 1 Replies
View Related
Dec 31, 2004
I 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)
View 2 Replies
View Related
Mar 11, 2004
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 (
While TempFulLString <> ''
begin
cnt = InStr(1, TempFulLString, ',')
TempStyleFID = Left(TempFulLString, (cnt))
TempFulLString = Right(TempFulLString, (Len(TempFulLString) - cnt))
TempStyleFID + ','
end
)
-----------------------------------------------
View 3 Replies
View Related
Aug 23, 2007
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?
Any enlightenment is appreciated.
View 4 Replies
View Related
Oct 31, 2006
hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
View 2 Replies
View Related
Aug 3, 2004
Hi, I'm fairly new to strored procedures. what I need to do is create a new row with an INSERT specifying value A as a param ( so far no problem ) and value B as a value from table2. Is this possible?
Thanks for your help
Raif
View 3 Replies
View Related
Aug 21, 2007
Insert into Stored Proc
--------------------------------------------------------------------------------
I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions? Can someone tell me if something doesn't look right? I've been over this a million times!
CREATE PROCEDURE TEST_PROC
/*
My Name
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAVG2003 AS MONEY
SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')
CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)
INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID
UPDATE #FORECAST
SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)
UPDATE #FORECAST
SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)
SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005
FROM #FORECAST
ORDER BY TOTAL_2003 DESC
DROP TABLE #FORECAST
RETURN 0
END
GO
View 12 Replies
View Related
May 17, 2008
Hello,
Please consider the following:
CREATE procedure [dbo].[jason_test]
with execute as 'bulk_insert_test_jcb'
as
exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRDataonmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')
This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works
However, if I run this as a windows user logged into the server
alter database stage_scorpio_bulk_jcb set trustworthy off
exec jason_test
--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4
--You do not have permission to use the bulk load statement.
I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...
alter database stage_scorpio_bulk_jcb set trustworthy on
exec jason_test
--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4
--Cannot bulk load because the file "\shodbs29CDRDataonmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).
Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29
--User Logoff:
-- User Name: ANONYMOUS LOGON
-- Domain: NT AUTHORITY
-- Logon ID: (0x0,0x4C99BD2F)
-- Logon Type: 3
--
--
--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.
Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.
Thanks!
Jason
View 6 Replies
View Related
May 22, 2006
hi 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
View 6 Replies
View Related
Sep 25, 2006
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
View 2 Replies
View Related
Oct 15, 2007
I'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?
View 1 Replies
View Related
Mar 5, 2007
Hi,
How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[showtube_addNewSUser]
-- Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@FirstName nvarchar(32),
@LastName nvarchar(32),
@DescShort nvarchar(256),
@DescLong ntext,
@ClanID uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID)
values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID);
END
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.
View 3 Replies
View Related
Mar 1, 2005
I 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.
View 2 Replies
View Related
Apr 20, 2001
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'
select * from #SavingsData1
View 1 Replies
View Related
Jul 12, 2007
Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
View 10 Replies
View Related
Feb 22, 2006
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
View 2 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
Aug 7, 2007
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 :
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?
View 4 Replies
View Related
Oct 10, 2006
Hi 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;}
View 7 Replies
View Related
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
View 4 Replies
View Related
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.
Please please help.
Ahrvie
View 1 Replies
View Related
Feb 17, 2008
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 look forward to any response. Thanks!
View 5 Replies
View Related
Feb 13, 2008
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
View 3 Replies
View Related
Aug 24, 2006
I 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.
View 3 Replies
View Related
Jun 15, 2006
Hi 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
Oct 14, 2006
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w
ten sposob co napisalem kilka postow wyzej (select ID as UID from
aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest
wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
View 6 Replies
View Related
May 5, 2008
hi need help do this
i have table that generate Serial
i get the code from this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2734123&SiteID=1
and i must to get the same sn + date
so whan i do this
Code Snippet
select * from tbl_1a
WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
select * from tbl2_2B
WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
i get the current state DATE + SN
i need to insert into other 2 table this Serial+date like this
--need help fix this code
Code Snippet
DECLARE @sn
DECLARE @date
set @date =Getdate()
-- insert 1 new records tb sn
INSERT INTO ConfirmationNumber([ConfirmationDate])
SELECT GETDATE()
---select last value sn
set @sn = SELECT top 1 SequenceNumber FROM vw_ConfirmationNumber ---- get the last sn
order by SequenceNumber desc
--insert into tb1
insert into tbl_1a (fld1,fld2,sn,date)
select from tbl_2a fld1,fld2,@sn,@date
--insert into tb2
insert into tbl2_2B (f1,f2,date)
select from tbl2_1B f1,f2,@sn,@date
TNX
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Feb 2, 2006
I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1
View 6 Replies
View Related
Dec 11, 2007
In a stored procedure, I've created a temp table, and am trying to populate it using:
insert #myTable
exec p_someProc
p_someProc returns 2 record sets, the first is the one I want to stuff into #myTable, the second is a single field.
I think that is the reason that I'm getting:
"
Insert Error: Column name or number of supplied values does not match table definition.
"
#myTable has the same structure as the first record set. Is that second recordset causing the error, if so, how to I either incorporate it into the temp table or just ignore it?
View 5 Replies
View Related
Sep 25, 2015
I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
[Code] ....
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.
View 7 Replies
View Related
