Binding Datatable to Report

Hi,

I have created a datatable which is filled by the result of stored procedure. So I need to know like how to bind this datatable with the report using report viewer.

Apoorva

First you have to make sure you have a dataset. Also that the data set is connecting to this table.

If this is your first time creating a report I would suggest finding a walk through on a simple report creation

Here is a pretty good wak through

http://www.codeproject.com/dotnet/HowToReport.asp

good luck

Bind Variable in CURSOR

SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.

I have a stored procedure which is not working the way I think it
should be.

I have a CURSOR which has a variable in the WHERE clause:

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

It won't return anything, even when I verify that @.dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:

SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename

DB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-29
09:52:11.823
DtsAdtStdArchive_DataSourceType5175768822006-03-29
09:52:11.8702006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-29
09:52:12.103

I've taken out most of the guts for simplicity, but here's what I've
got:

--CREATE TABLE dbo.db_ind
--(
--db_ind_tkintIDENTITY,
-- id int NULL,
-- tablename sysname NOT NULL,
-- indid int NULL,
-- indexname sysname NOT NULL,
-- shcontig1dt datetime NULL,
-- defragdt datetime NULL,
-- shcontig2dt datetime NULL,
-- reindexdt datetime NULL
--)

ALTER PROCEDURE IDR
(@.hours int
)
AS

--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF

DECLARE @.tabname varchar(100),
@.indname varchar(100),
@.dbname varchar(50),
@.vsql varchar(1000),
@.v_hours varchar(4),
@.shcontig1dtdatetime,
@.shcontig2dtdatetime,
@.defragdtdatetime,
@.reindexdtdatetime,
@.idint,
@.indidint,
@.rundbcursorint,
@.runtabcursorint,
@.runindcursorint

DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @.dbname
AND tablename = @.tabname
ORDER BY indexname

OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

SELECT @.v_hours = CONVERT(varchar,@.hours)

--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====

WHILE @.rundbcursor = 1
BEGIN -- db while

PRINT '============================='
PRINT @.dbname
PRINT '============================='

--================================================== ================================================== =====
--================================================== ================================================== =====

OPEN get_tabs

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @.tabname
SELECT @.runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITS
SELECT @.runtabcursor = 0
end

WHILE @.runtabcursor = 1
BEGIN
PRINT @.dbname
PRINT @.tabname

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN
PRINT 'Index:' + @.dbname + '.' + @.tabname + '.' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END-- 1st loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

--==========
PRINT 'db.tab: ' + @.dbname + '.' + @.tabname

--==========

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN

PRINT 'dbname: ' + @.dbname
PRINT 'tabname: ' + @.tabname
PRINT 'indname: ' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END -- 2nd loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
SELECT @.runtabcursor = 1
ELSE
SELECT @.runtabcursor = 0

END-- loop through tables
CLOSE get_tabs

--================================================== ================================================== =====
--================================================== ================================================== =====

PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
PRINT ''

FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds

--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====

GO

And this is what I'm getting:

=============================
Archive
=============================

(0 row(s) affected)

not getting any tables!
Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]

..
..
..
etc.

Am I missing something obvious?

Thank you for any help you can provide!!One of my fellow emps got it - apparently the CURSOR needed to be
declare w/in the loop right before I opened it.

I moved the get_tabs and get_inds cursor declarations and all is well .
.. .sql

Bind to multiple tables from stored procedure

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

Isthis article of any use?|||no fortunately not :(

Bind many tables from a single sp on many tables on a single repor

Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?

Bind a stored procedure within dataview

As I would like to execute a stored procedure within the Report Creation
Wizard. But only SQL String are allowed.
How can I "put" my stored procedure in this data view ? (I try EXEC
my_stored_proc in the SQL String area but not working...)
I would like to do it because I have almost 60 fields to add one by one into
a table manualy
If one of you have tips or can help me, I will appreciate :)
Have a good day !Are you getting any particular Errors? I've found that some sproc can be
called using exec sprocname @.Param1,@.Parm2 etc., while some do not. I've
resorted to the follwowing:
Putting a select * from table1
Once you get to the data tab, click on the ... and change the Command Type
to stored procedure.
You can then enter you sproc name in the query string box (don't enter the
exec).
If the sproc has parameters you'l need to set those up as well, to feed the
sproc.
"JahPil" wrote:
> As I would like to execute a stored procedure within the Report Creation
> Wizard. But only SQL String are allowed.
> How can I "put" my stored procedure in this data view ? (I try EXEC
> my_stored_proc in the SQL String area but not working...)
> I would like to do it because I have almost 60 fields to add one by one into
> a table manualy
> If one of you have tips or can help me, I will appreciate :)
> Have a good day !

