Setting Value Returned Via App_name() Function
Jul 31, 2007
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?
View 5 Replies
ADVERTISEMENT
Aug 29, 2006
Hi
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
Thanks for reading this whole thing
View 7 Replies
View Related
Dec 23, 2013
When I use for instance:
SELECT
FORMAT(BegDate,"dd.mm.yyyy")
FROM TABLE1
result is - |BegDate|22.12.2013.......................|
Is there any way to limit length of string returned from FORMAT function .
Database is on ACCESS.
View 1 Replies
View Related
Aug 22, 2006
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
Thank you in advance,
Bishoy
View 1 Replies
View Related
Feb 13, 2004
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!
View 3 Replies
View Related
Feb 21, 2008
Hi Guys,
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 would appreciate any help
Thanks & Regards
View 4 Replies
View Related
Feb 2, 2008
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.
Thanks for any help.
Wesley Marshall
View 4 Replies
View Related
Apr 18, 2007
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
RETURN
END
View 2 Replies
View Related
Jun 20, 2006
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)
View 2 Replies
View Related
Aug 1, 2005
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
View 3 Replies
View Related
Dec 9, 2007
Hi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
View 8 Replies
View Related
Oct 19, 2004
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
View 1 Replies
View Related
Mar 22, 2006
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
View 1 Replies
View Related
Jan 7, 2014
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.
View 5 Replies
View Related
Jul 24, 2007
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
View 7 Replies
View Related
Oct 22, 2007
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?
View 1 Replies
View Related
Jan 9, 2007
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
View 6 Replies
View Related
Sep 27, 2007
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
View 3 Replies
View Related
Sep 10, 2004
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?
Thanks in advance.
View 2 Replies
View Related
Dec 21, 2004
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
SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled
FROM tbl_units as A
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
-----------------------------------------------------------------------------------------------------
View 3 Replies
View Related
Mar 9, 2006
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
)
View 1 Replies
View Related
Nov 19, 2004
Hi,
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?
Thanks,
Alph
View 3 Replies
View Related
Dec 7, 2011
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.
SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,
[code]...
View 3 Replies
View Related
Jan 27, 2012
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.
View 2 Replies
View Related
Mar 25, 2015
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
What i need as a output
Username | Application | Role
"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.
View 2 Replies
View Related
Apr 15, 2008
Hi
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".
Please help me.
Thanks
Mark
View 7 Replies
View Related
Apr 29, 2008
Select * from table where field <> 'This'
Why does that not return nulls? 'This' does not equal null. Why does sql server work this way?
View 2 Replies
View Related
Dec 18, 2013
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.
View 5 Replies
View Related
Jun 12, 2007
I am using SQLExecute() in C++ code.
My query is "select * from Revoked_users"?
How I will come to know the no. of rows returned by SQLExecute() ?
View 4 Replies
View Related
Jan 26, 2008
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.
View 2 Replies
View Related
Jan 29, 2008
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
View 8 Replies
View Related
Apr 7, 2008
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
t1.[DateTime] = t2.[DateTime]
ORDER BY t1.[DateTime];
View 11 Replies
View Related
Aug 28, 2007
Okay, this is what I have as my store procedure:
DECLARE @parentPage varchar(250)
-- Insert statements for procedure here
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.
View 1 Replies
View Related
