Hi,
am after a book or two on TSQL and Stored Procedures, any
recommendations would be much appreciated.
CheersThe Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
Professional SQL Server 2000 Programming
by Robert Vieira
http://www.amazon.com/exec/obidos/t...=glance&s=books
Inside Microsoft SQL Server 2000
by Kalen Delaney
http://www.amazon.com/exec/obidos/t...=glance&s=books
Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
http://www.amazon.com/exec/obidos/t...=glance&s=books
Microsoft SQL Server 2000 Bible
by Paul Nielsen
http://www.amazon.com/exec/obidos/t...=gla
nce
For absolute beginners, also consider:
Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days
by Richard Waymire
http://www.amazon.com/exec/obidos/t...e.blogspot.com/|||Many thanks,
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135176704.126957.11030@.o13g2000cwo.googlegroups.com...
> The Guru's Guide to Transact-SQL
> by Ken Henderson
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Professional SQL Server 2000 Programming
> by Robert Vieira
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Inside Microsoft SQL Server 2000
> by Kalen Delaney
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Microsoft SQL Server 2000 Unleashed (2nd Edition)
> by Ray Rankins, Paul Jensen, Paul Bertucci
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Microsoft SQL Server 2000 Bible
> by Paul Nielsen
> http://www.amazon.com/exec/obidos/t...=g
lance
> For absolute beginners, also consider:
> Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days
> by Richard Waymire
> http://www.amazon.com/exec/obidos/t...e.blogspot.com/
>sql
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Thursday, March 22, 2012
Saturday, February 25, 2012
BLOBs and Stored Procedures
I have been told that using BLOBS and Stored Procedures is a bad thing.
Running the SQL in the page is the only correct way. We are using SQL Serve
r
2000 - soon to go to 2005. Could someone direct me to documentation that
addresses this situation?
--
Tonywho told you this?
BLOBS I can see why people would advise you to avoid them, they aren't
bad things, they just have the capability to be used badly.
Stored procedures are very good things, by "running the SQL in the page
is the only correct way" do you mean passing SQL as a string to your
sqlCommand objects? is this just for when you are using BLOBS or all
SQL you run?|||some of the key benefits of stored procedures are mentioned in this
article:
http://msdn.microsoft.com/library/d...>
_07_31vb.asp|||Will,
Thanks for getting back to me. I have used Stored Procedures for years.
No, my questions is specific to BLOBS and stored procedures. (I have heard
all the negatives about BLOBS - pdfs in databases, but I have a client ...)
My DBAs tell me that 'adding' a layer to the data process - for BLOBs only-
is too high a price to pay (resources) for me to use them.
I can't find definitive proof one way or the other. I would really like to
continue to use my data layer and not use in-page SQL (ADO .Net to SQL Serve
r
with no SP). I can't argue to vehemently because I don't know if a query
plan is even generated for the BLOB handling SP.
With over twelve years of experience with SQL Server, I have never seen a
situation where performance was better without a stored procedure. But SQL
has to handle BLOBs differently, so I was hoping to find something to suppor
t
either side of the argument that I could take to my DBAs.
Thanks for your time.
--
Tony
"Will" wrote:
> some of the key benefits of stored procedures are mentioned in this
> article:
> http://msdn.microsoft.com/library/d...
es_07_31vb.asp
>
Running the SQL in the page is the only correct way. We are using SQL Serve
r
2000 - soon to go to 2005. Could someone direct me to documentation that
addresses this situation?
--
Tonywho told you this?
BLOBS I can see why people would advise you to avoid them, they aren't
bad things, they just have the capability to be used badly.
Stored procedures are very good things, by "running the SQL in the page
is the only correct way" do you mean passing SQL as a string to your
sqlCommand objects? is this just for when you are using BLOBS or all
SQL you run?|||some of the key benefits of stored procedures are mentioned in this
article:
http://msdn.microsoft.com/library/d...>
_07_31vb.asp|||Will,
Thanks for getting back to me. I have used Stored Procedures for years.
No, my questions is specific to BLOBS and stored procedures. (I have heard
all the negatives about BLOBS - pdfs in databases, but I have a client ...)
My DBAs tell me that 'adding' a layer to the data process - for BLOBs only-
is too high a price to pay (resources) for me to use them.
I can't find definitive proof one way or the other. I would really like to
continue to use my data layer and not use in-page SQL (ADO .Net to SQL Serve
r
with no SP). I can't argue to vehemently because I don't know if a query
plan is even generated for the BLOB handling SP.
With over twelve years of experience with SQL Server, I have never seen a
situation where performance was better without a stored procedure. But SQL
has to handle BLOBs differently, so I was hoping to find something to suppor
t
either side of the argument that I could take to my DBAs.
Thanks for your time.
--
Tony
"Will" wrote:
> some of the key benefits of stored procedures are mentioned in this
> article:
> http://msdn.microsoft.com/library/d...
es_07_31vb.asp
>
Blob as text
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) 
,convert(int,convert(binary(4),img)) [img]
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?
Blob as text
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) ,convert(int,convert(binary(4),img)) [img]
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?
Sunday, February 12, 2012
Bizarre Stored Procedure Behavior
I have dozens of stored procedures, but one of them is suddenly behaving badly.
Basically, if the sp is run as a query (full text), it runs fine and finishes successfully, but if 'executed' (Exec [sp_name]) it fails, saying that one of the referenced tables doesn't exist. The really weird thing is that the table is created earlier in the same procedure and is recognized enough to allow an index to be created on it. But when it comes to an Update statement, forget it. The sp dies saying the object (table) is unrecognized. I have dropped and recreated it, tried everything I can think of.
Has anyone else experienced this? Any ideas what may be causing it?
I'm stumped.
Thanks!you do have the correct names on your command object of the update statement?
Bitwise NOT Operation
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 -2 254
Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...
Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)
Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE
Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.
Betcha that the tinyint data type does not have a sign bit.
yep...that's it...from BOL:tinyint
Integer data from 0 through 255. Storage size is 1 byte.
So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).
In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).
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 -2 254
Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...
Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)
Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE
Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.
Betcha that the tinyint data type does not have a sign bit.
yep...that's it...from BOL:tinyint
Integer data from 0 through 255. Storage size is 1 byte.
So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).
In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).
Subscribe to:
Posts (Atom)