Augent Pls Help::::Is_Member() Function Returns 0 Instead Of 1 In Sql2005
Aug 10, 2007
Hello All,
I have a situation where after creating roles and addding users to the roles then when i call Is_Member('MyRole') on my development box it tells me the user is a member of the Role,
But when i detach the database and deploy on a production server and call Is_Member('MyRole') it tells me that the user is not a member of the Role....
I went on to call the system stored procedures, sp_helprolemember to actualy determine if the user is in the 'MyRole' role and the procedure returned a list which i confirmed that the user actually exists in that role..
So am pretty confused when i call Is_Member('MyRole') and it gives me 0 meaning the user is not in the role....
Plss i need help on this thanks a million
KNOW THY SELF
View 8 Replies
ADVERTISEMENT
Sep 5, 2006
Hello,
Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter??
View 4 Replies
View Related
Oct 21, 2005
Hi, How to write a SQL function that returns a string that contains xml string from "SELECT ....FOR XML"In other word, I want to put result of select .. for xml into a variable.Thanks
View 3 Replies
View Related
Jul 20, 2005
Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???
View 2 Replies
View Related
Feb 4, 2014
I have following select-statement:
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
View 2 Replies
View Related
Mar 3, 2008
Dear all hi,
I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column.
I know that If I have the table name I can find the column width through the system tables. I don€™t want this.
Sincerely,
Hellen
View 10 Replies
View Related
Dec 12, 2007
Hello!
Is there a function that gets the name of a column and a string as arguments and returns the position of this string in the column given?
Thank you in advance.
View 1 Replies
View Related
Aug 14, 2014
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
View 0 Replies
View Related
Oct 15, 2014
Any UDF that accepts a Month and Year and returns Start Date and End Date.
For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.
View 8 Replies
View Related
Sep 7, 2006
I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task
create a function called CleanString (ByVal CustomerName as String) As String
CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd
ie parse 'Mr John Tools' and the function returns 'Tools'
ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'
ie parse 'Microsoft Corp' return 'Microsoft Corp'
ie parse 'Digidesign Ltd' return 'Digidesign Ltd'
Any idea of a regular expression or existing piece of existing code I can have
thanks in advance
dave
View 3 Replies
View Related
Apr 21, 2015
We run std 2008 r2. I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested.Â
But when he selects * he gets a single row.
I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it. Â
View 8 Replies
View Related
Apr 28, 2015
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
      RETURNS date
AS
BEGIN;
 DECLARE @Result date;
 SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
   RETURN @Result;