Binary_Checksum - How secure is it?

Hi,
In one of my client's database some confidential information is stored in an
encrypted format and a different column has the original value in a
Binary_Checksum format.
For example, if 'abc' is a password, its first encrypted and put in Column1.
But a Binary_Checksum of 'abc' is stored in Column2 for comparison purposes.
I am just wondering whether this is secure. Can't the Binary_Checksum value
(26435) be reversed to get the original 'abc'?
Thank you.
Regards,
KarthikHi Karthik,
The hashes generated are only 32 bits long, which is tiny. BOL states that
the probability of a collision is higher than that of the MD5 one-way hash
function, which itself is considered insecure at this point. This means a
hacker has a much greater chance of guessing the password with brute force
or dictionary attacks. Consider the following sample run in SQL 2005:
SELECT BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
SELECT BINARY_CHECKSUM('A')
The first thing to notice is the simplicity of the algorithm.
BINARY_CHECKSUM('A') returns 65, which is the ASCII code for.. what else?
'A'. Both of the SELECT statements above produce the same result. Someone
trying to hack this particular system where you use BINARY_CHECKSUM to hash
the password will have a pretty easy time of getting some generated string
to match that hash. I would switch to another hash algorithm like SHA or
something.
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
> Hi,
> In one of my client's database some confidential information is stored in
> an
> encrypted format and a different column has the original value in a
> Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
> Column1.
> But a Binary_Checksum of 'abc' is stored in Column2 for comparison
> purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
> value
> (26435) be reversed to get the original 'abc'?
> Thank you.
> Regards,
> Karthik|||Karthik (Karthik@.discussions.microsoft.com) writes:
> In one of my client's database some confidential information is stored
> in an encrypted format and a different column has the original value in
> a Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
> Column1. But a Binary_Checksum of 'abc' is stored in Column2 for
> comparison purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
> value (26435) be reversed to get the original 'abc'?
I believe the checksum algorithm is not very sophisticated at all, it only
performs some XOR operations. Then again, it's a destroying
transformation. There are many strings that gets the same checksum. So
it's not completely trivial to guess the original text. Unless, of
course, you already have an idea of what it could be.
So it's not certainly not as secure as a real encrypted value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You need to educate them on the definition of "encrypted" versus "encoded".
It's generally accepted that encrypted means some type of key is required to
decrypt the data, whether it's a certificate or a password. Encoded means no
key is required. You're talking about encoded here.
Ray

> In one of my client's database some confidential information is stored in
> an
> encrypted format|||Any checksum is simply Character by Character XOR. This is the same as for
RAID parity, .zip checksums, etc. It is not intended to be used for
encryption.
It is based on the fact that the XOR operator is commutative and transitive:
A XOR B = C = B XOR A,
B XOR C = A = C XOR B, and
C XOR A = B = A XOR C,
Which is why PARITY works.
Encryption works the same way, which is why you can decrypt. However, to
make it more secure, instead of XORing the characters together, the original
values are XORed with a fixed algorithm hash. The method of constructing
this hash is what determines the strength of the algorithm.
The hash for a checksum is 0, which is extremely simple to hack: I just told
you what it was; no big secret.
Sincerely,
Anthony Thomas
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
> Hi,
> In one of my client's database some confidential information is stored in
an
> encrypted format and a different column has the original value in a
> Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
Column1.
> But a Binary_Checksum of 'abc' is stored in Column2 for comparison
purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
value
> (26435) be reversed to get the original 'abc'?
> Thank you.
> Regards,
> Karthik|||Hi Mike, Erland, Ray and Anthony,
Thank you for all the valuable input. I will present these facts to my
client. Hopefully he will agree for a proper hash rather than
binary_checksum()
Thank you!
Regards,
Karthik
"Mike C#" wrote:

