Please Help A Noob Out In Stored Procedure!
Jul 14, 2005
Hi All,
I'm a newbie in stored procedure programming. I wanted to learn how to perform keyword(s) search with stored procedures. Take the Pubs database for example. How can i create a stored procedure which takes in a string of keywords and return the title_id, title and notes column ? like if i pass in "computer easy" as keywords then the stored procedure should return all the rows with ANY of these keywords in those 3 columns. Can anyone give me some ideas on how to do this? like do i have to use dynamic sql?, any tutorials or sample codes? Thanks in advance!regards
View 16 Replies
ADVERTISEMENT
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Oct 10, 2006
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies
View Related
Nov 15, 2006
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin
View 3 Replies
View Related
May 18, 2007
This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/
CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
Please help me regarding the same.
Thanks in advance.
Sandeep Kumar
View 2 Replies
View Related
Apr 29, 2008
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks
View 10 Replies
View Related
Apr 29, 2004
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
View 1 Replies
View Related
Mar 2, 2007
Hello people,
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
Your help is much appreciated.
View 6 Replies
View Related
May 16, 2008
Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
View 6 Replies
View Related
Dec 21, 2005
In VB, I have this code...
lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _
"a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _
"a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _
"a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _
"LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _
"(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _
"GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')"
Set RS = New ADODB.Recordset
RS.Open lSQL, DB
Do While Not RS.EOF
Select Case RS!Pane
Case "0" Or "1"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "2"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS2 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "3"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS3 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
End Select
ProgressBar1.Value = ProgressBar1.Value + 1
If ProgressBar1.Value = 800 Then
ProgressBar1.Value = 0
End If
RS.MoveNext
Loop
...and I'm planning to use the same flow of logic in the SQL Server Agent using T-SQL. Is there any way I can do it?
View 6 Replies
View Related
Feb 9, 2004
Hi experts,
I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.
I created a new stored procedure within the built-in Northwind database. I pasted the following inside:
-----------------------------------------------------------------
declare
vname Employees.firstname%TYPE;
begin
SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';
dbms_output.put_line ('Name is ' || vname);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line ('no data found');
when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');
END;
-----------------------------------------------------------------
I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option
Any idea?
Thanks..
View 1 Replies
View Related
Aug 25, 2005
Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.
Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
Display_Address = 99
CE = 4.12
TPK = 2251
Si= 1.63
C = 3.58
TPL = 2150
TPS = 2050
Can anyone tell me how to do this?"
View 1 Replies
View Related
Jun 7, 2007
/* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/
The task is:
Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.
The table: emp
Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY
What i came up with:
selectempno, firstname, lastname
fromemp
whereavg(salary)>any(selectavg(salary)
fromemp
wheresex='M')
group by sex
havingsex = 'F'
View 3 Replies
View Related
Aug 7, 2007
Here is my code to connect, any help would be appreciated. I am a total noob at this.
############### Code
<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="orderofb_test"; // Database name
$tbl_name="orderofb_test.members"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// username and password sent from signup form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword");
header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}
?>
This is the error message I receive.
############### Code
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/orderofb/public_html/php/checklogin.php on line 11
cannot connect
I changed all the permissons to 777 and I still receive this error.
$tbl_name="orderofb_test.members"; // Table name
Is this the correct table name? Here is a pic of my phpmyadmin
http://mnetcs.com/thumb/storage/b3583134.JPG
View 2 Replies
View Related
Jul 20, 2005
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View 1 Replies
View Related
Sep 26, 2007
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person.
I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID
and e.EMPLOYEE_ONCLOCK <> 'False'
and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID
and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name
that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
This is a sample of the tm_punch table
Code:
PUNCH_ID PUNCH_SEQNR PUNCH_EMPID PUNCH_DATE PUNCH_TIME PUNCH_MODIFIED PUNCH_PC PUNCH_CLOCKID PUNCH_DATA1 PUNCH_DATA2 PUNCH_DATA3 PROJECT_ID
141 0 52 2007-07-05 00:00:00 603 -1 0 0 0 0 0 NULL
142 0 52 2007-07-05 00:00:00 909 -1 0 0 0 0 0 NULL
143 0 52 2007-07-05 00:00:00 911 -1 0 0 0 0 0 NULL
144 0 52 2007-07-05 00:00:00 914 -1 0 0 0 0 0 NULL
If the query works correctly what should be returned is just the line with id 144
can anyone point out where i'm going wrong
View 3 Replies
View Related
Aug 23, 2006
hi,
just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!
g11DB
View 8 Replies
View Related
Nov 19, 2007
Hi Everybody.
So as the subject states, I'm a total sql noob. I have a database in SQL 2k5 that was transfered over from an acces DB. In the sql db there are a few fields using nvarchar(255) but the data held is actually dates and times. I need to convert these over to datetime format but do no know how. Also, not all rows have a full mm/dd/yyyy hh:mm format. some hold only the date and can be seen as either m/d/yyyy or full out mm/dd/yyyy format. If anyone can help with this that would be great.
Thanks
View 2 Replies
View Related
Dec 14, 2007
Hi
I installed sql sever 2005 enterprise edition in my computer.Where is the management studio? Is it a different program? Can I install the express edition of the management studio?Help please.
View 6 Replies
View Related
Mar 1, 2008
Ok so i have something like this for a simple quary,
select avg(reorderlevel)/ (avg(QOH)/(avg(reorderlevel)*2)) as "Needed qty",ItemCategory from item
group by ItemCategory
What I basicly need to do is to output the quantity needed for QOH to be 2 times the amount of reorderlevel. then grouped by diffrent catagories.
Anyways I have the formula done and it should work except for some reason "(avg(QOH)/(avg(reorderlevel)*2))" gives me 0 when in fact it should give me 0.728383 or w/e. Im wondering if its omiting the decimals or what??
Because if i run that code it tells me i cant divid by zero
Sorry im new at this and might be doing this all wrong so go easy on me =/
Thanks a lot
View 7 Replies
View Related
Apr 30, 2008
Apologies first up -I dont do security so slapped wrists all round, but I have a little situation that I need to take seriously so I thought I'd try do the job right for a change.
Scenario: I have a stored procedure that I want one, and only one, 'thing' to have access to. The 'thing' will be one and only one SSIS package invoked by external scheduling software so basically a call to the dtexec utility to run the package stored in the file system. At runtime therefore, this package has an ExecuteSQL task which connects to the server and runs the sproc. Given windows authentication how do I ensure that the ONLY user allowed to execute the stored procedure is whatever user happens to invoke the package but that this same user can never log in to sql server and execute the stored procedure. In even simpler terms, I want nothing on earth to run that stored procedure nor select the data from the table other than this package when it runs, regardless of who runs it, it is only internal processing by the package that gets the data.
View 1 Replies
View Related
May 9, 2007
My background is networking with a lot of SQL Server 6.5-2000 dba/sql code thrown in. I've written some straight ASP pages, some Access DB frontends to SQL etc and I just can't get this .Net stuff.
I'm trying to work my way thru the getting started vids here and can't get a db connection to the membership db. I ran the aspnet_regsql.exe to create the db on my sql dev box, and it won't connect. I've added the code from http://aspnet.4guysfromrolla.com/articles/120705-1.aspx to the web config.
I erased the connection strings from the web config....I installed SQL 2005 express on my local machine and STILL CAN'T get anything other than can't connect to data store, data store doesn't exit, click here to crash....blah blah blah.
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Unable to connect to SQL Server database.
blah blah
Test the AspNetSqlProvider and get
Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.
again.
I know the machine itself is fine. It built the db on both the local machine and the remote sql server. I can connect to the local and remote sql servers using sql management studio.
Can someone help a brother out?
View 2 Replies
View Related
Feb 5, 2005
I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not!
How can I set my SQL Server 2000 to be case sensitive as well?
View 1 Replies
View Related
