Is there a way to set what value is returned via app_name function?
I have a SQL Server 2005 database that is written to by a MS Access front end... but I have users who have written their own access front ends that are manipulating data incorrectly. I want to allow the approved access applications but disallow the user written ones.
Application roles are not an option because there is also a propietary software package that writes to the databases that we would not be able to modify to work with application roles. Allowing a whitelist of named applications is my only way to pull this off.
How can I make MY access application report nameX instead of Microsoft Access or other similar generic name?
I have to implement table level loggin on a bunch of tables. The problem is that I also have to log the user that made the changes
Obviously the right way would be to run everything tru stored procs - but that is not possible for me because there is a lot of existing functionality built already - some direct sql and a super large number of sprocs.
So I wanted to go with a trigger based approach .However since the changes are being made by a web app SQL has no idea who the actual web application based user is.
I originally tried to solve the problem by adding a LastUpdatedBy column to some of the tables and forcing the functions to update that too - so that the trigger can read that column but that has turned out to be very tedius and buggy.
So I want to try something else - so far it seems to be working but I just want to make sure I am not messing up anything along the way.
There is a function is TSQL called APP_NAME() , this return the name of the application that started the connection that the TSQL is running in. This made scense during the desktop days when it would return the actual .EXE name of the file that executed the changes.
Nowadys it's not so usefull any more becuase in a web based app all changes are being done by aspnet_wp.exe so i don't know if that info is usefull - as a matter of fact all access thru ado.net default to ".Net SqlClient Data Provider" .
So my thinking is like this - if I add 'Application Name= userid" to the connection string before I open it - then I will have access to that userid thruout any changes that usermakes via APP_NAME()
I realize that I may be messing up the connection pool a bit - but I have relativley few (60) users who are on the system for long spans - so each one will be able to reuse their own connectiions.
I just want to make sure before I go off on this that i am not messing things up badly
Hi All, I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set.
I made that code but it returned null, any help please?
create trigger DatabaseEvents on database for ddl_database_level_events as --select eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') declare @data xml select @data = eventdata() select Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type' ,Col.value('(/PostTime/text())[1]','datetime') as 'Post Time' from @data.nodes('/EVENT_INSTANCE') as EventsTable(Col) go
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database. I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple... One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')' EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed). Does somebody know how to make what i want?? I prefer to avoid using temporary tables. Thanks!
I wanted to know how to set the schema for a stored procedure/function. I have some tables, T-SQL stored procedures and fuctions under schema MYSOFT and I call them as follows
MYSOFT.LOOKUP_REC
I have now written some c# SP's and Functions and want to add them to the MYSOFT Schema, how would I do this. I have tried setting the Assemly Owner value to MYSOFT in the project properties but it doesnt seem to work. I can see all my other functions & SP's having the schema name as their post fix in braces
FIND_NAMES (MYSOFT)
but not my c# ones.
When I try to access my C# Function with the schema name MYSOFT I get an error message
'Cannot find either column 'MYSOFT' or the user-defined function or aggregate 'MYSOFT.FIND_NAMES' or the name is ambiguous'
I am trying to figure out how to set the Description of a Column in my database table by making a SQL function call. I know that I can go into Microsoft Studio Express and type in each desciption for each column. I just have about 1000 variables and each variable's description is in an Excel spreadsheet. I want to be able to build SQL code that will set each of the 1000 variables own description.
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer. Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16). Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned. If someone is able to help me figure this out, I would appreciate it. 1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList); 13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner] @BannerPage nvarchar(50), @MagazineID intASBEGIN SET NOCOUNT ON; DECLARE @BannerID int SELECT TOP 1 @BannerID = BannerID FROM Banners WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID AND StartDate <= GetDate() AND EndDate >=GetDate() ORDER BY Views SELECT BannerID, BannerFileName, AltText, URL, Views FROM Banners WHERE BannerID = @BannerID UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; Response.Write(RecordCount); } This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc. Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell me what I need to change to get the proper value? Thanks
I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%' UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks
I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query: SELECTEvents.*, Agencies.A_NAME AS A_NAME, (Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME, (Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME FROM Events INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID WHEREE_ID = 89
Even if all the inner join id's are zero, I would at least like to get back the event information. Could anyone help me out here, or do I have to do 4 queries?
The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
---------------------------------------------------------------------------------------------------- CREATE PROCEDURE spUnitsbyUnitID @unitid int AS
INNER JOIN tbl_radios as B ON A.unitid = B.unitid INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid INNER JOIN tbl_contacts as D on A.unitid = D.unitid INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID GO -----------------------------------------------------------------------------------------------------
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
update purchaseorders set orderstatusID = 4 where purchaseorderid in ( select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null )
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
I am getting the below error while trying to run sql query
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.
I am running a query that works just fine however, I would like it to exclude value that are equal to zero.
Basically my query looks at the commission that clients pay over a number of periods.
So it goes like this
T.Client_Code as Client ,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day
FROM TABLE T JOINING FX TABLE
WHERE fx.Currency = 'USD'
And T.Salesman_Name in ('X''Y'Z)
Group BY T.Client_Code
It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.
i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code: select top 100 username, (Select b.name from application b inner join userroleapplication c on b.applicationID = c.applicationID ) Application, (select name from role d, userroleapplication gr where gr.roleID = d.roleID and gr.userID = a.userID) role from users a where username in ( 'username' )
i know where is the problem but i cant figure how to fix this
I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query
The table with data is like this,one file maybe belong to many group.
Filename FilegroupID
file1 1 file2 2 file1 3 file1 4 file3 1 file2 5
I need write a query to get the result like this
file1 1,3,4 file2 2,5 file3 1
I try to write a query with subquery but I get error message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I use SQL Server 2005.but I need the subquery return data like "1,3,4".
I have a simple update query that looks like this:
Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID
I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.
Okay, I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."
But the point of the subquery was to in fact return more than one value.
Anyone know how I can null-out the returned values of the PrjMgr field? I have bolded the field in question. Here is the code:
select AgencyId ,ControlNumber ,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr,ISNULL(CASE LEN(datepart(mm, PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, PeriodFinish) as varchar(2)) END ,'')As month ,ISNULL(CAST(datepart(dd, PeriodFinish) as varchar), '') As day ,ISNULL(CAST(right(datepart(yy, PeriodFinish),2) as varchar),'') As year ,ISNULL(CAST(left(datepart(yy, PeriodFinish),2) as varchar),'') As century ,custName from LLA_Audits Left Outer Join Projects on controlnumber = prjcode Left Outer Join Customers on Agencyid = CustCode where AgencyId is not null and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null) and left(controlnumber,2)<>'72' and left(controlnumber,2) <>'13' and (CPAFirmId IN ('0','')or CPAFirmId is null) order by ControlNumber
The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date. Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned. BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times. Can anybody help resolve this?
SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,
[DbName].[SchemaName].[TableName2] AS t2
WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)
AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))
IF @parentPage IS NOT NULL
IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID])) SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]
And I kept getting this error when testing in SQL Management Studio.
Msg 512, Level 16, State 1, Procedure parentPage, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
