BCP/DTS/cmdshell problem

Hi
Using 2000
I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million
rows each). I need your help and advice on this
1)is dts faster than BCP converting to flatfiles and again copying to
destination tables.
2) if we write cmdshell and use BCP in that instead of directly using in
.cmd file, will performance be slower. I am new to .cmd so controlling and
error handling will be easier if I write in a sp which uses cmd shell to
extract bcp
3. any resource on writing .cmd files using osql and bcp( templates etc). I
googled but no use.
--
Thanks
DevaHi deva
I think you will get a better answer if you try the DTS discussion forum
"DEva" wrote:

> Hi
> Using 2000
> I am writing a .cmd file for Bulk copying data(about 25 tables of 1 millio
n
> rows each). I need your help and advice on this
> 1)is dts faster than BCP converting to flatfiles and again copying to
> destination tables.
> 2) if we write cmdshell and use BCP in that instead of directly using in
> .cmd file, will performance be slower. I am new to .cmd so controlling and
> error handling will be easier if I write in a sp which uses cmd shell to
> extract bcp
> 3. any resource on writing .cmd files using osql and bcp( templates etc).
I
> googled but no use.
> --
> Thanks
> Deva

Basing Report on a Procedure with Cursor

I need some advice on how to design a report using cursors.

Is it possible to base a report on a cursor?

I would think that I would have to put the cursor into a stored procedure and call that procedure. But if I do use the cursor in the procedure, will the looped variables automatically populate the report or will I need to insert them into a table that populates the report?

Thanks,

cj

If the data source type is "Oracle" and you want to call a Oracle stored procedure, please search for related threads on this forum regarding Oracle stored procedures (e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1).

-- Robert

|||No, it is SQL Server.|||

This should work just fine if you follow the guidelines in http://msdn2.microsoft.com/en-us/library/ms159260.aspx - scroll to the stored procedure section.

If you experience problems, can you post a sample of a SQL stored procedure that doesn't work?

-- Robert

|||

Thanks, I understand that part now. Usually I am populating a table to get my output from the stored procedure. For example, I'll use the INSERT clause to populate as the cursor loops. Can I populate the dataset instead? If so, how?

Thanks,

cj

|||

You could use temp tables in the stored procedure. For example:

CREATE PROCEDURE MyProc
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT * FROM #t
GO

However, in that case make sure to use the text-based query designer (with 2 panes) and keep in mind that automatically generating a schema (getting the list of fields) fails on SPs with temp tables. This automatic method is invoked when you switch from Data view to Layout view.
Instead, before going to Layout, get the schema manually by clicking on the Refresh Fields button which will manually refresh the query.

The automatic method gets a schema quickly when available without any necessary user interaction. (Behind the scenes it is running the query with SET FMTONLY ON). The manual method gets the schema by running the query. It takes longer and will prompt the user for query parameter values if required. The former works for most queries with the exceptions of temp table use or some dynamic SQL. The latter always works, but has performance drawbacks for some queries.

-- Robert

Basic RS Parameter Question

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

Thanks much!

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

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

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

Josh

|||

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

SELECT * FROM tbl_Name WHEREId=@.Id

The RS refer the @.Id as Parameter.