> Hi Karthik,
> The hashes generated are only 32 bits long, which is tiny. BOL states tha
t
> the probability of a collision is higher than that of the MD5 one-way hash
> function, which itself is considered insecure at this point. This means a
> hacker has a much greater chance of guessing the password with brute force
> or dictionary attacks. Consider the following sample run in SQL 2005:
> SELECT BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
> SELECT BINARY_CHECKSUM('A')
> The first thing to notice is the simplicity of the algorithm.
> BINARY_CHECKSUM('A') returns 65, which is the ASCII code for.. what else?
> 'A'. Both of the SELECT statements above produce the same result. Someon
e
> trying to hack this particular system where you use BINARY_CHECKSUM to has
h
> the password will have a pretty easy time of getting some generated string
> to match that hash. I would switch to another hash algorithm like SHA or
> something.
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
>
>

Binary parameters to stored procedures?

I have a stored procedure that takes a byte string as an argument:
CREATE PROCEDURE Reporting_TicketSelectGroups
@.publicPart NVARCHAR(400),
@.checkField BINARY(46),
@.langCode VARCHAR(9)
AS
...
I've created a DataSet with the name of the stored proc as as its query
string and with this expression as its parameter value for @.checkField:
=Code.CheckField(Parameters!ticket.Value)
This in turn refers to a function in the Code tab of the Report
Properties property sheet:
Public Function CheckField(ByVal aTicket As String) As Byte()
...
Return Convert.FromBase64String(...)
End Function
The idea is that there is a parameter called ticket and it is split in
to two parts, one part being in binary, and these two parts are then
used as parameters to the various queries used in the report. When I
attempt to preview this report, I get this error message:
An error has occurred during report processing.
Query execution failed for data set 'Groups'.
Implict conversion from data type nvarchar to binary is not allowed.
Use the CONVERT function to run this query.
I'm assuming the last two sentences come from SQL Server and indicate
that my byte[] value is being converted to string on the way -- either
that or I have stuffed it up in some way. Can anyone tell me whether
this approach should work, or is simply not possible to pass binary
parameters from RS?
--
Damian CugleyI wrote:
> I have a stored procedure that takes a byte string as an argument:
> [...] Can anyone tell me whether
> this approach should work, or is simply not possible to pass binary
> parameters from RS?
I gather from the deafening silence that it is possible to pass neither
binary parameters nor other formats like UUIDs.
My workaround was straightforward enough, once I had decided to do it: I
wrote a base64 (RFC 1521) codec in T-SQL so I can pass the data safely
as a character string.

Bigint stored as varchar has issues...

Hi,
I'm importing contact information into SQL Server from Excel using
OPENROWSET. The issue I'm having is with how the phone numbers get stored.
The phone numbers have no extra characters like dashes, so they appear like
9495551212, which is equivelant to 949-555-1212. The phone number is being
imported to a varchar field, which implicitly converts it to a format like
7.70947e+009. The final destination field is intended to hold the data as it
is originally, so it's a bigint datatype.
My first thought was to use CAST or CONVERT. But I get:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Any ideas?Try,
select str(cast('7.70947e+009' as float), 10, 0)
AMB
"Eric" wrote:

> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear lik
e
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Problem #1 7.70947e+009 is not a representation of an integer. It is
expressed with exponentials, so it is a float. You first have to cast it
to a float:
select cast(cast ('7.70947e+009' as float) as bigint)
Problem #2,( and this is the kicker) The result will be:
7709470000
Which is probably not a valid phone number. You are losing some of the
significant digits. Try casting it ot a bigint first, then to a
varhchar(10).
SELECT cast(cast(phonenumber as bigint) as varchar(10))
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
This worked for me.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||That did the trick! Thank you so much...
"Louis Davidson" wrote:

> Problem #1 7.70947e+009 is not a representation of an integer. It is
> expressed with exponentials, so it is a float. You first have to cast it
> to a float:
> select cast(cast ('7.70947e+009' as float) as bigint)
> Problem #2,( and this is the kicker) The result will be:
> 7709470000
> Which is probably not a valid phone number. You are losing some of the
> significant digits. Try casting it ot a bigint first, then to a
> varhchar(10).
> SELECT cast(cast(phonenumber as bigint) as varchar(10))
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
> This worked for me.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
>
>|||You could try adding IMEX=1 to the connection properties to see if that
helps.
select *
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\filename.xls;HDR=YES;IMEX=1;'
,Sheet1$
)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Hi
Make sure that on the Excel spreadsheet, the column is not "general" but
rather a "text" type cell.
General cells are evaluated by the Excel driver and if they meet numeric
criteria, they are passed as numeric to SQL Server. This is an Excel issue.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?

