exectuing a .bat from cmdexec

All,
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:
> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creating
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be the
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
> Sorry, the step does fail. I see it in the appliction log as a failure. I do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
> > All,
> >
> > sql 2000 sp3
> >
> > I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> > data in the files that I am creating. Below is the contents of the .bat. I
> > have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> > connection, any other ideas would be a great help.
> >
> > db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> > db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> > d:\wip\ds2_wrs_ind_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> > d:\wip\ds3_wrs_clr_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> > d:\wip\ds4_wrs_client_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> > d:\wip\ds5_wrs_diary_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> > d:\wip\ds6_wrs_dry_history_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> > d:\wip\ds7_wrs_folder_control_db2.txt
> > --
> >
> > Thanks,
> >
> > Snyper

exectuing a .bat from cmdexec

All,
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:

> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is
no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creatin
g
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be th
e
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
[vbcol=seagreen]
> Sorry, the step does fail. I see it in the appliction log as a failure. I
do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
>

Excuting distributed queries in parallel

Hi,

Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:

SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')

I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:

SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..

Any suggestions?

Rgds

Bob

TSQL statement execution is always serial in a batch or module. You can run the statements in parallel by doing one of the following:

1. Use on-demand SQL Agent jobs which contain TSQL task with each distributed query. You can then start each job in your TSQL code and wait for their completion

2. In SQL Server 2005, you can use service broker messaging infrastructure to activate multiple procs in parallel with each one executing a specific query

However, it is not clear if you are returning the results to client or processing on the server side itself. If you are returning the results to client then these techniques will not be efficient because you will have to dump the results into a table and then query it back. So you might as well run 3 different commands from the client code.

Excluding weekends in queries

Hi
How can I exclude wenddays from a query?
I have found "SQLDMOW_WEnds" which seems to be made for that purpose?
Otherwise I guess the only way is to use something like
where day(datefield) <> 6 and day(datefield) <> 7
Any other suggestions?
regards
HenryBuild a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
w_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
..);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html|||hi henry
you can do it as
SELECT datepart(w,datefield) NOT IN (1,7)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Henry" wrote:

> Hi
> How can I exclude wenddays from a query?
> I have found "SQLDMOW_WEnds" which seems to be made for that purpose
?
> Otherwise I guess the only way is to use something like
> where day(datefield) <> 6 and day(datefield) <> 7
> Any other suggestions?
>
>
> regards
> Henry
>
>|||That should be "dw" or "wday" in the DATEPART function; also look up
@.@.DATEFIRST and SET DATEFIRST before hard-coding the IN(1,7) condition.
"Chandra" wrote:
> hi henry
> you can do it as
> SELECT datepart(w,datefield) NOT IN (1,7)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Henry" wrote:
>sql

excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.

Excluding a column with snapshot replication

I have a database that I'm trying to replicate to allow users to run MI
type queries. This would remove the impact on the operational database
of long running expensive queries. The database records details of
emails including the text and is about 40 GB in size. The database is
part of a package system. One table contains all the text of all emails
in an ntext column and accounts for 25 GB. I would like to exclude the
column from replication as with it, replication takes over 12 hours and
the MI queries do not use the column. However, the queries do use views
that reference the column.
I'm using snapshot replication that runs once a day. In the
"Publication Properties" in the "Filter Columns" tab, I de-selected the
column. The target database has an identical schema to the source
database. When the distribution job runs it fails with a message
indicating it couldn't bulk load the table in question and the error
message indicates that an "Unexpected EOF encountered in BCP data-file".
I've assumed that the BCP data file has a structure that is at
variance with the table. I tried dropping the column from the target
table but replication then fails during the application of a number of
*View.sch scripts because the views reference the column. This is
despite the fact that the publication property for all database objects
is not to drop them.
Can anyone suggest a way in which I can get replication to work without
including the data in one column but to retain the complete schema.
TIA
Laurence Breeze
Laurence,
you could replicate the table (minus the problem column) to a table of
another name. Create a view which has the old tablename and queries the new
table, with an additional column containing a hardcoded null.
Rgds,
Paul Ibison
|||Thanks Paul,
This has done the trick.
Laurence
Paul Ibison wrote:
> Laurence,
> you could replicate the table (minus the problem column) to a table of
> another name. Create a view which has the old tablename and queries the new
> table, with an additional column containing a hardcoded null.
> Rgds,
> Paul Ibison
>

Excessive memory usage

Hi.
my sqlservers are using 1.5 gigs of memory and
experiencing slow queries where large amount of rows are
returned. Some page swapping at the os is occurring as
well. Any ideas?
Hi -
Please check the KB Arcticle
http://support.microsoft.com/default...b;EN-US;110983
Some time when any relatively heavy query/process comes down... the system
starts paging because there are not enough free sectors to swap out data
Thanks
-Surajit
"KD" <anonymous@.discussions.microsoft.com> wrote in message
news:19ae701c44d63$7558ee40$a401280a@.phx.gbl...
> Hi.
> my sqlservers are using 1.5 gigs of memory and
> experiencing slow queries where large amount of rows are
> returned. Some page swapping at the os is occurring as
> well. Any ideas?

