BCP within a transaction

Hi guys
I have a small problem. I want to use BCP with in a transaction it's this
first I execute a SQL Statement against the database and delete some data,
then
I run the BCP utility.
my problem is how to use these two with in a single transaction!
please help me
I appreciate your help and time!
thanks,
-ArunaI haven't tested it myself, but see if BULK INSERT is usable in a transactio
n.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aruna Tennakoon" <aruna.tennakoon@.gmail.com> wrote in message
news:%23L2k7OdWFHA.3840@.tk2msftngp13.phx.gbl...
> Hi guys
> I have a small problem. I want to use BCP with in a transaction it's this
> first I execute a SQL Statement against the database and delete some data,
> then
> I run the BCP utility.
> my problem is how to use these two with in a single transaction!
> please help me
> I appreciate your help and time!
> thanks,
> -Aruna
>|||Hi Tibor,
Thanks for your reply. I tried the BULK INSERT statment but, it didn't work
because it need the text file in the SQL Server.I am using client PC to do
this.
Please send me if you have any sample.
Thanks again
-Aruna
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OY36rDfWFHA.3760@.TK2MSFTNGP15.phx.gbl...
>I haven't tested it myself, but see if BULK INSERT is usable in a
>transaction.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aruna Tennakoon" <aruna.tennakoon@.gmail.com> wrote in message
> news:%23L2k7OdWFHA.3840@.tk2msftngp13.phx.gbl...
>

BCP results in 3 rows

Good Day

I want to know if bcp can do this for me,(I get my results nicely from fetch statement) lets say I do this
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA
and my results are

1 80 6.0 34

I want my text file that I will use bcp to recreate to be like in this format
VersonNo
Fileid
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA

6.0
34
1 80 6.0 34Refer to the books online for BCP Format file option to get the task.|||Refer to the books online for BCP Format file option to get the task.sql

BCP issue

Dear gurus,
I can't work out with a problem using our dearest statement, i.e BCP.
Well, I'm trying to import a .DAT file to a table from a DOS session and I
obtain the following error:
C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
-Uusrdts
-Pdts
Where the hell the problem is?
Thanks for any input or advice and regards,On the strenght and bearing on mind that the name of the database is ABS. An
d
ABS is a reserver word in SQL!!!
"Enric" wrote:

> Dear gurus,
> I can't work out with a problem using our dearest statement, i.e BCP.
> Well, I'm trying to import a .DAT file to a table from a DOS session and I
> obtain the following error:
> C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
> -Uusrdts
> -Pdts
> Where the hell the problem is?
> Thanks for any input or advice and regards,|||"the name of the database is ABS."
Then you are also missing the owner or just leave it away:
[ABS]..abs_diariopeticiones
HTH, jens Suessmeyer.|||cheers
"Jens" wrote:

> "the name of the database is ABS."
> Then you are also missing the owner or just leave it away:
> [ABS]..abs_diariopeticiones
>
> HTH, jens Suessmeyer.
>

BCP in Stored Procedure

I am trying to set up a stored procedure to run BCP. My bcp statement runs fine if I run it from Query Analyzer or from a command prompt. However, when I try to run it from a stored procedure, it will not run. The statement is as follows:

exec master..xp_cmdshell 'bcp FeeScheduleValidation..FS_PhysicianCOSMOSSystemFSFile in C:\FeeScheduleValidationTool\Data\testfile.txt -c -t~ -r\n -S(local) -Ufeescheduleuser -Pfeescheduleuser'

Is there something special that needs to be done to allow bcp to run from a stored procedure?Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

I think you should check the permissions of that stored procedure to make sure it allows "execute".|||Have you looked at using the Bulk Insert command in a stored proc rather than using the command shell...?

bcp export stored procedure with a date in the statement

Hi

Please can someone help me with the statement below. I am trying to export, via bcp a stored procedure which requires two dates and cannot seem to work out the correct way of typing it into the statement. I know that the dates are meant to have an ' around them but cant work out how to get this concatenated correctly.

Any help would be appreciated.

Paul

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP 05/01/07, 01/01/07" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sq

use the following query...

Code Snippet

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

|||Thanks very much for your help

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Invalid Instance()).
NULLTry,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:
> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happens
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Invalid Instance()).
> NULL
>
>

bcp error

