Books Online

Does anyone know how to install Sql Server 2k Books
Online to say my laptop? I insert the CD but it doesn't
show anything about that.
Thanks in advance,
Darryl
You can download Books Online directly from Microsoft SQL Server website:
http://www.microsoft.com/sql/techinf...2000/books.asp
From the CD, choose custom installation and look into the various components
you can install...you will see BOL in there.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:890101c432ac$918ae860$a401280a@.phx.gbl...
Does anyone know how to install Sql Server 2k Books
Online to say my laptop? I insert the CD but it doesn't
show anything about that.
Thanks in advance,
Darryl

Blocking When doing bcp

Any ideas?

I execute a sproc from QA.
It interogates a folder:

Select @.Command_String = 'Dir ' + @.FilePath + '\*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @.Command_String
[/code[

Which I then Parse and get the details...

Set up a CURSOR (Booo hiss...)

[code]

DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]


WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN

Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)

SET @.cmd = 'bcp ' + @.db_name + '..' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + ' in '
+ @.FilePathAndName
+ ' -f' + 'd:\Data\Tax\' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + '.fmt'
+ ' -S' + @.@.servername + ' -U -P'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + '''' + ', NO_OUTPUT'

INSERT INTO #XLAT_BCP_Results(Col1) Exec(@.Command_String)


MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH

A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time...

I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

When I do sp_who, it shows the blocking...

I'm so confused...Can you get the table/index that has the blocking lock on it?|||Well the blocking spid I found in sp_who, is the QA window I lauched the sproc from...the blocked spid is the idependent bcp thread (well not so independent)..

There's no RI between these code tables, and just a trigger...that moves the rows to history table...

I'll check sp_locks...|||Every single lock on the "parent" spid has a status of "grant" and a type of IX...|||I am pretty sure there wasa way of telling a blocking lock from a regular lock. I will have to do a bit of checking, but this job thing has me a bit tied up.|||In the output of sp_lock, look for a status of "WAIT". This is the process that is attempting to get at the locked resource, and of course, must wait for the first process to get done with it. Alternatively, you can check the waitresource column in the sysprocesses table. This will also tell you what the processes that is blocked is waiting on.
This will give you the name of the table/index that you are getting hung up on. After this, I am afraid it is going to be a slog through the code to find the actual stumbling block.
Let us know how it comes out.|||Thanks...

Been there...

It seems that even though I expected lines inside of a sproc to be serial, it looks like the delete doesn't complete before the sproc continues to the nect line of code, which is the xp_cmdshell bcp...

I find this totally unbelievable, but in every instance, the independent xpshell thread (which is executed with a different login) is the one that is being blocked. And it is being blocked by the spid that executed the sproc...

With me so far...

I then placed the COMMIT immediatley after the DELETE, and viola!

No more problems...now tell me...are the lines in a sproc serial?

I always thought they were, and I'll be damned if I believe any differently, but I have NO other explination.

Thanks for everyones help.

WORK AROUND ...HO!

Blocking

I am doing an insert into a table which is taking forever
2 hrs and still waiting for 20 million records
But while its doing the insert i am able to do
select count(*) from table and its giving me 0
my question how cum select is not blocked by above insert
sanjaySanjay,
You might read some under this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_6fhj.asp
And this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_2sit.asp
SQL Server default is to use the isolation level "Read committed". This
means that it will read the rows already there, but none of those in the
insert transaction.
By the way, you are apparently inserting 20 million records in a single
transaction. This will take much longer and use way more log space than
inserting them in smaller chunks. FWIW, I usually keep the insert blocks
down to about 10,000 rows, but that is not a scientific number. :)
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:03a501c34578$ce926ac0$a101280a@.phx.gbl...
> I am doing an insert into a table which is taking forever
> 2 hrs and still waiting for 20 million records
> But while its doing the insert i am able to do
> select count(*) from table and its giving me 0
> my question how cum select is not blocked by above insert
> sanjay

Blob in Tsql

Hi,
I am writing a tsql in which i am saving contents of one table into another using "insert into.. select".
But it doesnt work for Blobs.. what should i do??
can anyone help me on this.
Thank You.Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.

Originally posted by Satya
Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH
Originally posted by swatisk
ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.|||its not a regular process... it is triggered by the user of the system.
i have not used DTS at all.
by the way what is the problem with my code??
the way i am using writetext,readtext is fine??
i am not able to find any example for writing a blob(image) into a blob(image) of a table... not available in the books online.
pls help.

Thank You.

Originally posted by Satya
Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH|||Try this

http://www.motobit.com/help/ScptUtl/sa307.htm

Blank spaces

How can I pass blank spaces for Required fields into database from Forms as
Initial Value , On insert , Post insert options does not work.
Thanks- HAVE YOU TRIED SETTING THE SETTING THE DEFAULT VALUES ON THE FIELDS?

-DO THEY HAVE TO BE SPACES? WOULD 'NULL' VALUES WORK?|||Originally posted by edwinjames
- HAVE YOU TRIED SETTING THE SETTING THE DEFAULT VALUES ON THE FIELDS?

-DO THEY HAVE TO BE SPACES? WOULD 'NULL' VALUES WORK?

1. Forms will not take blank spaces as it will truncate it for varchar2 fiedls .

2. All fields are required.

3. Assigning it from triggers have different problems of record locking.

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

Bitwise question -

I am new to bitwise thing in MSSQL.

Let's suppose there's a table of favorite foods

insert int fav_foods(food_name,bitwiseVal)
values('Pasta',1)

insert int fav_foods(food_name,bitwiseVal)
values('Chicken',2)

insert int fav_foods(food_name,bitwiseVal)
values('Beef',4)

insert int fav_foods(food_name,bitwiseVal)
values('Fish',8)

insert int fav_foods(food_name,bitwiseVal)
values('Pork',16)

How do I write query to find people who selected more than one item and
selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
I hope my question is not confusing....Bostonasian wrote:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....

Your question isn't confusing but your design decision is. Why use
bitwise on something like this? If you were to use proper table design
this query would be trivial (and fast).

Zach|||I tried to simply the example as much as possible, that's probably why
it didn't look that neccesary to build table like this.

I actually have survey data. Survey answer includes text, single select
multiple choice and multi-select multiple choice.

In answered data table, I currently have schema like following :

customer | question_id | answer
---------------
John | 1 | Pasta
John | 1 | Beef
John | 1 | Chicken
John | 1 | Pork

And I've got 2.4 million customers to manage, so I thought it'd save
some rows by using bitwise to reduce row numbers to one.|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

What you save in rows (i.e. disk space, which is cheap), you'll likely
lose in readability, mainainability, performance and standardization.
Search out one of Joe Celko's rants about thinking like a procedural
programmer and not a SQL/set based programmer because I think that's the
problem here.

Zach|||> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

I'll bet that disk space is much cheaper than the cost of the time you'll
spend fixing up a kludge like that :-)

Try this:

CREATE TABLE Foods (customer_id INTEGER NOT NULL REFERENCES Customers
(customer_id), food INTEGER NOT NULL REFERENCES Foods (food), PRIMARY KEY
(customer_id, food))

SELECT customer_id
FROM Foods
WHERE food IN (1,2,3,4,5) /* Pasta,Chicken,Beef,Pork,Fish */
GROUP BY customer_id
HAVING COUNT(CASE WHEN food IN (1,2,3,4) THEN 1 END) = COUNT(*)
/* Everything except fish */

--
David Portas
SQL Server MVP
--|||Bostonasian (axkixx@.gmail.com) writes:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....

SELECT *
FROM tbl
WHERE fav_food & (SELECT SUM(bitwiseVal)
FROM fav_foods
WHERE food_name IN ('Pasta', 'Chicken', 'Beef', 'Pork'))

But as pointed out by others, this is a poor design. You may
save disk space, but if you need to find all that selected Chicken,
you will find that you cannot have an index on bit in an integer
column, so you get awful performance.

Look at David's query, and use that instead of the above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

Hi Bostonasian,

There is no need to denormalize or use bitwise operations for this. IMO,
bitwise operations are not suitable for this problem.

The database does not have to grow very fast. If you normalize all the
way through, you would get a Customers (reference) table, a Questions
(reference) table and a Answers (reference) table. All these reference
tables can have short keys, which you use in your CustomerAnswers (data)
table. If you have fewer than 64000 customers, fewer than 256 questions
and fewer than 256 (fixed) answers per question, then each row in
CustomerAnswers would be just 2+1+1 = 4 bytes (excluding the free format
text answers).

Your schema could look something like this:
CREATE TABLE Customers(CustomerID smallint PRIMARY KEY, Name
nvarchar(100))
CREATE TABLE Questions(QuestionID tinyint PRIMARY KEY, Question
nvarchar(3000))
CREATE TABLE Answers (QuestionID tinyint, AnswerID tinyint, Answer
nvarchar(200),PRIMARY KEY (QuestionID,AnswerID))

CREATE TABLE CustomerAnswers
(CustomerID smallint REFERENCES Customers
,QuestionID tinyint REFERENCES Questions
,AnswerID tinyint
,TextAnswer nvarchar(2000)
,PRIMARY KEY (CustomerID,QuestionID,AnswerID)
,FOREIGN KEY (QuestionID,AnswerID) REFERENCES Answers
)

Hope this helps,
Gert-Jan