Hello,
Lets say I have a SP that return 1000 records,
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
Hi!!!
i have a problem. I want to execute a sp from a DTS package but it doesn't work correctly. If i execute it from the query analyzer it work fine.
I have configured merge replication with pull subscription on the data base.
If someone can help me i will apreciate.
Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()
Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapter.Fill(dt)
Dim row As DataRow
Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]
@InputDate Datetime
AS
SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions
Where
dtmDate > @InputDate
Order By dtmDate
I have a INSERT INTO where i retunr the result from a store procedure. But I want to only insert the data if the row not already exist. How can i do that? (See Where xxxxxxxxxxxx).
I can't use a function as i store data in a temporary table in the store procedure.
--Get Generated Times
INSERT INTO @GeneratedTimes(
ResourceId ,
DateFrom ,
DateTo )
EXEC dbo.P_GenerateTimes @ApplicationId , @EventId , @FromDate , @ToDate , @WeekScheduleId , @FromTimeToBook , @ToTimeToBook
WHERE xxxxxxxxxxxxxxxxxx
I have a Execute SQL Task that calls a stored procedure. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. It has a date parameter, but hard coding the date parameter does not work either. I checked the permissions on the Stored Procedure and SQL Agent, which are ok. When I change BypassPrepare to True, the query will execute and insert the rows to the required table. If someone has an explanation for this, I would appreciate some sql enlightenment. Thanks.
Dan
Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."
The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155
The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------------------------------------------------
EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'
Thanks for your help
ehx5
Hi, I'd like to know which is the best choice to execute a store procedure that execute transformations from table to files using bcp command, 2 millions rows everyday (i'ts a Data Warehouse database). So in the sql task in connectionType I had choiced ADO.NET is the best option?
Thanks
Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:
USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]
It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:
Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.
Please give me some helps. Thanks.
hi,
i need execute a store procedure of Informix from sql server integration services.
i use driver IBM informix provider 3.2 and have linked server Informix and use sql task for execute the procedure
it's posible??
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks
HI, I'm a simple store procedure that returns a result such as this one:
AM
AM-1
AM-2
AM-n
and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile
select fields from table where id in (execute sp)
how can I make this?
Thanks a lot.
hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT
THANKS
jk
I want to create store procedure which will print out something like this:
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn)
But first one print out good, as I expected. I either got nothing or the error message on second piece.
Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
Any Help will be greatly appreciated.
on SQL 2005 SP1.
Here is my example code (not the real one):
Create Procedure [dbo].[makeNewString]
@myBookID
As
Declare bookID int
Declare newRowID int
Declare insertBookInfoString nvarchar(150)
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[gameBooks] value (' + @gameBookID + ',' + gameBookTitle +',' + ganeVolumn +')'
from [dbname].dbo.[gameBooks])
where @gameBookID=@myBookID
Print insertBookInforString
select 'SET @newRowID = Scope_Identity()'
print @newRowID
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[cookBooks] value (' + @cookBookID +',' + cookBookTitle +','+cookVolumn+')'
from [dbname].dbo.[cookBooks]
where @cookBookID=@myBookID)
Print insertBookInforString
first insertBookInfoString prints out fine.
But I kept this error on second part:
------------------------------
DECLARE Declare @newBookID int
(1 row(s) affected)
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) (this is what I want)
-----------------------------------------
SET @newBookID = Scope_Identity()
(1 row(s) affected)
Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View 3 Replies View RelatedHow do you use data from a select statement as inputs for a store procedure?
e.g.
Code Block
Select FirstName, LastName from Student where Grade = 'A'
And I want to use all the FirstName and LastName as inputs for this store procedure
Code Block
Exec StudentOfTheMonth @FirstName = FirstName, @LastName = LastName
Thanks
Hi,I'm not a big friend of MSSQL, but I have to do one query I've done formySQL.But I don't know how...I have to select 'user' from 'db' where first letter is E or N, second is Bor 0 and after that there are 6 or 7 digits I know.How can I do that?In mySQL it would be something like:SELECT * FROM `table` WHERE `account` regexp '^[EN][B0]123456$' ORDER BY`Id`;Thanks in advance,Martin
View 10 Replies View RelatedI've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables.
But some procedures of above cited, in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables.
The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok.
You troube the same, some help please :)
HiI have a large table with allot of data in it, Im trying to find the correct SQL statement to select all the records that do not start with a letter or a number.So some records may start with a '~' and a few '$' and even some '?'. So I would like a statement that would select all of these while leaving out any records that start with anything else, such as numbers or letters.And on a similar theme, Im also trying to select all records that start with a number. Ive been using the "select blah from blah where LIKE 'a%'" for each letter and ive tried to have a play and search around that statement....
Today I was told to use a regex within my 'SELECT' statement, but I had never heard of this being done before... so I done a number of searches on Google and although I found references, I didnt actually find any examples. So when I tried to put something together I kept getting errors.
Ive used regular expressions before so theres no problem writing the expression that I need, however Ive no idea how to implement it into a SELECT statement.
If someone could please show me an example of the structure/syntax of an sql select query that contains a regex then I would be most appreciative! :)
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
View 14 Replies View RelatedHi,
I want to run a stored procedure automatically at regular intervals.The time duration is given by me as input details.for example i will
give the date and i should send email to some recipients just one day before the given date.I want this to be done in sql stored procedures.
Can anyone help?
Thanks in advance,
Nithya
Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
Hi,
I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.
The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.
The SET OPTION are same for both the statement.
To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.
I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.
This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.
Data
1. Stored Procedure
/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pPatientFindByID]
@patientID uniqueidentifier
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
SELECT
[patient].[AVINumber] AS 'AVINumber',
[patient].[BirthDate] AS 'BirthDate',
[patient].[BreedID] AS 'BreedID',
[patient].[ChangeDate] AS 'ChangeDate',
[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',
[patient].[ClientID] AS 'ClientID',
[patient].[Color] AS 'Color',
[patient].[County] AS 'County',
[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',
[patient].[CreateDate] AS 'CreateDate',
[patient].[CreateWorkstationID] AS 'CreateWorkstationID',
[patient].[DeathDate] AS 'DeathDate',
[patient].[DeathReasonCode] AS 'DeathReasonCode',
[patient].[FirstVisitDate] AS 'FirstVisitDate',
[patient].[HasAllergy] AS 'HasAllergy',
[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',
[patient].[HasChronicCondition] AS 'HasChronicCondition',
[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',
[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',
[patient].[HospitalID] AS 'HospitalID',
[patient].[IsBites] AS 'IsBites',
[patient].[IsBitesInFear] AS 'IsBitesInFear',
[patient].[IsClinicalStudy] AS 'IsClinicalStudy',
[patient].[IsLitters] AS 'IsLitters',
[patient].[IsMixBreed] AS 'IsMixBreed',
[patient].[IsScratches] AS 'IsScratches',
[patient].[LastVisitDate] AS 'LastVisitDate',
[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',
[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',
[patient].[MicrochipNumber] AS 'MicrochipNumber',
[patient].[Name] AS 'Name',
[patient].[Note] AS 'Note',
[patient].[PatientID] AS 'PatientID',
[patient].[RabiesTagNumber] AS 'RabiesTagNumber',
[patient].[SexCode] AS 'SexCode',
[patient].[SpeciesID] AS 'SpeciesID',
[patient].[Status] AS 'Status',
[patient].[SystemLog] AS 'SystemLog',
[patient].[Weight] AS 'Weight',
[patient].[WellnessplanLevel] AS 'WellnessplanLevel',
[patient].[WellnessplanValue] AS 'WellnessplanValue'
FROM
[Client].[Patient] [patient]
WHERE
[PatientID] = @patientID
SET NOCOUNT OFF
END
2. sp_executeSQL
set nocount on
execute
sp_executeSQL N'
SELECT [patient].[AVINumber]
,[patient].[BirthDate]
,[patient].[BreedID]
, [patient].[ChangeDate]
, [patient].[ChangeWorkstationID]
, [patient].[ClientID]
, [patient].[Color]
, [patient].[County]
, [patient].[CountyRabiesTagNumber]
, [patient].[CreateDate]
, [patient].[CreateWorkstationID]
, [patient].[DeathDate]
, [patient].[DeathReasonCode]
, [patient].[FirstVisitDate]
, [patient].[HasAllergy]
, [patient].[HasBitesOrScratch]
, [patient].[HasChronicCondition]
, [patient].[HasDeclinedPreventiveCare]
, [patient].[HasMedicalAllergy]
, [patient].[HospitalID]
, [patient].[IsBites]
, [patient].[IsBitesInFear]
, [patient].[IsClinicalStudy]
, [patient].[IsLitters]
, [patient].[IsMixBreed]
, [patient].[IsScratches]
, [patient].[LastVisitDate]
, [patient].[ManufacturerNameOfAVIChip]
, [patient].[ManufacturerNameOfMicrochip]
, [patient].[MicrochipNumber]
, [patient].[Name]
, [patient].[Note]
, [patient].[PatientID]
, [patient].[RabiesTagNumber]
, [patient].[SexCode]
, [patient].[SpeciesID]
, [patient].[Status]
, [patient].[SystemLog]
, [patient].[Weight]
, [patient].[WellnessplanLevel]
, [patient].[WellnessplanValue]
FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID
',N'@patientID uniqueidentifier'
,'B1ABC1EA-D4AB-DB11-921E-00087434402F'
set nocount off
3- Trace Data
Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710
DBA
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.
All the tests where made using the Query Analizer.
What could be wrong?
Thanks a lot in advance.
Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.
Please guide with some facts.
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.