I am getting an error that I can't open the host file when running bcp
through a tsql statement. When I set the path to a local drive on the sql
server the utility runs. However, when I set the path to a unc path that th
e
server and service account both have permission to then it won't run.
Any ideas on what I should try to fix this problem?Who is the owner of the job? If it is not sa then you will use the account
setup for the Proxy. See this: http://www.support.microsoft.com/?id=269074
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>I am getting an error that I can't open the host file when running bcp
> through a tsql statement. When I set the path to a local drive on the sql
> server the utility runs. However, when I set the path to a unc path that
> the
> server and service account both have permission to then it won't run.
> Any ideas on what I should try to fix this problem?|||I don't know if this helps answer your question, but it does not run as a
job. It is running through a stored procedure. DBO is the ower of the
stored procedure.
"Andrew J. Kelly" wrote:

> Who is the owner of the job? If it is not sa then you will use the accoun
t
> setup for the Proxy. See this: [url]http://www.support.microsoft.com/?id=269074[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>
>|||Whether it is a job or not still has the requirement of who is trying to
execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
that user is a member of the sa role it will use the account that SQL Agent
is running under. If not it will attempt to use the account that is set for
the Proxy account of SQL Agent. The proxy account most likely does not have
the right permissions. From BOL under xp_cmdshell:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...[vbcol=seagreen]
>I don't know if this helps answer your question, but it does not run as a
> job. It is running through a stored procedure. DBO is the ower of the
> stored procedure.
> "Andrew J. Kelly" wrote:
>|||I executed the following:
xp_sqlagent_proxy_account n'get'
It returned an account. I checked the account and it was a member of the
sysadmin role.
To me, it seems like it should be working.
"Andrew J. Kelly" wrote:

> Whether it is a job or not still has the requirement of who is trying to
> execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
> that user is a member of the sa role it will use the account that SQL Agen
t
> is running under. If not it will attempt to use the account that is set f
or
> the Proxy account of SQL Agent. The proxy account most likely does not ha
ve
> the right permissions. From BOL under xp_cmdshell:
> When xp_cmdshell is invoked by a user who is a member of the sysadmin fixe
d
> server role, xp_cmdshell will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent prox
y
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail. This is true only for
> Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
> impersonation and xp_cmdshell is always executed under the security contex
t
> of the Windows 9.x user who started SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...
>
>|||That does not mean it has permissions to access the file share or even the
server. Is that account a domain account with permissions to that share?
The sa part has to do with who is running the command and not the proxy
account. Please read the entry in BOL for xp_cmdshell.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...[vbcol=seagreen]
>I executed the following:
> xp_sqlagent_proxy_account n'get'
> It returned an account. I checked the account and it was a member of the
> sysadmin role.
> To me, it seems like it should be working.
>
> "Andrew J. Kelly" wrote:
>|||The account is a domain account and does have permissions to the share.
"Andrew J. Kelly" wrote:

> That does not mean it has permissions to access the file share or even the
> server. Is that account a domain account with permissions to that share?
> The sa part has to do with who is running the command and not the proxy
> account. Please read the entry in BOL for xp_cmdshell.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...
>
>

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Invalid Instance()).
NULL
Try,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:

> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happens
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Invalid Instance()).
> NULL
>
>

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invali
d
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Invalid Instance()).
NULLTry,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:

> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happen
s
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Inva
lid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Co
nnectionOpen
> (Invalid Instance()).
> NULL
>
>

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -T
Please post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is
I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here'
s
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:
> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

Batch Printing

Hi,

I have to print bank statements for several thousand accounts. I'll have to design a statement template and connect it to my SQL Server database. What tool(s) can I use for this? I can use Reporting Services but I'm not sure if I can use it to print several reports based on an account range in batch mode. Any pointers and suggestions will be highly appreciated.

Thanks.

Umar.

Reporting Services, custom Visual Studio application, Access -even Microsoft Word or Excel 'could' work.

|||See this http://www.sql-server-performance.com/sm_sql_2005_reporting.asp for direct client printing.

Batch Insert

