I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell: declare @cmd varchar(5000) set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;c:\foo.xlsx' print @cmd exec xp_cmdshell @cmd This works fine, ho...
Looking on some source code I've inherited, there's a snippet of code that calls a SQL stored procedure making an Update. The stored procedure returns -1 in case something goes wrong: IF @@Error <> 0 BEGIN ROLLBACK TRAN SELECT -1 RETURN END COMMIT TRAN SELECT 0 The C# code is something like this: ...
I'm currently working on a new system that uses already existing stored procedures that are used in another system. They contain lots of logic for validation etc. so I want to use them in my new system and call them from Entity Framework 4 using WCF RIA Services. Is there a way to only supply a certain number of params as some of the S...
When a sproc is executed on one of our ASP.NET pages, it times out on the SQL Server with the exception Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. When I execute the same sproc in SSMS, it returns relatively quickly. The SQL Server and IIS are on the same box. I logg...
Hi, I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ? So considering performance and other aspects when and why should I prefer one over the other ? ...
So, I'm writing this Stored Proc and I really suck at SQL. My Question to you guys is: Can I select an entire row and store it in a variable? I know I can do something like: declare @someInteger int select @someInteger = (select someintfield from sometable where somecondition) But can I select the entire row from sometable and stor...
hi friends, http://pastebin.ca/1946913 When i write "IN(1,2,4,5,6,7,8,9,10)" inside of the procedure, i get correct result but when i add the id variable in the "IN", the results are incorrect. I made a function on mysql but its still not working, what can i do? ...
I have this IF statement in my Stored Procedure: if ((@someParam = 'C') OR (@someParam = 'I' AND @SomeOtherParam <> 2 AND @SomeOtherParam <> 4 AND @SomeOtherParam <> 5)) My question is can I check @SomeOtherParam in one shot, instead of having to check it 3 separate times? ...
Hello, I worked on a project that had the following requirement: TableA is the parent table. Whenever any children records of TableA are updated the 'LastActivityDate' field in TableA should be updated with the current UTC date. Now, I know there are many ways of doing this. My post is NOT about the many different ways this coul...
mysqli_query($link, "CALL my_first__status_call('Other',@out_value);SELECT @out_value"); This is my first sp program , my above SP throwing Error on execution , How to call the sp in mysqli php am getting the below error Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in ...
What is the fastest option to issue stored procedures in a threaded environment in Java? According to http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-basic.html#connector-j-examples-preparecall Connection.prepareCall() is an expensive method. So what's the alternative to calling it in every thread, when synchronized access ...
I'm getting a SQL Server error on a stored procedure (although it appears to run and run correctly). When I view the stored procedure call in my code, it is underlined, and the highlighted error message says: "Procedure sp_ReferenceSite has no parameters and arguments were supplied." My procedure is called like this: execute dbo.sp_Re...
$link = mysqli_connect("localhost", "username", "testpassword", "dbname")or die('connection problem'); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } //if ($result = mysqli_query($link, "CALL my_sqrt(Other,@out_value)")) { $result =...
What are the security reasons that extended stored procedure xp_cmdshell is disabled by default? ...
Hi Friends, I need to develop a SQL Procedure,i am having 2 table one,with following details userid h1 h2 h3 h4 h5 h6 h7 date and another table with following details useid per why i need a procedure,because, the data to the first table is of follows userid h1 h2 h3 h4 h5 h6 h7 date 1 p p p p p p p 2010-10-10 while data is updat...
I have seen transaction usage in some cases but never really understood in which situations they should be used. How and when transactions should be used (begin transaction statement)? I have read that Microsoft do not suggest to use transaction statements (commit, rollback) inside a trigger and stored procedure. ...
my stored procedure is- CREATE PROCEDURE [dbo].[usp_SetMenu]( @locationId BIGINT, @menuId BIGINT = NULL, @name VARCHAR(100) = NULL, @taxable BIT = NULL, @type VARCHAR(100) = NULL, @dateFrom DATETIME = NULL, @dateTo DATETIME = NULL, @timeFrom VARCHAR(10)...
I'm working on a system to store appointments and recurring appointments. My schema looks like this Appointment ----------- ID Start End Title RecurringType RecurringEnd RecurringTypes --------------- Id Name I've keeped the Recurring Types simple and only support Week Days, Weekly, 4 Weekly, 52 Weekly If RecurringType is null the...
Hi, I want to have list of all SPs from my database that fulfill some conditions, say all those SPs that have StudentId in them as i want to update those SPs where StudentId would be a key column. How can i get the list of all such SPs? Thanks for your inputs. ...
I'm working on a web app that is using PHP and MSSQL. One of my requirements is to use the field descriptions in MSSQL in part of the web app. I know the Stored Proc. for adding descriptions in MSSQL is: EXEC sp_addextendedproperty @name = N'Description', @value = 'Description goes here', @level0type = N'Schema', @level0name = 'schem...