Big query on different servers

Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.
Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:

> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...[vbcol=seagreen]
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
to[vbcol=seagreen]
On eight[vbcol=seagreen]
c0000005[vbcol=seagreen]
Stored[vbcol=seagreen]
plus[vbcol=seagreen]
fails at[vbcol=seagreen]
by[vbcol=seagreen]
same[vbcol=seagreen]
works[vbcol=seagreen]
during[vbcol=seagreen]
|||
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
sql

Big query on different servers

Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
> > Hi.
> > From my dektop PC I started Query Analyser on 12 servers and used it to
> > execute a Stored Procedure (same database structure on all servers). On eight
> > it worked, on four it did not, giving the message:
> >
> > ODBC: Msg 0, Level 19,. State 1
> > SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Connection broken
> >
> > Can someone tell me what is going on and how to get round this? The Stored
> > Procedure is like this:
> >
> > CREATE TABLE... (no problem here)
> > INSERT INTO... several thousand rows generated by reading a million plus
> > rows
> > from a different database on the same server
> > (this sometimes works, sometimes it fails at
> > this point)
> > UPDATE... all the rows from the INSERT, again with data generated by
> > reading a
> > million plus rows from a different database on the same
> > server
> > (if it gets beyond this point it works
> > correctly)
> > UPDATE... as the first update
> > UPDATE... as the first update
> > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> >
> > A valid run takes about 10-15 minutes. The last one I tried failed during
> > the first UPDATE after 26 seconds.
> >
> > TIA,
> >
> > Peter.|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL version and service pack level (select @.@.version)?
> >
> > Check that you are on the latest SP's and possible hotfixes.
> >
> > Regards
> > Mike
> >
> > "PeterHyssett" wrote:
> >
> > > Hi.
> > > From my dektop PC I started Query Analyser on 12 servers and used it
to
> > > execute a Stored Procedure (same database structure on all servers).
On eight
> > > it worked, on four it did not, giving the message:
> > >
> > > ODBC: Msg 0, Level 19,. State 1
> > > SqlDumpExceptionHandler: Process nnn generated fatal exception
c0000005
> > > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> > >
> > > Connection broken
> > >
> > > Can someone tell me what is going on and how to get round this? The
Stored
> > > Procedure is like this:
> > >
> > > CREATE TABLE... (no problem here)
> > > INSERT INTO... several thousand rows generated by reading a million
plus
> > > rows
> > > from a different database on the same server
> > > (this sometimes works, sometimes it
fails at
> > > this point)
> > > UPDATE... all the rows from the INSERT, again with data generated
by
> > > reading a
> > > million plus rows from a different database on the
same
> > > server
> > > (if it gets beyond this point it
works
> > > correctly)
> > > UPDATE... as the first update
> > > UPDATE... as the first update
> > > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> > >
> > > A valid run takes about 10-15 minutes. The last one I tried failed
during
> > > the first UPDATE after 26 seconds.
> > >
> > > TIA,
> > >
> > > Peter.|||> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)

Bidirectionnal replication between SQL 2000 and Sybase ASE

Hello,
For political reasons, I need to havec datas stored both on a SQL Server
2000 and a Sybase ASE v12.
If data changes in SQL, it must be replicated to the Sybase server, and if
data changes in Sybase, it must be propagated to the SQL Server.
Do you know how can I reach my goal in a simple manner ?
Thanks,
Steve
Does Sybase replicate to heterogeneous data sources (like SQL Server) using
stored procs? If so:
The best way to do this is to add a column to the tables you are replicating
called ServerName, or something like this. Then assign this column a default
of @.@.servername (I believe this option is available in Sybase).
Then modify update and delete the procs to do their work if the value of
servename being passed in the proc variable is not equal to the value in the
underlying column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steve B." <steve_beauge@.com.msn.Swap.dotcom.and.msn> wrote in message
news:41fa438e$0$2068$626a14ce@.news.free.fr...
> Hello,
> For political reasons, I need to havec datas stored both on a SQL Server
> 2000 and a Sybase ASE v12.
> If data changes in SQL, it must be replicated to the Sybase server, and if
> data changes in Sybase, it must be propagated to the SQL Server.
> Do you know how can I reach my goal in a simple manner ?
> Thanks,
> Steve
>