I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx
Please post the DDL of both tables plus the error message.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx
|||Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
wh_idvarcharno10
order_numbervarcharno30
store_order_numbervarcharno40
type_idintno4
customer_idcharno15
cust_po_numbervarcharno30
customer_namevarcharno100
customer_phonevarcharno30
customer_faxvarcharno30
customer_emailvarcharno50
departmentcharno10
load_idvarcharno30
load_seqintno4
bol_numbercharno10
pro_numbervarcharno20
master_bol_numbercharno10
carriervarcharno30
carrier_scacvarcharno4
freight_termsvarcharno10
rushcharno5
priorityvarcharno3
order_datedatetimeno8
arrive_datedatetimeno8
actual_arrival_datedatetimeno8
date_pickeddatetimeno8
date_expecteddatetimeno8
promised_datedatetimeno8
weightfloatno8
cubic_volumefloatno8
containersintno4
backordercharno1
pre_paidcharno10
cod_amountfloatno8
insurance_amountfloatno8
pip_amountfloatno8
freight_costfloatno8
regionvarcharno5
bill_to_codecharno15
bill_to_namevarcharno30
bill_to_addr1varcharno30
bill_to_addr2varcharno30
bill_to_addr3varcharno30
bill_to_cityvarcharno30
bill_to_statevarcharno3
bill_to_zipvarcharno12
bill_to_country_codecharno5
bill_to_country_namevarcharno30
bill_to_phonevarcharno30
ship_to_codecharno15
ship_to_namevarcharno30
ship_to_addr1varcharno30
ship_to_addr2varcharno30
ship_to_addr3varcharno30
ship_to_cityvarcharno30
ship_to_statevarcharno3
ship_to_zipvarcharno12
ship_to_country_codecharno5
ship_to_country_namevarcharno30
ship_to_phonevarcharno30
delivery_namevarcharno30
delivery_addr1varcharno30
delivery_addr2varcharno30
delivery_addr3varcharno30
delivery_cityvarcharno30
delivery_statevarcharno3
delivery_zipvarcharno12
delivery_country_codecharno5
delivery_country_namevarcharno30
delivery_phonevarcharno30
bill_frght_to_codecharno15
bill_frght_to_namevarcharno30
bill_frght_to_addr1varcharno30
bill_frght_to_addr2varcharno30
bill_frght_to_addr3varcharno30
bill_frght_to_cityvarcharno30
bill_frght_to_statevarcharno3
bill_frght_to_zipvarcharno12
bill_frght_to_country_codecharno5
bill_frght_to_country_namevarcharno30
bill_frght_to_phonevarcharno30
return_to_codecharno30
return_to_namevarcharno30
return_to_addr1varcharno30
return_to_addr2varcharno30
return_to_addr3varcharno30
return_to_cityvarcharno30
return_to_statevarcharno3
return_to_zipvarcharno12
return_to_country_codecharno5
return_to_country_namevarcharno30
return_to_phonevarcharno30
rma_numbervarcharno40
rma_expiration_datedatetimeno8
carton_labelvarcharno10
ver_flagcharno4
full_palletsintno4
haz_flagcharno10
order_wgtfloatno8
statusvarcharno20
zonevarcharno10
drop_shipcharno1
lock_flagvarcharno10
partial_order_flagcharno1
earliest_ship_datedatetimeno8
latest_ship_datedatetimeno8
actual_ship_datedatetimeno8
earliest_delivery_datedatetimeno8
latest_delivery_datedatetimeno8
actual_delivery_datedatetimeno8
routevarcharno30
order_amountfloatno8
pick_typecharno1
invoiced_amountfloatno8
t_pick_detail definition
pick_idintno4
order_numbervarcharno20
line_numbervarcharno5
typecharno2
uomvarcharno10
work_q_idvarcharno30
work_typevarcharno2
label_numbervarcharno22
statusvarcharno10
item_numbervarcharno30
lot_numbervarcharno15
serial_numbervarcharno30
unplanned_quantityfloatno8
planned_quantityfloatno8
picked_quantityfloatno8
staged_quantityfloatno8
loaded_quantityfloatno8
pick_locationvarcharno10
picking_flowvarcharno10
staging_locationvarcharno10
zonevarcharno20
wave_idvarcharno20
load_idvarcharno30
load_sequenceintno4
stop_idvarcharno20
container_idvarcharno22
pick_categoryvarcharno10
user_assignedvarcharno10
bulk_pick_flagcharno1
stacking_sequenceintno4
pick_areavarcharno10
wh_idvarcharno10
requested_quantityfloatno8
request_returned_qtyfloatno8

>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>
|||1) You left out the definition for t_order_detail.
2) You did not give actual DDL - i.e. CREATE TABLE statements.
3) You did not post the error message.
4) Of the tables you did present, you have mismatched datatypes, which
may be the source of your problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8

>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>
|||Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.

>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>message.
-
>--
>message
>.
>
|||Reconsider your approach. It will take _much_ longer. It will be much
faster if you simply post the DDL and the error message.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<anonymous@.discussions.microsoft.com> wrote in message
news:0a5501c4e39e$f909e650$a401280a@.phx.gbl...
Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.

>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>message.
-
>--
>message
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Thanks for looking at it , Tom
> I have suggested the folks to use a loop method to insert
> rather than the current method.
> Thanks again.
>
Are you the OP? If so, are you suggesting that a looping construct
inserting a row at a time will be faster than a single insert statement?
Before commiting to that design, some testing is in order.
David Gugick
Imceda Software
www.imceda.com