END;
View 11 Replies
View Related
Apr 13, 2007
Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
View 3 Replies
View Related
Apr 30, 2001
Hi everyone,
I've got a problem using the function is_member()...
Here's the background story:
I'm a member of an NT group: NTGroup1Me
I HAVE TO be a member of this group, not managed by the company i'm in!
I developped an application accessing to a SQL Server database, and the access is managed with others NT Groups (ie: if i'm a member of NTGroup2 then i can access to modify, if NTGroup3, then i can access to read only...)
So i'm in NTGroup1, and i'm also in NTGroup2, as "NTGroup1Me", not as "Me"
I'd like to use is_member to verify that i'm in NTGroup2, but is_member returns me '0' (it should be '1' !!)... which login does the is_member() function verify ?
- "Me"
- "NTGroup1Me"?
- ... ?
I hope it's understandable, because it's not easy to explain... And English is not my mothertongue :))
If someone can help me ...
View 1 Replies
View Related
Apr 16, 2002
IS_MEMBER returns status on membership in a NT group or db role. My problem is , it returns 0 (No) if I use IS_MEMBER on a role, and the user is member of a NT Group which again is member of the role. Example: NT User = UK_User, this account is member of NT Group UK_Group. In SQL Server, only the NT Group (UK_Group) is entered as login / user. And in SQL Server, UK_Group is member of the role UK Role. When UK_User calls IS_MEMBER ('UK Role'), the answer is 0 (No). I would like it to be 1 (Yes) ! Any suggestions how to do this (we do not want to enter all NT Users as logins in SQL server, only the NT Groups).
brgds,
Odd Oren
View 1 Replies
View Related
May 16, 2006
Hi,
IS_Member function Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role. If I want to test this for different user other than the current user, How I can do this?
Thanks
View 6 Replies
View Related
Dec 11, 2006
Hi All -
I am running sql2005 on windows 2003 all with uptodate SPs
Im sure this has been discussed somewhere, but I just dont understand why its behaving this way.
my issue is that if a 'db_owner' is a member of another db role then the is_member('some_other_role') always returns 0
example:
dbrole: testrole
ntuser: joeuser
ntgroup: testgroup
ntgroup: grpsysadmins
joeuser is a domain login.
testgroup and grpsysadmins are both nt groups.
joeuser is a member of both testgroup and grpsysadmins
grpsysadmins is a member of administrators grp on the sql server machine
testrole is a sql role
testgroup is a login into sql
testgroup is a member of testrole
1) when I do select is_member('db_owner') I get 1
2) when I do select is_member('testrole') I get 0 <== this is the wierd one
3) when I do select is_member('machinename estgroup') I get 1
I am expecting all 3 to return 1. now if I am logged as a user that is not sysadmin then select is_member('testrole') returns 1.
this behavior seems to be by design, but for the life of me i have no idea why?
View 7 Replies
View Related
Mar 20, 2007
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.
This is how I retrieve the longitude:
<asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>"
SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)">
</asp:SqlDataSource>
I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:
var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13);
However, lat & long do not contain the retrieved result but rather a useless System.something string.
How do I assign the retrieved results to these variables and port them over to Javascript as required?
Many thanks!
View 14 Replies
View Related
Mar 1, 2007
I am not sure why this is not working it clearly states in the MSDN that it should "
'role'
Is the name of the SQL Server role being checked. role is sysname and can include the database fixed roles or user-defined roles but not server roles.
"
I have seen many questions revolving around this issue on this site and on the net about this but know one can answer it
I have created a new user defined database role called testrole with any owner
then created a new sql login and user (Sql Authentication)
add the user to the database role testrole
check IS_MEMBER and it returns 0
try this with a fixed database role and I get the desired result of 1
this is simple and should not be such a problem for every one
unless I am doing something wrong
Please help
Thanks
View 3 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
Sep 19, 2007
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
Can anybody please advise?
View 1 Replies
View Related
Jul 8, 2007
Hello,
I have a vb program that include a dts package that has been saved to vb with sql2000 dts wizard and works very good.
Now that I upgrade my website to sql2005, this vb dts package doesn't work.
The error I get is:
Microsoft Data Transformation Services (DTS) Package
Invalid STDGMEDIUM structure
(Microsoft Data Transformation Services (DTS) Package (80040066): Invalid STDGMEDIUM structure
) (Microsoft SQL-DMO (ODBC SQLState: 42000) (80004005): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.)
I searched in the internet how to make dts package in sql2005 and save it to vb and found no information about it.
What Can I do to get the vb code of the dts package I create in sql2005 or how do i migrage the sql2000 vb dts package code to sql2005?
Thanks,
Kubyustus
View 4 Replies
View Related
Jul 23, 2005
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies
View Related
Nov 13, 2006
I have some production boxes on Win 2000 32-bits OS and some production servers have been upgraded to Win2003 64-bit OS runing SQL Server 2005. There are also a number of Win2003 32-bit OS running SQL 2005.
The issue is that when linking the 64-bit production servers to the 32-bit boxes running SQL 2005 / Win 2003 OS, the linking seems to succeed, but I am unable to see a number of entries in sys.objects. Typically, these objects are User Stored Procedures.
Moreover, the linking seemed to have worked, but data extraction does not take place between the servers. However, there are no errors. The objects (user stored procs) exist on the 64-bit side, but linking does not actually happen.
Microsoft KB has addressed this in SQL 2000 case in this KB article, but has not suggested a solution for SQL 2005.
Any ideas? Has anyone else encountered this?
Thanks.
View 1 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
Dec 16, 2004
CREATE PROCEDURE UpdateAccount
(
@AccountID int,
@EmailAddress varchar(255),
@Password varchar(16)
)
AS
DECLARE @actualPassword varchar(16)
SELECT @actualPassword = Password
FROM Account WHERE AccountID = @AccountID
If Exists( SELECT EmailAddress FROM Account
WHERE EmailAddress = @EmailAddress )
RETURN -1
ELSE IF @Password <> @actualPassword
RETURN - 2
ELSE
UPDATE Account
SET
EmailAddress = @EmailAddress,
Password = @Password
WHERE AccountID = @AccountID
GO
View 2 Replies
View Related
Aug 11, 2006
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned
DECLARE @memberList VARCHAR(4000),@hDoc INT
SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT)
EXEC sp_xml_removedocument @hDoc
View 1 Replies
View Related
Feb 26, 2007
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
View 1 Replies
View Related
May 4, 2008
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
View 9 Replies
View Related
