Bitwise NOT Operation
Sep 6, 2006
I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like
update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @loginame and srvid IS NULL
How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?
Look at the following sample code and result. I just wanted to understand how it works with int and smallint
declare @a int
declare @b smallint
declare @c tinyint
select @a = 1, @b = 1, @c = 1
select @a=~@a, @b= ~@b, @c= ~@c
select @a, @b, @c
Result
~~~~~
-2-2254
Thanks in advance
Gnana
View 3 Replies
ADVERTISEMENT
Nov 20, 2013
I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.
OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.
View 1 Replies
View Related
Nov 27, 2007
Hi,
This is a crosspost, I also asked this question in the learnvisualstudio.net forum. I'm trying to engineer a bit of code in SQL that "decodes" a value stored in a table that represents an array of boolean values, stored using bitwise operations. I have the number, a reference of bit positions for each item, and the actual items checked per the program that normally processes this value. I'm trying to write a sproc that will perform actions based on the booleans in my list, but I can't get the same results the interface gives me.
Here's an example:
Value is 66756
Bit positions 2, 6, 7, and 10 should all return true. I was trying to deduce this using the following:
Code BlockSELECT CONVERT (BIT, <value> & (2 ^ <position>))
but that doesn't work at all. It's my (broken) translation of the code normally used to display this value as a checkbox, which comes from VB6 and looks something like this:
Code Block.ListItems(CStr() & "_").Checked = CBool <value> And (2 ^ <position>))
Can anyone tell me what I'm doing wrong? Thanks in advance.
View 3 Replies
View Related
Dec 4, 2003
I was doing a SUM on my returned rows and i found that what i really want is an aggregate bitwise OR on all the returned rows. Do you know what's the function for that?
Thank you
View 4 Replies
View Related
Jan 30, 2004
Is there a way to do a logical exclusive OR (XOR) in sql server?
I'm trying to do this in where clause, something like:
WHERE
(not exists (select 1 from table a where a.date > '01/30/03') XOR
exists (select 1 from table a where a.date < '01/30/03'))
Thanks!
View 14 Replies
View Related
Jul 23, 2005
I am new to bitwise thing in MSSQL.Let's suppose there's a table of favorite foodsinsert int fav_foods(food_name,bitwiseVal)values('Pasta',1)insert int fav_foods(food_name,bitwiseVal)values('Chicken',2)insert int fav_foods(food_name,bitwiseVal)values('Beef',4)insert int fav_foods(food_name,bitwiseVal)values('Fish',8)insert int fav_foods(food_name,bitwiseVal)values('Pork',16)How do I write query to find people who selected more than one item andselected items from "Pasta, Chicken, Beef, Pork"(but not fish)?I hope my question is not confusing.....
View 6 Replies
View Related
Nov 11, 2004
Hello All
I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.
So I've decided the best way to handle this is to assign a value to each day of the week like so:
MON = 1
TUE = 2
WED = 4
THU = 8
FRI = 16
SAT = 32
SUN = 64
So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.
So the values would be:
@begin_date = '12/01/2004'
@begin_time = '10:00 AM'
@end_date = '02/27/2005'
@end_time = '10:30 AM'
@recur_days = 10
Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things
1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)
2. What is the best way to loop through the date range and create a record for each day?
Regards
Russ
View 2 Replies
View Related
Oct 22, 2014
I am trying to get a culmulative Bitwise OR operation on a column by query - rather than using a CLR function.
The problem is shown below:
CREATE TABLE #Operations (
PK INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserProcess VARCHAR(50) NOT NULL,
ServerOperation VARCHAR(50) NOT NULL,
Permission INT NOT NULL );
[Code] ....
So Far I've tried SUM - wrong results, and STUFF - which doesn't seem appropriate for bitwise operation - neither give useful results.
-- SUM Operation - fails
SELECT DISTINCT
UserName,
ServerOperation,
SUM(Permission) AS Permission
FROM #Operations
[Code] ....
It may be possible to materialise the permissions each time one is changed (e.g. by to use a cursor ), however I would prefer a query or function or combination to generate the result.
View 5 Replies
View Related
Jan 15, 2007
I would like to do something like a SELECT * FROM Files WHERE (Attributes & ?) but the & operator isn't recognized. I looked at the documentation sample for it and in the sample the & operator isn't used in the WHERE clause so I thought that might be the reason but I still can't get the operator to work. So even the doc sample code for it doesn't work.
View 3 Replies
View Related
Feb 25, 2001
HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....
SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA
.5 is where the query bombs. any idea why is this happenning?
by the way, i'm using sql server 7.0.
francis,
View 2 Replies
View Related
Mar 18, 2014
I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).
View 9 Replies
View Related
Feb 6, 2007
Hi,
We are facing a problem while passing a string containing the "&" character into Full Text search CONTAINS predicate. The records that do have this character are not being returned by the search.
I'd like to raise two questions:
1) Is there any special way to escape this character?
2) Does FTS index it at all?
We have tried all known (to us) ways of escaping like doubling the character, using char(38), using ESCAPE etc..Nothing seem to work. Any help would be appreciated.
Thanks,
Alex
View 2 Replies
View Related
Aug 2, 2000
I am running SQL Server 7. I have an user that need to run the Set Identity On a table, and he gets an error message:
Server: Msg 8104. The current user is not the database or object owner of table 'tpartners'. Cannot perform SET operation.
I can grant this user with DBO user role, then he can edit anyting on the database.
Is there a work around that he can run the Set cmd and I don't need to grant him the DBO user role?
View 4 Replies
View Related
Jul 20, 2005
HiI have a database with full recovery model.Every night at 11.00pm performs full backup, and dayly every 3 hoursperforms differential backup, and trasnactional log backup performsevery 15 minutes during a day.I am interested what operations i can not recover from log ordifferential backup.I saw that when i drop table i can recover it from log backup, but isthere some operation that can not be recoverThanks a lotAlex*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Apr 6, 2007
Dear friends, I was not able to find any perfect toolbox item to use "not in" operation on my selected columns, also how can we use multiple join conditions in the merge join item? Thanks,
View 1 Replies
View Related
Jan 18, 2008
Why do I get the message "Operation can't be completed?" when I try to save a stored procedure!!! When I create a new stored procedure and copy the code into it it works fine!
View 2 Replies
View Related
Jan 18, 2003
I have a sp that creates a #temp table and performs a bulk insert.
CREATE TABLE #template (template varchar(8000))
EXEC ('bulk INSERT #template FROM "' + @filename + '"')
SET @html = (SELECT template FROM #template)
DROP TABLE #template
...
When I access this sp with any other user than 'sa' I get this error message:
The current user is not the database or object owner of table '#template'. Cannot perform SET operation.
I've been everywhere (with no success) in the sql srv admin to look for the appropriate check box to allow another named user to access this sp.
Can anyone help?
View 2 Replies
View Related
Jun 4, 2001
On SQL 2k, How can I set my DTS package, which imports a 3 million row .TXT file to a table, as a NON-LOGGED operation?
(LOGGING is taking tooooo long!! I will be sure to run a full DB backup when the job completes.)
thx-
BT
View 1 Replies
View Related
Jun 19, 2006
If tables are in relation (foreign key), then how should one proceed for deletion of a record from a master table?
how will the database itself tackle the problem? What should be done from the front end for this?
please help.......I urgently require a solution for this.
View 1 Replies
View Related
Feb 18, 2004
hello,
I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.
Salik.
View 6 Replies
View Related
Apr 14, 2008
IDENTITY_INSERT is already ON for table 'Elbalazo.dbo.DeliveryOption'. Cannot perform SET operation for table 'City'
Here's my entire script:
--------------------------------------------------------------------
/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'
/*Enable Constraints & Triggers again*/
--exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
-- City
SET IDENTITY_INSERT City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT City OFF
-- State
SET IDENTITY_INSERT [State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT [State] OFF
-- NumberOfPeopleOption
SET IDENTITY_INSERT NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT NumberOfPeopleOption OFF
-- DeliveryOption
SET IDENTITY_INSERT DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionID], [DeliveryOptionName],1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT DeliveryOption OFF
-- User
SET IDENTITY_INSERT [User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])
SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT [User] OFF
-- EntreeOption
SET IDENTITY_INSERT EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT EntreeOption OFF
-- CateringOrder
SET IDENTITY_INSERT CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT CateringOrder OFF
-- CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem OFF
select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]
View 15 Replies
View Related
Dec 14, 2007
Can anybody explain whether Delete and Truncate are Logged Operations?
On some site, I came accross Truncate is not Logged Operation, on some site, Truncate is Logged Operation.
Can anybody explain, what exactly it is?
Thanks in advance,
Mahesh
View 2 Replies
View Related
Mar 25, 2008
A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?
I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.
View 7 Replies
View Related
May 3, 2006
set rowcount 1select Idnum1,Idnum3 from mytable order by Idnum1 DESC, idnum3 ASCis equivelent toselect first(Idnum1),first(idnum3) from mytable order by first(Idnum1)DESC ,first(Idnum3) ASCIsn't that silly? why did Microsoft not add an agregate function Firstfor the Microsoft SQL Server to be consistent with its Microsoft Accessproduct eh?
View 3 Replies
View Related
Mar 26, 2008
i have FTP task in my SSIS package.
when executing FTP task,what i want each time i want to store copied file with new FileName(like timestamp attachto file name)
how i can do this?
Thanks,
Chetan S. Raut.
View 7 Replies
View Related
Mar 21, 2007
What I am looking to do is have a stored procedure begin a dialog with my request service.
With that dialog established my stored procedure sends 50 request messages, one for each of the 50 of the United States. I want these to be processed asynchronously by a procedure that is called on activation for the request queue. In that activation procedure the request is processed against the respective state and a response message is sent to the response service (to the response queue). I want to be able to tie these request messages and response messages together with some type of shared identifier. These requests don't need to be processed in any specific order and don't need any fancy locking mechanism via conversation group since these requests require to be processed asynchronously. What is the best approach? Do I need to create 50 seperate queues and open dialogs with each? If this is the route to take, would this be a performance hit?
My goal is to have all 50 states process all at once, each finishing with a response message sent to the response queue. The initiating procedure, after sending these 50 requests, would then spin and wait for all 50 to complete, be it a response, error, or timeout. When all 50 have returned, the procedure would then merge the results and return. So as you can see in my scenario, I dont care when a state is complete as they do not affect the outcome, nor do they access any of the same resources when being processed.
View 3 Replies
View Related
Mar 21, 2007
Hi I just got a paging procedure from the internet and it’s working just fine, but I would like to return the numbers of pages too. So I used the Count() function to retrieve all records on the table and divided by the page size, but even I doing with float number, this is returning a integer number. I just started with SQL Server, does some knows what is wrong with the following code? or knows a better way to do it.
Declare @Records int,
@Pages float,
@hey int
Set @Records = (Select Count(*) as 'Friends' From friends
Set @Pages = ((@Records) / (@PageSize))
Set @hey = (@Pages)
if @Pages > @hey
begin
@Pages = @Pages + 1
end
return @Pages
Thank you very much.
View 4 Replies
View Related
Mar 24, 2008
Hi to all,
Im using SQL Server 2000.
If i divide two integer column values, the result i am getting is integer. i.e
Update tblStudents SET Percentage=(AttendedClasses/TotalClasses);
AttendedClasses,TotalClasses are two column names(Integer). I hav declared Percentage column as float.
The result im getting is integer value..
I want the actual float value(i.e Percentage)
How to Resolve this??
Help me with this issue..
thanks in advance.
View 1 Replies
View Related
Apr 30, 2006
Hello, I have my table Produits :
CREATE TABLE [dbo].[Produit] ( [Produit_ID] [int] IDENTITY (1, 1) NOT NULL , [Reference] [nvarchar] (50) COLLATE French_CI_AS NULL , [Designation] [nvarchar] (50) COLLATE French_CI_AS NULL , [Quantite] [int] NULL , [PrixU] [sql_variant] NULL , [MontantHT] [sql_variant] NULL , [TVA] [sql_variant] NULL , [Facture_ID] [int] NOT NULL )GOhere is stored procedure CREATE PROC spBaseTVA_Bis
(
@Facture_ID int
)
AS
SELECT SUM(MontantHT) AS montantHT, TVA
FROM
Produit
GROUP BY TVA, Facture_ID
HAVING ( Facture_ID = @Facture_ID)
GO
My stored procedure fill data in a datagrid with to column TauxTVA and TVA like this : TauxTVA TVA But I would like to add a theard column as (the value of the fisrt colum) * ( the value of the second colum ) I would like to desplay data like this : TauxTVA TVA Prod X Y X*Y How can I modify my stored procedure to perform this ? Regards
View 3 Replies
View Related
Aug 31, 2004
Hi,
I am having trouble trying to import a big file (aprox 250Mb is size) into an SQL Server database and I keep getting the message:
"Not enough storage is available to complete this operation".
The application tries to import the file by executing a stored procedure:
CREATE PROCEDURE sp_updateMaterialBlob
@MaterialId Int,
@BLOB image
AS
BEGIN
Update Material SET blob = @blob where id = @materialId
END
The application uses an ADO connection. I've tried increasing the memory of the client machine but that didn't work. Whenever I do run the import, nearly all the memory on the machine is used up but every time after several hours I get the same error message. What is the cause of the problem and how do I resolve it? Ideally I want to use my application to do the import rather than anything bespoke.
View 7 Replies
View Related
Jul 21, 2006
Hi there,
I am trying to update one table on server A/database A with new records from a server B/database B.
Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.
Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?
Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations...
Anybody else got the same problem ?
Thx in advance
dajm
View 2 Replies
View Related
Jun 17, 2014
Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.
View 2 Replies
View Related
Mar 29, 2008
Hi,
I have got a column with the string "I too Love Sql Server". I am trying an output as shown below:
I want to divide this into 4 columns :
Column1 Column2 Column3 Column4
I too Love Sql Server I.t.L.S.S
The Column 4 contains the first letter of each word. If the string contains only 2 words, Eg: "Sql Server" then Column 2 will be empty and only Column 1,3 and 4 will be filled.
How do I do this?
View 2 Replies
View Related