Bidirectionnal replication between SQL 2000 and Sybase ASE

Hello,
For political reasons, I need to havec datas stored both on a SQL Server
2000 and a Sybase ASE v12.
If data changes in SQL, it must be replicated to the Sybase server, and if
data changes in Sybase, it must be propagated to the SQL Server.
Do you know how can I reach my goal in a simple manner ?
Thanks,
SteveDoes Sybase replicate to heterogeneous data sources (like SQL Server) using
stored procs? If so:
The best way to do this is to add a column to the tables you are replicating
called ServerName, or something like this. Then assign this column a default
of @.@.servername (I believe this option is available in Sybase).
Then modify update and delete the procs to do their work if the value of
servename being passed in the proc variable is not equal to the value in the
underlying column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steve B." <steve_beauge@.com.msn.Swap.dotcom.and.msn> wrote in message
news:41fa438e$0$2068$626a14ce@.news.free.fr...
> Hello,
> For political reasons, I need to havec datas stored both on a SQL Server
> 2000 and a Sybase ASE v12.
> If data changes in SQL, it must be replicated to the Sybase server, and if
> data changes in Sybase, it must be propagated to the SQL Server.
> Do you know how can I reach my goal in a simple manner ?
> Thanks,
> Steve
>

Bidirectionnal replication between SQL 2000 and Sybase ASE

Hello,
For political reasons, I need to havec datas stored both on a SQL Server
2000 and a Sybase ASE v12.
If data changes in SQL, it must be replicated to the Sybase server, and if
data changes in Sybase, it must be propagated to the SQL Server.
Do you know how can I reach my goal in a simple manner ?
Thanks,
Steve
Does Sybase replicate to heterogeneous data sources (like SQL Server) using
stored procs? If so:
The best way to do this is to add a column to the tables you are replicating
called ServerName, or something like this. Then assign this column a default
of @.@.servername (I believe this option is available in Sybase).
Then modify update and delete the procs to do their work if the value of
servename being passed in the proc variable is not equal to the value in the
underlying column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steve B." <steve_beauge@.com.msn.Swap.dotcom.and.msn> wrote in message
news:41fa438e$0$2068$626a14ce@.news.free.fr...
> Hello,
> For political reasons, I need to havec datas stored both on a SQL Server
> 2000 and a Sybase ASE v12.
> If data changes in SQL, it must be replicated to the Sybase server, and if
> data changes in Sybase, it must be propagated to the SQL Server.
> Do you know how can I reach my goal in a simple manner ?
> Thanks,
> Steve
>

Bidirectionnal replication between SQL 2000 and Sybase ASE

Hello,
For political reasons, I need to havec datas stored both on a SQL Server
2000 and a Sybase ASE v12.
If data changes in SQL, it must be replicated to the Sybase server, and if
data changes in Sybase, it must be propagated to the SQL Server.
Do you know how can I reach my goal in a simple manner ?
Thanks,
SteveDoes Sybase replicate to heterogeneous data sources (like SQL Server) using
stored procs? If so:
The best way to do this is to add a column to the tables you are replicating
called ServerName, or something like this. Then assign this column a default
of @.@.servername (I believe this option is available in Sybase).
Then modify update and delete the procs to do their work if the value of
servename being passed in the proc variable is not equal to the value in the
underlying column.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steve B." <steve_beauge@.com.msn.Swap.dotcom.and.msn> wrote in message
news:41fa438e$0$2068$626a14ce@.news.free.fr...
> Hello,
> For political reasons, I need to havec datas stored both on a SQL Server
> 2000 and a Sybase ASE v12.
> If data changes in SQL, it must be replicated to the Sybase server, and if
> data changes in Sybase, it must be propagated to the SQL Server.
> Do you know how can I reach my goal in a simple manner ?
> Thanks,
> Steve
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?
SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:

> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:

> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:
> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

Between clause...

Hi all, I need write a stored proc with a clause like "between like xxx AND
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:

> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:

> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BETWEEN clause & <= operators

Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.

HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --

where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th

using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura