if I have table XXXX with columns a,b,c,d,e,f,g,h,i and I need a function or stored procedure.If I use SELECT a,b,c,d from XXXX and the function returns the result set with columns e,f,g,h,i only Means the columns used in Select must not be included in the result set.
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
I am using SQL select query to select MIN from column data but I have 12 columns and want to select MIN of all these 6 columns in one SQL select statement.
I have written as SELECT MIN(temp_sv,humidity_sv,SH1,SH2,SH3,SH4) from production but its not working... How to do this....
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as begin Declare @ret int; Declare @EnterVolumeTime int; Declare @ExitVolumeTime int; Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
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 have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
What is the counterpart of this function when using SQL Server 2000? Im getting this error "System.Data.SqlClient.SqlException: 'Val' is not a recognized function name"
What do I have to change in my queryString?
SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1, ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded, ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo, ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID = CEN_USERS.UserBadgeNo WHERE (((ASP_MainForm.FormStatus)<>'Approved') AND ((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC
Hello there... I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename... This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that.... I was searching BOL but I couldn't find like this function in SQL Server.. So I need help:-)))) Because everybody has a different length of the lastname, I have a problem. I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...
I have a database in Access with the following fields: Portfolio Code Trade ID <- Uniquie Trade Shares Order Decision Price Trade Execution Price Trade Date
I want to retrieve total traded market value for the 2nd quarter (months 4, 5 and 6) for specific portfolio's. When I retrieve a list of all individual trades (by trade ID which is unique for each), Trade Shares, Order Decision Price and Trade Execution Price and do the Multiplying in Excel (i.e. Order Decision Price * Trade Shares and Trade Execution Price * Trade Shares), the final aggregate total looks correct. However, when I try to do the calculation in Excel and group it so I do not get a list of all trades, just the total Traded Market Value ( using Sum([Order Decision Price]*[Trade Shares]) and Sum([Trade Execution Price]*[Trade Shares]) ) in design view as Expression, the total traded market value is larger than what it was in the first step when the calc was done in Excel. It seems that some of the reported values are exactly double of what I get while doing the calc in Excel.
This is only happening in the 2002 database. When I use the Sum method in all other databases, the results are 100% on.
Any ideas??
Example of the SQL created by design view: SELECT Sum([Order Decision Price]*[Trade Shares]) AS Expr1, Sum([Trade Execution Price]*[Trade Shares]) AS Expr2 FROM TradeHist WHERE (((TradeHist.[Portfolio Code])="852" Or (TradeHist.[Portfolio Code])="2CM" Or (TradeHist.[Portfolio Code])="2CN" Or (TradeHist.[Portfolio Code])="2WA") AND ((Month([Trade Date]))=4 Or (Month([Trade Date]))=5 Or (Month([Trade Date]))=6));
Does anyone know if you can call an Access function from DTS? I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.
I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!
I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.
hello, i have 2 tables joined, the Pictures and the aspnet_Users at Pictures table i have some 2 rows (UserId and LastUser - both are uniqueidentified) how can i select the "UserName" from the UserId and the LastUser (witch corresponds with an user from the aspnet_Users table) like so: in place to show "80a2c487-9981-412f-b4e4-e0f8acda8cea" i awnt to show the UserName witch corresponds with that code (from UserId and from LastUser witch may be different) here is my stored procedure SELECT Pictures.PictureID, Pictures.UserId, Pictures.Name, Pictures.LastUser, aspnet_Users.UserId, aspnet_Users.UserName FROM aspnet_Users INNER JOIN Pictures ON aspnet_Users.UserId = Pictures.UserIdshould i make another table, the third one, in witch i have 2 rows the PictureId and UserId and make a relationship between them...?But still, how can i take the UserName witch corresponds with LastUser code?please help me, thanks
I am moving the database built in access to Sql 7 and i am unable to find any subsitute of format function of Access in sql. Please help me out ot find a suitable solution of it.
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
I am trying to get the details of an xml. For this I am using below code. I am not able to get the desired output.I want to add 1 more columns AnimalID and DetailID.I want these column to interlink the other values. Below is the desired output.
declare @x xml set @x= '<config> <Animal name="Baboon" ref="Ape"> <detail name="Ape detail" ref="Monkey" typo="animal" required="true">
I have a sql query where i want to find the highest number in a column. I believe i am querying for the data properly, but i think my problem is that i don't know how to retrieve that information in code. The error message i get is this:"Conversion from type 'DBNull' to type 'Integer' is not valid"Here is my code:------------------------------------------------------------- queryString = "SELECT MAX(SortOrder) as HighestNumber " & _ "FROM uQI_Questions WHERE (SurveyID = @SurveyID)"........ more code in between ....... ' Make sure there is 1 and only 1 record returned. If ds.Tables(0).Rows.Count = 1 Then ' retrieve the number and add 1 to it. NextNumber = ds.Tables(0).Rows(0)("HighestNumber") NextNumber += 1 Else ' Else it must be the FIRST record so return '1'. NextNumber = 1 End If-------------------------------------------------------------The line that generates the error is "NextNumber = ds.Tables(0).Rows(0)("HighestNumber")".Any suggestions?
Hi all, in SQL 2000 I can select data from a different server using the sintax "select * from server_name.db_name.owner.table_name". Can i do the same thing with a table function (e.g "select * from server_name.db_name.owner.MyFun_name()")? Is there a way to do it?
CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1)) RETURNS integer as BEGIN DECLARE @PersonID integer set @PersonID=0 if @SSN>0 and @SSN<999999999 Begin select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN End ELSE if @SSN is not null BEGIN IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null Begin select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @NamePrefix and strFirstName=@FirstName and strMiddleName=@MiddleName and strLastName=@LastName and strNameSuffix=@NameSuffix and lngSSN=@SSN End ELSE if @LastName is not null BEGIN select MIN(lngPersonID) as PersonID from Persons where strLastName=@LastName and lngSSN = @SSN END END return (@personID) END
I m having problem with the "Select" function the error I m getting is Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
I have a select statement running within stored procedure as below. What I need to do is format the results of the select into one string by concatanting the below, and removing any null fields by using ISNULL. I am struggling with syntax. Can anyone advise? Should I be using a function? is so any sample syntax or pointers would be great..thanks marco
SELECT address1, address2, address3, address4, address5 FROM customer
I've got a complex UDF I need to call twice in a select statement, as shown below:
SELECT
dbo.myFunction(colName), dbo.myFunction(colName) * 2 FROM
tableName
The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).
Is there any way I can write the query so that dbo.myFunction is only executed once?
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
I am used to working in Access and just recently became somewhate proficient using custom functions in modules. I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access? Thanks for your help. Mike
How to grant users the right to use the DECRYPTBYKEY function to decrypt the data. I have seen some people talk about using a stored procedure or view to surface the decrypted data, but how would you implement that when trying to pull back a single dataset? It would be best to use an inline function to allow the row to be returned decrypted, but opening the keys isn't allowed in the function construct.
So, I know I have to be missing something, but how do you let basic users (db_reader types) decrypt the data they need based on a custom database role? What do I need to give the user permission to?
My setup is simple: - I have my SMK - I have a DMK encrypted by password - I have my self signed certificate - I have my symmetric Key encrypted by the certificate