Batch Insert

I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
ThanxPlease post the DDL of both tables plus the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx|||Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||1) You left out the definition for t_order_detail.
2) You did not give actual DDL - i.e. CREATE TABLE statements.
3) You did not post the error message.
4) Of the tables you did present, you have mismatched datatypes, which
may be the source of your problem.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||Reconsider your approach. It will take _much_ longer. It will be much
faster if you simply post the DDL and the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<anonymous@.discussions.microsoft.com> wrote in message
news:0a5501c4e39e$f909e650$a401280a@.phx.gbl...
Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Thanks for looking at it , Tom
> I have suggested the folks to use a loop method to insert
> rather than the current method.
> Thanks again.
>
Are you the OP? If so, are you suggesting that a looping construct
inserting a row at a time will be faster than a single insert statement?
Before commiting to that design, some testing is in order.
--
David Gugick
Imceda Software
www.imceda.com

batch file help

I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
statement below needs some modification as well.
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
When i enter test.bat, i want it to prompt for servername so i can add it at
the prompt
Also i would like the prompted servername to be filled in %1 parameter in
the OSQL . Can this be done ?Hi Hassan,
This is not the right place to ask any batch file related questions.
Anyway try this ....
You can specify server name in the command propmt. If you haven't specify
the server name in the command prompt it will prompt for it.
Regards,
Suhanthan, V.
suhan@.jhc.lk
----
@.ECHO OFF
IF NOT _%1_ == __ GOTO WITHPARAM
GOTO WITHOUTPARAM
:WITHPARAM
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
GOTO EXITTHIS
:WITHOUTPARAM
SET /P SERVER="Enter SQL server name :"
OSQL -E -S%SERVER% -Q"select @.@.servername" -oc:\xyz\%1output.txt
SET SERVER=
:EXITTHIS
----
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O2$uiVRnDHA.1084@.tk2msftngp13.phx.gbl...
> I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
> statement below needs some modification as well.
> OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
> When i enter test.bat, i want it to prompt for servername so i can add it
at
> the prompt
> Also i would like the prompted servername to be filled in %1 parameter in
> the OSQL . Can this be done ?
>
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?
Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

Basic SQL: Multiple AND/OR nightmare in SELECT statement

This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT *
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
ParentID1 = COALESCE(@.taxparent, ParentID3) OR
ParentID2 = COALESCE(@.taxparent, ParentID2) OR
ParentID3 = COALESCE(@.taxparent, ParentID3) OR
ParentID4 = COALESCE(@.taxparent, ParentID4) OR
ParentID5 = COALESCE(@.taxparent, ParentID5) OR
ParentID6 = COALESCE(@.taxparent, ParentID6) OR
ParentID7 = COALESCE(@.taxparent, ParentID7) OR
ParentID8 = COALESCE(@.taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

Hi

You can also use IN and NOT IN as well as Having caluse to verify condition. As ( and ) paranthesis can also help you to verify condition on a condition,

|||Hi Akbar,

Sorry, but that is not really helpful.

As far as I understood, IN is used to test multiple values against a single column. I am testing a single value against multiple columns.

HAVING is used with aggregate values. I am not using those.

As I already wrote, using parenthesis does not work for me, or I do not know how to properly apply them in this particular case.

I am still with my hands in my hair on finding a solution to this, so I would appreciate any help.|||All right, fair enough, after some study, I was able to simplify the code using 'IN', that -new to me- could also be used for testing a single value against multiple columns. But I am stuck with the same problem that it won't combine with the rest of the conditions in order to yield the desired results!

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' OR
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me too many results and

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me no results....

|||OK, another discovery! When I comment out:

ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%'

It does actually succesfully combine the different criteria!

This must mean something goes wrong with those lines only...

Here is the entire code of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [petrander].[DynamicQuery]
@.taxparent int = NULL,
@.museum int = NULL,
@.collection int = NULL,
@.binomen Nvarchar(254) = NULL,
@.locality Nvarchar(254) = NULL
AS
SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE 'N%' + @.binomen + '%' AND
Locality LIKE 'N%' + @.locality + '%' AND
@.taxparent IN (ParentID1,
ParentID2,
ParentID3,
ParentID4,
ParentID5,
ParentID6,
ParentID7,
ParentID8)

Could the problem lie in combining null values with the LIKE 'N%' + statements?|||Problem lay somewhere else and solution can be seen in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620363&SiteID=1