DECLARE @returnDay int
DECLARE @query varchar(8000)
--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 3
SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c: est.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @query
EXEC master.dbo.xp_sendmail @recipients='test@hotmail.com',
@copy_recipients = 'test@hotmail.com',
@message='Submitting Results for the previous month.',
@subject='BloodBank results for the previous month',@attachments = '\cenc$ est.txt'
SELECT @@ERROR As ErrorNumber
I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current date
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
Hello All, I have two procedures being run one after the other. when I run proc1 it runs for about 15 min. Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min. If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.
I have a stored procedure in SQL Server 2008 (the stored proc is actually stored there with a name) and I can run it with the 'exec storedproc_name insert_date' command (my stored proc needs a date to run).
The stored proc just creates a temp table with some data (but we can ignore this bit).
I only need a way to run this stored proc remotely (I dont care about getting the data, I just need to run it in the server).
Is there any way of doing this? Preferably via a unix system? I just need a way to run the 'exec' command. Returning data etc. isn't needed.
How does one prevent a long running procedure form crapping out in CLR? I am trying to do a pull from a distant data source and it works, except I have to break down my stored procedure call into several smaller calls. I would like to do everything in one shot, but I get the thread abort exception when I try to get a lot of data.
im getting an error when i run the stored proc with a string parameter in execute sql task object.
this is the only code i have:
exec sp_udt_keymaint 'table1'
I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.
Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?
For reasons beyond the scope of my question, is there a way to run this command within a Stored Procedure from a low privileged user login? I can grant the entity "db_ddladmin" privilege and the proc runs, but I'd rather not give out that level of permission to what is basically a glorified web access login.
Please forgive the simplicity of this question - I am not the dba type. When I connect to a server and look at my connection attributes in activity monitor, the user column shows the correct information for my domainusername. When I run a certain stored procedure in that connection, the domainusername changes to another person. We are not using execute as, setuser, or anything special to explicitly change the user. The stored procedure is in a schema that is owned by dbo (principal_id = 1 - I verified by checking sys.database_principals.)
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
I'm creating a sproc to to count the dates and times users log on to a page. I want the date and time broken out into two columns. When I attempt create the sproc I get the following error: Subqueries are not allowed in this context. Only scalar expressions are allowed.Create PROCEDURE [dbo].[LoginCounter]
@Username Nvarchar(50), @IPAddress Nvarchar(50), @BuilderID, INT
As
INSERT INTO LoginCount ([UserName], [IPAddress], [Date], [Time], [BuilderID])
Hello i have this store proc with the syntax below. The getdate get the current date but i need to change the date this one time to 3/20/2005. I was wondering is there a way to do that an not modify my sp. I tried to harcode 3/20/2005 as asofdate and i get all 0 in my table.
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
SELECT * FROMtbl_Feedback WHERE tbl_Feedback.DateIn >= @startDate GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead ' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command") set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput) set cmd.ActiveConnection=DBConnection cmd.CommandText="sp_009_SiteLead" cmd.Parameters.Append peDate cmd.Parameters.Append psdate psDate.Value = "10/01/2004" Set rsObj = CreateObject("ADODB.Recordset") rsObj.ActiveConnection = DBConnection rsObj.CursorLocation = adUseClient rsObj.PageSize= 20 rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it. Can anyone help?
What is the recommended method of returning a formatted date from a stored procedure?
The date is held in a date time field. I wish to return the date formatted as:
dd/mm/yyyy hh:mm
for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:
dd/mm/yyyy hh:mm:ss
I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.
I was looking at:
select jobHeaders.DateTimeJobTaken AS [Job Taken], CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))
from jobHeaders
but this gives : 8 /3 /2004 with spaces.
Before looking further I thought one of you guys may have the answer.
I have stored procedure which runs on some period, what i want is when it first run i want to store that date permanently or till the next time it runs again, and then i need to take my data from that store proc , where hist_date between (date stored when it ran first time,example, 08/21/2015) and today date.(exampple, 08/24/2015)
Now next time when it runs , date stored should be updated in this case it should be (08/24/2015) .
How can i do this in stored proc, I tried to use temp table but it didn't work .
Hullo all I have two machines, One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ? running the procedure as a Domain user results in a failed job/stored proc. also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ? please e-mail me at wayde@sunnygrp.com if you have any thoughts...
I have two tables that I am pulling data from: an item table and a sales table. Almost all of the information comes from the item table (item description, location, amount on hand). The last field wanted is Year-To-Date sales. I can pull the sales field from the sales table, which gives me all sales from the creation of the db. I need to be able to run a date variable of This Year on that sales field only. I have a date field I can reference off of in the sales table.
I am wondering how to achieve a running count on the rows being displayed in a table list. Not sure how to get it to show 1 for first row, 2 for second row, 3 for third row and so on. Example
No | Name 1 | John 2 | Jane 3 | Jim
I am also wondering on the date format in SSRS. When I try to format the date 10/31/2007 in a DD/MM/YYYY format it does not come out so good. Basically I go to the cell property and choose custom formatting, input the formatting string d/mm/yyyy but it only shows 31/00/2007. Can you guys tell me what I am doing wrong?
I am working on a software primarily related to accounting with visual basic
Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,
have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.
ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg
ID DATE DEBIT CREDIT BALANCE
1 02.02.2012 100.00 0.00 100.00
6 04.04.2012 0.00 150.00 -50.00
3 02.05.2012 70.00 0.00 20.00
4 02.05.2012 80.00 0.00 100.00
2 06.06.2012 120.00 0.00 220.00
must balance the figure above.
if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.
the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.
I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)
SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
I am working on creating a new report that has to get its date from several different tables. Some of these tables update at the beginning of the fiscal month, others at the beginning of the calendar month (). My goal is to develop a stored proc that will run once a month, get all the data from the various sources, and insert into a history table. An SSRS report will generate off of the data in this history table so users can see a running history of the data. I am having trouble creating a way to determine this though. Fiscal months always start on a Sunday. We have a calendar table that translates each date into the fiscal day, fiscal month, etc. Changing the legacy scripts to run on fiscal instead of calendar is not an option.
Idea 1: Wrap the script in an IF statement that will determine the date of last day of the previous calendar month, and the date of the last day of the previous fiscal month, and if current date is greater than both of those, then run the script. But this runs into issues because the current date could be in a different calendar month than fiscal month causing the idea of "previous month" to be incongruent.
Idea 2: Find the current day of year, then if this is greater than last day of the previous fiscal and calendar months, run script. Issue this this is in january, fiscal day might be 2 but calendar day might be 364, so it will never run.
Hi guys, i am getting data from db2 database on AS400 system in CSV files and i need to use this data and build a dataware house in sql server 2000. the date formatt coming from AS400 is YYYMMDD(eg:- 1011223 101---stands for year 11--stands for month 23-- stands for day of the month )
can anybody advice me how to encounter this problem. thanks.
I made one report in SSRS 2008 in which getting data from one SharePoint List.
three parameters in report :
Country StartDate EndDate
I am using query which I created with CAML.
query is running well and data is coming correctly if I run this on Query designer.and date format must be YYYY-MM-DD.but when I try to run through on run time then the date control is showing format dd/mm/yyyy.
I change the regional settings of windows and SharePoint site to English(United States). and when I select date control it is also putting date in format like "YYYY-MM-DD'. and in that format report is working well in Query designer view.But on run time still it is not working.
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
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.
Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000. Basically I am calling proc2 from the inside of proc1. proc2 returns 2 resultsets. I want to process these two resultsets from within proc1.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
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
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut
