bcp -x and "Invalid Field index."

Hey, I am using bcp to try to create my xml file formate off from a table
that has about 477 fields. I am getting the following error:
F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
FNIApplication_X
MLFormatFile.xml
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I remove the -x to create the non xml file format no issues arise.
Idea's? I have not been able to find anything on web about this.
bcp Yea, you know me. (bcp Yea, you know me.@.discussions.microsoft.com)
writes:
> Hey, I am using bcp to try to create my xml file formate off from a table
> that has about 477 fields. I am getting the following error:
> F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
> FNIApplication_X
> MLFormatFile.xml
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
>
> When I remove the -x to create the non xml file format no issues arise.
> Idea's? I have not been able to find anything on web about this.
Sounds like a bug to me. I suggest that you file one on
http://connect.microsoft.com
Personally, I have not been able to whip up any enthusiams over the XML
format file. I have not been able to find that it buys me anything that
the old format does not. (Which, true, is fairly obscure. But I know
it by now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.
Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:

> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.
sql

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:
> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.sql

bcp utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

BCP Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql

BCP out, extended chars when field is null (sometimes)

I want to "BCP out" some tables. The format is to be delimited with "\r\n"
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end

BCP not exporting the "{" or "}" characters

Hello,

I am running a bcp command to export all data from one single table. One of the table field has the flower brackets at the begining and the end and the column is defined as of data type uniqueidentifier (length 16).

The original command that I used is :

c:\bcp dbo.Product out c:\product.csv -w -t"|" -r\n

The ProdID column in Product table have values like : {3E116F82-5E52-4EF9-9A97-8756EA6E9A16}

But in the out put file, the flower brackets are being omitted.

So, I tried the following query :

c:\bcp "Select ID,Name,"+'"{"+'+"convert(varchar(100),ProdID)"+'+"}"'+",ProdType,IsActive,TitleCode,CreationDate,CreatorID,AllowView,AllowRead,AllowWrite,AllowDelete,AdminURL,ReportProc from dbo.Product" queryout c:\Product16Feb.csv -w -t"|" -r\n

But it is giving the following error message:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

The SQL statement is correct because when I run it individuall, it works but not with the bcp command.

I just want the ProdID column to have the flower brackets in the output file using bcp. How can I achieve this?

Many thanks

Ratan

You can define your SQL statement as a view and then BCP the view. Otherwise, consider editing the file after the BCP operation and applying the brackets then.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
files
Hi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish
|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
--
Yogish|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish|||this is the limitation of bcp. use dts instead.

BCP Field Separator

Hi All,
I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or o
perator. The bcp is giving error if I sepcify -t | .
Please suggest something as I have to upload a file separated with pipe.
DipankarHi
Use -t "|" (Inside double quotes)
Thanks
Hari
MCDBA
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:EF3D99FB-31F9-42D7-B920-BCEF1EB52541@.microsoft.com...
> Hi All,
> I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or
operator. The bcp is giving error if I sepcify -t | .
> Please suggest something as I have to upload a file separated with pipe.
> Dipankar|||Dipankar,
put double quotes around the delimiter. i.e.
bcp mydb..mytable out C:\mytable.bcp -c -T -t"|"
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Thanks all...It's working perfectly now.

BCP Field Separator

Hi All,
I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or operator. The bcp is giving error if I sepcify -t | .
Please suggest something as I have to upload a file separated with pipe.
Dipankar
Hi
Use -t "|" (Inside double quotes)
Thanks
Hari
MCDBA
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:EF3D99FB-31F9-42D7-B920-BCEF1EB52541@.microsoft.com...
> Hi All,
> I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or
operator. The bcp is giving error if I sepcify -t | .
> Please suggest something as I have to upload a file separated with pipe.
> Dipankar
|||Dipankar,
put double quotes around the delimiter. i.e.
bcp mydb..mytable out C:\mytable.bcp -c -T -t"|"
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Thanks all...It's working perfectly now.

BCP Error with Key Field Name

Hi I have a table with the following structure

ID Int,
Key nVarchar2(50),
Text nText,
upsizeTS TimeStamp

I want to use BCP to import some data,

it always gives me the following error:
SQLSTATE = 37000, native error 156
Incorrect syntax near the keyword 'Key'

There is no way I can change the name of the field to something else.

Could anybody tell me why am I getting this error ?
Thank you in advance.You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||How do I do that, what script are you talking about. I am using a BCP command.

Originally posted by DoktorBlue
You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||Post your BCP command.

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:

> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

Batch update and trigger

Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit

Basic select statement question

If I want to select certain values from a table where date is equal to something and the second field is null,would I go.

select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.

thanksNULL is a tricky beast, because nothing ever equals NULL, not even NULL itself. You need to modify your statement slightly to use a semantically different test, like:SELECT *
FROM table
WHERE date = 'something'
AND Sent IS nullThis seems like a trivial difference, and from a coding perspective it is. From the logical perspective however, the difference is huge.

-PatP|||Ooh,that made all the difference, I always say, it is easy once you know how to do it.
Thanks for you help once again|||Since you're dealing with this, you might want to look up ANSI_NULLS in Book Online and memorize it. :) It will save you a lot of pain down the road. Make sure you use SET ANSI_NULLS ON when creating tables and procedures.|||Will do so,thanks

Basic RS Parameter Question

Being VERY new to RS 2000 (started looking at it a week ago), I need some advice. I have a table that contains a datetime field and I have a report that is prompting for a start date and start time, along with an end date and end time (4 prompts in total). How do I combine the dates and times together to pull into the query filter? Am I even going about this the right way?

Thanks much!

If I am understanding correctly you could do something like in your filters set the

TimeValue(Fields!Example.value) = TimeValue(Parameters!Time.Value)

and you could do that for time and date if you are trying to break apart a datetime field.

Josh

|||

u can add the parameters into the SQL code such as :

SELECT * FROM tbl_Name WHEREId=@.Id

The RS refer the @.Id as Parameter.