It would need to accept 'OR', 'AND' conditions.
Would it be a good idea to maybe create stored procedures and call these somehow?
Thank you in advance.
Michael O'Connorselect * from table where boolfield = 1 or boolfield = 0 and boolfield = 1
heh funny. anyways, what exactly do you mean by boolean search? Can you be more specific?sql
I have a 3 simple packages get called from master package. First package populates time dimension (stored proc), second one populates transaction type dimension(stored proc) and the final one populates date dimension.
I set the TransactionOption = Required in the Master package, every thing else (package & component) set TransactionOption = Supported.
I have been testing transactions, basically I made time and transaction type dimension to fail and the package did roll back. I am stuck in the date dimension.
Within Date dimension I got a stored procedure which populates calendar date attributes into Wrk_Date table. Then I have a data flow task which reads from thats working table, but it cant access it. I tried running SP_WHO2 command, and the status is SUSPENDED, being blocked by id -2.
I saw someone had similar problem and I did read Ash's comments. I did try to change the isolation level, didnt help.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74372&SiteID=1
Any thoughts please?
Thanks
Sutha
Sutha,
I had enormous problems with this and eventually just dropped the use of distributed transactions altogether. In some cases it simply wouldn't work because of the way DTC holds onto resources. Since then I have used checkpoint files and it works just fine.
-Jamie
|||Jamie
Thanks for the infor, but it is annoying. Do you know this is going to be fixed at any point ?
Client is not happy having a checkpoint. They want to rollback the whole dimension load. For example if we have got 6 dimensions and 5 of them loaded fine and 6th one failed, then they want to rollback the whole lot. What would be the solution in that scenario please, if we cant use transactionoption correctly?
Thanks
Sutha
|||I don't think its a case of it being fixed - its just "the way it is". DTC isn't doing anything wrong - its doing exactly what you've told it to i.e. Maintaining a lock on a table because of a running transaction.
Its a difficult one to broach with the client, I can see that. Maybe you could restructure your package so that the locks don't occur.
-Jamie
|||Jamie
Thanks. I was going through step by step and identified the reason for locks. During step 2, if date table exist in stage database it TRUNCATES the table else it creates the table. Truncate statement seems to hold the lock and it is not releasing it further down the line. I have managed to get the rollback to work after removing truncate.
Thanks
Sutha
Hi,
I have a conversion application which convertts an access database to an sql server(different versions). I'm using stored procedures. The thins is that I export the OLe Object form access to SQL varbinary. what I do is to convert the binary data from the OLE Object to string using ToBase64String. The thing is that when I execute the SQL statement I get the following error:
Error:Operand type clash: text is incompatible with varbinary.
Can anyone tell me what I do wrong and how can I fix this? Thanks.
Can you post the SQL Statement that is being sent? At the very least print the text out and post it here. In the statement the value should look something like:
'010101'
and not like:
0x010101
If not, you might want to go to the language specific forum to ask there by posting the code you are trying to use.
|||the value is something like:
'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
|||That is not binary, binary would be something like this 0x53516C20536572766572
what you posted is some (.NET) encrypted value that is stored in a column
The person/program who encrypted that is the person who will be able to decrypt that value also
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||well if you'd read the hole post you 'd notice that I've applied the ToBase64String function to the binary data.
|||That value looks okay enough. What about the query that is doing the entering? Can you capture that with profiler?
If this is a varchar or text column, that value should work just fine. For example:
CREATE TABLE testText
(
textValue text --use varchar(max) if this is SQL Server 2005
)
go
INSERT INTO testText
SELECT 'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
go
SELECT *
FROM testText
textValue
-
DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==
So there is something else going on...
|||Well as I've said I'm using SQL statements stored in a file. The statement is something like:
CREATE TABLE testBin
(
textValue varbinary
)
go
INSERT INTO testBin('DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==')
go
Try this
CREATE TABLE testBin
(
textValue varbinary(5000)
)
go
INSERT INTO testBin VALUES(convert(varbinary(5000),'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='))
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||You are trying to convert a string literal that is not in hexadecimal format to varbinary implicitly and this will not work. You need to use either text/ntext in SQL Server 2000 or varchar(max)/nvarchar(max) in SQL Server 2005 for the destination column. Or you can cast the value from one type to another which hurts performance. See BOL topic below on how to specify values for various data types:
http://msdn2.microsoft.com/en-us/ms179899(SQL.90).aspx
And also the CAST topic that has a table showing various conversions possible (implicit/explicit).
http://msdn2.microsoft.com/en-us/ms187928(SQL.90).aspx
|||well do you have a better solution for importing blob data using stored sql statements? And this should work with sql 2000 and 2005 also.|||Would this help.create table tmp(i int identity primary key,img image default '0x0')
insert tmp(img) values(default)
go
create proc usp
@.i int,
@.img image
as
declare @.ptr binary(16)
select @.ptr=textptr(img)
from tmp
where i=@.i
writetext tmp.img @.ptr @.img
go
declare @.b varbinary(8000)
set @.b=0x0000007B
exec usp 1,@.b
select *,convert(int,@.b) 
from tmp
go
drop proc usp
drop table tmp|||
Thanks for your reply! But it doesn't help; because I want to do everything with SQL statements. Meaning I want to export the Access database to a file which contains SQL statements, which can be run on using an SQL command interpreter like osql.exe or my own SQL command interpreter. So the blob has to be in the SQL statement stored in the SQL file.
Any idea?
Hi,
I have a conversion application which convertts an access database to an sql server(different versions). I'm using stored procedures. The thins is that I export the OLe Object form access to SQL varbinary. what I do is to convert the binary data from the OLE Object to string using ToBase64String. The thing is that when I execute the SQL statement I get the following error:
Error:Operand type clash: text is incompatible with varbinary.
Can anyone tell me what I do wrong and how can I fix this? Thanks.
Can you post the SQL Statement that is being sent? At the very least print the text out and post it here. In the statement the value should look something like:
'010101'
and not like:
0x010101
If not, you might want to go to the language specific forum to ask there by posting the code you are trying to use.
|||the value is something like:
'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
|||That is not binary, binary would be something like this 0x53516C20536572766572
what you posted is some (.NET) encrypted value that is stored in a column
The person/program who encrypted that is the person who will be able to decrypt that value also
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||well if you'd read the hole post you 'd notice that I've applied the ToBase64String function to the binary data.|||That value looks okay enough. What about the query that is doing the entering? Can you capture that with profiler?
If this is a varchar or text column, that value should work just fine. For example:
CREATE TABLE testText
(
textValue text --use varchar(max) if this is SQL Server 2005
)
go
INSERT INTO testText
SELECT 'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
go
SELECT *
FROM testText
textValue
-
DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==
So there is something else going on...
|||Well as I've said I'm using SQL statements stored in a file. The statement is something like:
CREATE TABLE testBin
(
textValue varbinary
)
go
INSERT INTO testBin('DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==')
go
Try this
CREATE TABLE testBin
(
textValue varbinary(5000)
)
go
INSERT INTO testBin VALUES(convert(varbinary(5000),'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='))
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||You are trying to convert a string literal that is not in hexadecimal format to varbinary implicitly and this will not work. You need to use either text/ntext in SQL Server 2000 or varchar(max)/nvarchar(max) in SQL Server 2005 for the destination column. Or you can cast the value from one type to another which hurts performance. See BOL topic below on how to specify values for various data types:
http://msdn2.microsoft.com/en-us/ms179899(SQL.90).aspx
And also the CAST topic that has a table showing various conversions possible (implicit/explicit).
http://msdn2.microsoft.com/en-us/ms187928(SQL.90).aspx
|||well do you have a better solution for importing blob data using stored sql statements? And this should work with sql 2000 and 2005 also.|||Would this help.create table tmp(i int identity primary key,img image default '0x0')
insert tmp(img) values(default)
go
create proc usp
@.i int,
@.img image
as
declare @.ptr binary(16)
select @.ptr=textptr(img)
from tmp
where i=@.i
writetext tmp.img @.ptr @.img
go
declare @.b varbinary(8000)
set @.b=0x0000007B
exec usp 1,@.b
select *,convert(int,@.b)
from tmp
go
drop proc usp
drop table tmp|||
Thanks for your reply! But it doesn't help; because I want to do everything with SQL statements. Meaning I want to export the Access database to a file which contains SQL statements, which can be run on using an SQL command interpreter like osql.exe or my own SQL command interpreter. So the blob has to be in the SQL statement stored in the SQL file.
Any idea?
