Exec Without Results
Feb 20, 2007this might sound dumb or impossible, but is there a way to run exec commands forcing them to not return results
View 6 Replies
ADVERTISEMENT
EXEC @SQLString With Output Results
Jun 2, 2005Hello, 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
View 1 Replies View RelatedWierd XML Results From 'Exec SQL Task
Sep 15, 2006Hi All, we got a problem that I hope you guys can help us with.
We are trying to extract data from a SQL database in XML format to a variable (string) using an 'Execute SQL Task'
In the task we are using an ADO.NET connection to the database which is fine and via direct input we are using this query.
SELECT
title,
initials,
surname,
username,
gender,
emailaddress,
addressline1,
addressline2,
addressline3,
town,
postcode,
country,
emailcontact,
postalcontact,
telephone
FROM
CRMCustRecords
FOR XML path('customer'), root('CRMData'), ELEMENTS XSINIL
In SSMS this is fine and returns an ok (to my eyes anyway!) XML resultset.
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxxx</surname>
<username>xxxxxx</username>
<gender>x</gender>
<emailaddress>xxxx@xxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxx</town>
<postcode>xxxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>99999999999</telephone>
</customer>
<customer>
</CRMData>
However the SSIS task (running exactly the same query) is returning
<ROOT>
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxx</surname>
<username>xxxxxxxx</username>
<gender>x</gender>
<emailaddress>xxxxx@xxxxxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxxxxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxxx</town>
<postcode>xxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>9999999999</telephone>
</customer>
</CRMData>
</ROOT>
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.
Thanks for any and all input
Pat
Capturing The Results From Exec Command
Apr 21, 2006Hi,
I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.
exec(select count(*) from @dynamictable where condition = @dynamiccond)
But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?
Thanks
Need To Pull A Select Few Results From INSERT EXEC
Aug 17, 2006I'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?
Any help is much appreciated.. thanks!
Join Results From Multiple EXEC Calls
Apr 25, 2006I would like to make inner join with results from different exec %procedureName% calls.
thanks.
Linked Server Exec Stored Procdure Results In Transaction Context In Use By Another Session.
Nov 3, 2005I am using sql 2005 beta
View 23 Replies View RelatedCan Exec Select But Can't Exec Sp
Oct 31, 2007I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?
Apr 1, 2007hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedSQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?
Mar 25, 2015I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
Feb 12, 2008Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
Mind-boggling Gridview Results! Different Results For Different Teams..
Jun 18, 2008Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Removing Individual Results From A Paged Set Of Results.
Oct 19, 2007Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
PASTE SQL RESULTS INTO EXCEL - Funny Results
Jan 30, 2008Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies View RelatedUsing Exec
Feb 29, 2000When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob
Exec
Jul 22, 2003When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
Thanks
Exec In My Sp
Nov 1, 2007I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...
create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)
---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------
as
declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName
------------------------------------------------------------------------------------------------------
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
Exec Sql
Apr 28, 2006I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
in particular, the code i am examining is
sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
MDX And EXEC
Feb 17, 2006Hi,
Can I execute my MDX statement as in the exhibit format!
DECLARE @test VARCHAR(MAX)
SET @test = 'WITH test AS (SELECT * FROM merchants)'
EXEC(@test)
SELECT * FROM test
If I don't use that dynamice sql statement, everything work fine.
Thanks,
Myo
EXEC
Mar 28, 2006Hi,
I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...
declare @strsql varchar(500)
set @strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'
exec (@strsql)
When I execute the above script, I immeaditely see the results in the query analyser like below:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0
Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....
Just wondering whats goin on..
Thanks.
Ranga
Get A Return Value With EXEC?
Aug 29, 2007Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies View RelatedExec And Sprocs
Nov 7, 2000is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?
i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.
EXEC Dynamic Sql
Oct 17, 1999How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)
Problem With USE And EXEC
Aug 23, 2000I found this statement in BOL and it didn't make it to work.Is anybody out there who ha the same problem?
Database is MASTER.
USE master EXEC ("USE pubs") SELECT * FROM authors
EXEC Dynamic Sql
May 13, 2003Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.
Print @Table (db and table name: opms..transactions)
Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
Print @sql
EXEC (@sql)
opms..Transactions
Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)
Server: Msg 1004, Level 15, State 1, Line 1
Invalid column prefix 'opms.': No table name specified
Any idea?
mipo
EXEC Statement
Aug 22, 2002I would like to execute something like that with sql6.5 :
select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (cmd)
I tried like this but I don't know what's wrong
exec ("USE "+ RTRIM(@dbname) +"exec sp_helprotect")
Thank you
How To Use @@ERROR For EXEC(@SQL)
Jun 25, 2005Hi,
I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that...
----------------------------------------------
DECLARE
,@DBName VARCHAR(128)
,@LoginName VARCHAR(128)
,@SQL VARCHAR(2000)
SET @DBName='dbname'
SET @LoginName='loginname'
SELECT @SQL=@DBName+'..SP_EXECUTESQL N''SP_REVOKEDBACCESS ['+@LoginName+']'''
EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
PRINT @@ERROR
END
--------------------------------------------------------------
Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like ,
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.
But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .
So How can I retrieve the error value 15008 in a variable ..?
:confused:
Dynamic SQL And EXEC
Aug 11, 2005Hello,
I'm trying to do something like the following, but it keeps complaining that I need to declare @max, even though I have (and it is of the same type as link_id).
EXEC('SELECT @max=MAX(link_id) FROM '+ @str1)
I've looked into sp_executesql but I'm not entirely sure how that functions. Any suggestions?
Exec Xp_sendmail
Aug 12, 2005How do I attached the file by using xp_sendmail?
exec xp_sendmail
@recipients='myemail@yahoo.com',
@subject='test',
@attach_results='True'
??? 'C:FileName.xls'
How To Allow Use To Exec Xp_sendmail
Sep 20, 2004I have a stored proc that assigns a value to a field based on user input from an Access front end.
The last part of the stored proc sends an email if certain conditions are met.
It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?
The users are getting this message:
EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)
Exec In A Function
Oct 15, 2004I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks much
Exec @string
Oct 24, 2005I am trying to do an insert statement utilizing a variable string.
something like:
Set @cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @lServer + ';User ID=' + @user + ';Password=' + @pword + char(39) + '
).myServer..
Insert into #Temp_table (field1, field2)
select exec @cString
--What is the syntax for this?