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.
View 10 RepliesI 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....
I cannot find the problem with this function.
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?
I have two tables. Users and records. I need to select only the users that not has lines recorded in the other table. How could I do that?
Example:
ID| Name| Access 1|Access 2|
----------------------------
1 | Axel | True |False |
2 | Ivan | False |False |
3 | Bob | True |False |
4 | Sue | False |False |
ID| Points| Month| Year|User_1|User_2|
--------------------------------------
1 | 2 | 5 | 2015| 2 | 1 |
2 | 5 | 5 | 2015| 2 | 1 |
3 | 1 | 5 | 2015| 3 | 1 |
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
View 3 Replies View RelatedI 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
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
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
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
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
Anyone have any ideas?
Thanks,
Deb
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
[code]....
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
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.
HowTo: Import data to MS SQL 2008 from password protected Access DB ?
View 2 Replies View RelatedWhat 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
Pls help thanks
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 ...
Thanks in advance
Jay
Hello All -
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.
Suggestions?
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!!!
View 9 Replies View RelatedI'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.
View 2 Replies View Relatedhello,
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
Hi,
What I want to do is include a function in my select:
select myFunc(aTable.aFieldValue), aTable.aFieldValue from aTable
but where myFunc actually returns a Table instead of a scalar. How can I do this please?
for example, I know:
select * from myFunc(123) from aTable
will work fine.
and
select myScalarFunc(aTable.aFieldValue) from aTable
will also work.
But how can i get the 123 value from a table?
Hi,
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.
Thanks
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">
[Code] ....
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?
View 1 Replies View RelatedHi 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?
Thank you all, Andrew
I have a table like this below:
<PRE>
Page Book Release MaxPages
1234ABC A1
1234ABC B2
9999ABC D1
9999ABC E2
7777ABC A1
7777ABC C2
</PRE>
I want to select every page of the book, but only the highest release of that page.
Something Like:
Select Book, Page, Max(Release), MaxPages
From Table
But I can't quiet figure it out.
Thanks for any help.
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.
Thanks
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
Hi,
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?
Thanks for your help,
Andrew
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.
View 1 Replies View RelatedI 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