Excessive memory usage

Hi.
my sqlservers are using 1.5 gigs of memory and
experiencing slow queries where large amount of rows are
returned. Some page swapping at the os is occurring as
well. Any ideas?Hi -
Please check the KB Arcticle
http://support.microsoft.com/defaul...kb;EN-US;110983
Some time when any relatively heavy query/process comes down... the system
starts paging because there are not enough free sectors to swap out data
Thanks
-Surajit
"KD" <anonymous@.discussions.microsoft.com> wrote in message
news:19ae701c44d63$7558ee40$a401280a@.phx
.gbl...
> Hi.
> my sqlservers are using 1.5 gigs of memory and
> experiencing slow queries where large amount of rows are
> returned. Some page swapping at the os is occurring as
> well. Any ideas?

Excessive memory usage

Hi.
my sqlservers are using 1.5 gigs of memory and
experiencing slow queries where large amount of rows are
returned. Some page swapping at the os is occurring as
well. Any ideas?Hi -
Please check the KB Arcticle
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110983
Some time when any relatively heavy query/process comes down... the system
starts paging because there are not enough free sectors to swap out data
Thanks
-Surajit
"KD" <anonymous@.discussions.microsoft.com> wrote in message
news:19ae701c44d63$7558ee40$a401280a@.phx.gbl...
> Hi.
> my sqlservers are using 1.5 gigs of memory and
> experiencing slow queries where large amount of rows are
> returned. Some page swapping at the os is occurring as
> well. Any ideas?

EXCEPTION_ACCESS_VIOLATION

I have installed SQL 2000 sp3a and I am getting and access
violation on even the simplest queries performed from the
Query Analyser.
Example
Select * from tblcode
Returns
SqlDumpExceptionHandler: Process 54 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Any ideas will be greatly appreciated.| From: "Doug" <dougs@.inquiry-tracking.com>
| Sender: "Doug" <dougs@.inquiry-tracking.com>
| Subject: EXCEPTION_ACCESS_VIOLATION
| Date: Tue, 9 Dec 2003 10:36:06 -0800
|
| I have installed SQL 2000 sp3a and I am getting and access
| violation on even the simplest queries performed from the
| Query Analyser.
|
| Example
| Select * from tblcode
|
| Returns
|
| SqlDumpExceptionHandler: Process 54 generated fatal
| exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
| is terminating this process.
|
| Any ideas will be greatly appreciated.
--
Hi Doug,
You should raise a product support call on this.
Thanks,
--
Eric Cárdenas
SQL Server support
||||Same sh*t that happens to me, seems to be connected to sqlServer now
generatign faulty indexes, beats me as to how I'm to handle it.
Everytime the maintenanceplan has been run it fails and leaves a certain
table inoperable/inaccesible (due to sqlServer generation badly namded
_and_ duplicate named indexes that cannot be deleted) :(
Anyone else experiencing this problem?
Peace,
Johan
In article <1058e01c3be83$4ed4fbc0$a601280a@.phx.gbl>, dougs@.inquiry-
tracking.com says...
> I have installed SQL 2000 sp3a and I am getting and access
> violation on even the simplest queries performed from the
> Query Analyser.
> Example
> Select * from tblcode
> Returns
> SqlDumpExceptionHandler: Process 54 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Any ideas will be greatly appreciated.
>|||Just found the answer - in article
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q293/1/77.ASP&N
oWebContent=1
But since this only recognizes the problem with 'hind%'-indexes you'll
have to adjust statement to include (for example) '== '. This problems
seems to have been a well known fact for sql7 and "known" for sql2000.
After scanning this newsgroup it seems that I'm not the only one
experiencing this problem, and it seems to have risen to "fame" with
sp3a...
2 words - "fuck" & "microsoft"...
Peace,
Johan
In article <MPG.1a47a16784ac9671989681@.news.internet5.net>, johan@.fap.se
says...
> Same sh*t that happens to me, seems to be connected to sqlServer now
> generatign faulty indexes, beats me as to how I'm to handle it.
> Everytime the maintenanceplan has been run it fails and leaves a certain
> table inoperable/inaccesible (due to sqlServer generation badly namded
> _and_ duplicate named indexes that cannot be deleted) :(
> Anyone else experiencing this problem?
> Peace,
> Johan
> In article <1058e01c3be83$4ed4fbc0$a601280a@.phx.gbl>, dougs@.inquiry-
> tracking.com says...
> > I have installed SQL 2000 sp3a and I am getting and access
> > violation on even the simplest queries performed from the
> > Query Analyser.
> >
> > Example
> > Select * from tblcode
> >
> > Returns
> >
> > SqlDumpExceptionHandler: Process 54 generated fatal
> > exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> > is terminating this process.
> >
> > Any ideas will be greatly appreciated.
> >
>|||| 2 words - "fuck" & "microsoft"...
|
| Peace,
| Johan
--
Johan,
Have you actually RAISED A CALL with MICROSOFT?
--
Eric Cárdenas
SQL Server support