Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
I have the following sp:ALTER PROCEDURE myspPrepareTextForHTML @MessageContent nvarchar(1400), @returnval nvarchar(1400) outputASBEGINSET NOCOUNT ON;SET @returnval='something'RETURN @returnvalENDWhen I try this: EXEC myspPrepareTextForHTML @MessageContent='dfgsdfgdf', @returnval OUTPUT print @returnval I get the error:Must declare the scalar variable "@returnval". How can I get this to work?
I have a c# app. This is a piece of code out of a stored proc. it is erroring: Procedure or function getTopParentDealerFromChildDealer has too many arguments OR @dealerID is not a parameter for procedure getTopParentDealerFromChildDealer.(if I put ",@dealerID=@parentID)
I have tried all combinations "@dealerID",@dealerID=@parentID" etc.
BEGIN --get the top parent dealerID DECLARE @parentID INT SET @parentID = 0 EXEC getTopParentDealerFromChildDealer @dealerID, @parentID OUTPUT IF (@parentID>0) BEGIN
------------------------------------------------------ here is the getTopParentDealerFromChildDealer as called ------------------------------------------------------ ALTER PROCEDURE getTopParentDealerFromChildDealer @childDealerID INT AS
SET NOCOUNT ON DECLARE @dealerID INT DECLARE @parentID INT SET @dealerID = 0 SELECT @dealerID = dealerParentID from dealerRelations where dealerChildID = @childDealerID
WHILE @dealerID <> 0 BEGIN declare @temp INT set @temp = @dealerID IF (SELECT count(dealerParentID) FROM dealerRelations WHERE dealerChildID = @temp)>=1 BEGIN SELECT @dealerID = dealerParentID FROM dealerRelations where dealerChildID = @temp END ELSE BEGIN SET @dealerID=0 set @parentID = @temp END END
if (@parentID IS NULL) BEGIN set @parentID = 0 --set @parentID = @dealerID END
return @parentID
I don't usually use stored procedures but the job I have taken over previously used them. Any help would be much appreciated.
HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question: Is there a way to INSERT INTO table EXEC dataProc *Without* making a temp table that takes in every single column the dataProc puts out?
I was wondering is there was is a way in SQL 2005 to not only email job completion notifications but also include the output of a step (e.g. TSQL step) within the email. I was able to accomplish this in the past with SQL 2000 by writing output to a file and then adding an extra CMDEXEC step that exectuted BLAT (CLI SMTP engine) that sent the contents of the output file as email body out of band. Although this worked and I can do the same with 2005 it is not very elegant and I was wondering if 2005 version might have something built-in.
I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!Here is my code: Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mySqlDataSource As SqlDataSource = New SqlDataSource
' Add Some Parameter Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String) someParameter.DefaultValue = New String("Some Value") someParameter.Direction = Data.ParameterDirection.Output
Hi All !Is it possible to get rid of these dash symbols which are underliningthe column name when recordset is returned after query execution ?For example, using isql.exe:SELECT 'blah'goproduces the following results:----blahWhat I want to achieve is justblahI know that SET NOCOUNT ON switches the "X row affected" thing. Buthow about column headers ?Thanks for your time,Seeker
I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hello, The SqlString variable; if a null value is assigned, will Value return the text null? Also, if I use another type such as SqlInt32, when I do ToString(), if the value is null, will it return the text null, so that it will work with a SQL string? I was wondering if that was the case. Brian
I have a stored procedure that outputs an XML result. As it is right now, I will periodically go into SQL Server Mngmnt Studio and run the procedure, then save the XML output to an XML file that my website uses. The procedure takes too long run (it is recursive through the entire data set) to have the website call the procedure on its own, and wait for the results.
My question is this: Can I create a trigger that will run this stored procedure and save the results to a file? Is that possible? The trigger would call the procedure any time a specific table is updated (which will be a rare occurrence), then save the file to the path provided so the website users could always have the most up-to-date information without having long wait times caused by long waits for the procedure to run.
Any advice on how best to accomplish this will be appreciated. Thanks in advance.
I have a dynamic database that will be periodically queried to selectthe data from a blob field. This blob data field is text of a variablelength. The data will be selected using an id field and a date range.There will be multiple blob fields returned that I would like to outputinto a txt file in a local folder.I have the blob fields showing up as text in the field and not areferring link. Can someone point me to an output to text solution?Thanks
When I run my CLR/C# stored procedure inside Visual/BI Dev Studio, the results in a datatable are sent over pipe and displayed in output window without the grid format. I looked up inOptions and settings but couldn't find a way to change the format of output window to grid. I would like to be able to do that so I can copy/paste the results in excel.
I am rewriting several stored procedures that originally had lots of "multiplicated" code. I am aware that references to objects within dynamic SQL do not create dependencies, so I intend to add code that will generate the dependencies but will NOT produce any output in both the Results and Messages tabs, not be overly "messy" or complicated, and have the least impact on execution plan creation as possible.
As we use a dependency list of tables used to our support staff pinpoint possible data issues associated with each of these stored procedures.
I have tried a few methods already, including this:
SET @SQL = N'SELECT Column1,Column2 FROM dbo.TableName'; ... /***************************************************************************************/ /* This code block is only to establish dependency of objects used within dynamic SQL. */ /* */ /* SET statements are used so that no output is produced in Results or Messages tabs. */ /* Object existence check avoids error 208, "Invalid object name" message. */ /***************************************************************************************/ DECLARE @DependentObject SQL_VARIANT; IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL SET @DependentObject = ( SELECT TOP (1) Column1,Column2 FROM dbo.TableName); /* End code for dependency of objects used within dynamic SQL */
I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.
Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):
557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS (only 12 pks in stock that will ship today) ^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000 ^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^
I am using vs 2005 and sqlserver 2005 to manage a large database. I need to create a series of text files based on the value of one of the columns. Is there an sql command that will pipe the output directly to a file?
Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
Can the same result be achieved sending as attachment with dbmail?
EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query
Change the query to return column headers in resultset SELECT 'CustID' as f1, 'name' as f2 UNION ALL SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set
I have been given a request at work that requires us to run the SQL scripts that are held in a report configuration table and output the results as .csv or.xls files in a desired folder with a file name that is also specified within the report config table.
An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.
Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.
------------------------------------------------------------------------------------------ -----SAMPLE TABLE SCRIPT ----------------------------------------------------------- ------------------------------------------------------------------------------------------ /****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Test_Table_PS]( [File_Name] [nvarchar](100) NULL,
