Recordset Modification and Copy

Please Help.

I have written a custom DTS task in an ActiveX Script. My script creates three recordsets from three existing tables in an SQL Server 2k DB by copying all the data in these tables.

My script then modifies the data in these recordsets including dropping several records, creates a new table and inserts the modified data into the new table.

Unfortunately, I need to keep my original data unmodified...and currently my code changes my original data when it modifies the record sets.

Does anyone have a method for modifying a recordset without modifying the source data? I am writing my ActiveX Script through the VB scripting language.

From what I have read on the internet some people suggest creating a new recordset, copying the data from the original recordset to the new recordset, and then modify and insert the new recordset. This is supposed to keep my source data unchanged. Unfortunately, I have not been successful with this method mainly because I am having trouble creating a new recordset and copying my original recordset data into this new object.

Thank you in advance for any help or input you might have!!!

-TRocheI assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.|||I don't think this will work. You see I have to manipulate the data through several DO Until loops. The constraint on my DO loop is

"DO UNTIL rs.EOF"

Before I set the recordset ActiveConnection to nothing It tells me I have to first close the recordset. Unfortunately, I can not run the syntax of the above loop if the recrordset is closed, and if I open the recordset with a new connection it again changes my source data.

Does anyone have any additional insight?

Thanks!

-TRoche|||create a function READ UNCOMMITTED transaction isolation level (you can do it with nolock optimizer hint) and do your DO UNTIL against the rs that points to it.|||Wow...I am just a beginner at this stuff...I will look into it, but do you have a sample code for this by chance?

thanks,

TRoche|||create function dbo.fn_select_star_from_your_table (
@.parm1 <data type>,
@.parm2 <data type>,
@.parm3 <data type> ) returns table
as return (
select * from your_table (nolock)
where field1 = @.parm1
and field2 = @.parm2
and @.field3 = parm3)
go|||Hmmmmm....ok to start I am writing a custom DTS Task, using an ActiveX Script in the DTS Designer, coding in VBScript

Well I am having a few problems with the previous comments. First off I can't get the code to drop the function ms_sql_dba advised. Here is my DROP code for a function called FUNSTUFF:

dim Dropx
set Dropx = CreateObject("ADODB.Command")

Dropx = "Drop Function [dbo].[FUNSTUFF]"
connection.execute Dropx

I am only trying to drop the function because if I run the code multiple times it errors saying the the object "FunStuff" already exists in the database...and I looked...and it does...dang Does anyone see something wrong with the above DROP code?

Also, ms_sql_dba, I am not sure I understand what your function does. Here is how I thought to use your advice.

Write a function called ADVICE as follows:
dim ADVICE
set ADVICE = CreateObject("ADODB.Command")

ADVICE = "CREATE FUNCTION dbo.FUNSTUFF (@.time float, @.Position float ) RETURNS Table AS RETURN (select * from GPSy (nolock) where ty = time and GPS_y = Position)"

then:
Open.recordset ADVICE, Connection, adOpenKeyset

Then write my do loop:
DO UNTIL recordset.EOF
blah, blah, blah
Loop

Unfortunately, I run into all kinds of errors like saying the operation can not be performed if the recordset is closed. But if I write an open command like:

open.recordset.connection

I get the error that the function FUNSTUFF alreadyexists in the database.

Hmmmmmmmm...

Does anyone have any advice? Do I have to write the Function code before every loop or a drop code after every loop...or...hmmm...I am just confused about how the function works.

A million thanks to anyone who has any input!!

-TRoche

Records Duplicate when edited...?

Hi,

I have written a web application using dreamweaver MX, asp.net, and MSsql server 2005.

The problem I am having occurs when I attempt to edit a record. I have setup a datagrid with freeform fields so that the user can click on edit, make the required changes within the data grid then click update. The data is then saved to the database. All this was created using dreameaver and most of the code was automatically generated for me.

The problem is that, not everytime, but sometimes when I go to edit a record once I hit the update button to save the changes the record is duplicated 1 or more times. This doesnt happen everytime but when it does it duplicates the record between 1 and about 5 times. I have double checked everything but cannot find anything obvious that may be causing this issue.

Does anyone have any suggestions as to what I should look for? Is this a coding error or something wrong with MSsql? Any ideas?

Thanks in advance
-Mitch

If you provide the code, then it will be easier to give suggestions.

Records appearing and then disappearing

Hi,

We have a SQL server db backend for our ERP system. I have written a
Crystal Report to extract data from one particular table called
itemspecbomw (c.30000 records) which contains amongst other things
Bill of Materials costings. When I run the report I know that some
rows are missing as when I look at values through the ERP system
itself, the values are different.

What I have found is that when I run the equivalent ERP system report,
the Crystal Report I have written shows extra rows. SQL Query Analyser
behaves exactly the same as the report I have written even when using
a "select * from". I have tested opening up a bigger tables (c. 700000
records) which appears without a problem. If anyone knows why this
mystery is happening, I woould be grateful for your help.

Many thanks,

Tony.tkennedy@.blenders.ie (Tony Kennedy) wrote in message news:<f25d55e1.0402170538.4d2c54f4@.posting.google.com>...
> Hi,
> We have a SQL server db backend for our ERP system. I have written a
> Crystal Report to extract data from one particular table called
> itemspecbomw (c.30000 records) which contains amongst other things
> Bill of Materials costings. When I run the report I know that some
> rows are missing as when I look at values through the ERP system
> itself, the values are different.
> What I have found is that when I run the equivalent ERP system report,
> the Crystal Report I have written shows extra rows. SQL Query Analyser
> behaves exactly the same as the report I have written even when using
> a "select * from". I have tested opening up a bigger tables (c. 700000
> records) which appears without a problem. If anyone knows why this
> mystery is happening, I woould be grateful for your help.
> Many thanks,
> Tony.

I don't completely follow your explanation, but you seem to be saying
that QA and Crystal give the same results, but your ERP report gives
different ones? If two tools are giving different results, then the
most likely explanation is that they are submitting different queries.
I would suggest using Profiler to trace the SQL sent to the server by
your ERP application, and then run the same SQL in QA - you may find
that it is constructing a query which is different from what you
expect.

Simon|||Hi Simon,

I checked out the query used in the system ERP report, but I found
when I cut'n pasted the query into query analyser and ran it, it
returned the exact same number of records as the one I wrote. It seems
to be a problem with the way I access the db or that the ERP program
has some sort of lock on the table...

Thanks for your advice,

Tony.

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402171042.2caa5e1c@.posting.google.com>...
> tkennedy@.blenders.ie (Tony Kennedy) wrote in message news:<f25d55e1.0402170538.4d2c54f4@.posting.google.com>...
> > Hi,
> > We have a SQL server db backend for our ERP system. I have written a
> > Crystal Report to extract data from one particular table called
> > itemspecbomw (c.30000 records) which contains amongst other things
> > Bill of Materials costings. When I run the report I know that some
> > rows are missing as when I look at values through the ERP system
> > itself, the values are different.
> > What I have found is that when I run the equivalent ERP system report,
> > the Crystal Report I have written shows extra rows. SQL Query Analyser
> > behaves exactly the same as the report I have written even when using
> > a "select * from". I have tested opening up a bigger tables (c. 700000
> > records) which appears without a problem. If anyone knows why this
> > mystery is happening, I woould be grateful for your help.
> > Many thanks,
> > Tony.
> I don't completely follow your explanation, but you seem to be saying
> that QA and Crystal give the same results, but your ERP report gives
> different ones? If two tools are giving different results, then the
> most likely explanation is that they are submitting different queries.
> I would suggest using Profiler to trace the SQL sent to the server by
> your ERP application, and then run the same SQL in QA - you may find
> that it is constructing a query which is different from what you
> expect.
> Simon

Record set to file

Any tips on how to get a recordset into a file. I want the result of a stored procedure (multiple rows) written to a flat file. I've tried a data flow task with an OLE DB Source linked to Raw File destination but that produces gibberish in the file - I need the raw rows returned from the s/p

Any tips much appreciated.

Greg.You can use DTS (Data Transformation Service) to accomplish this.|||I understood SSIS was the new DTS. Any pointers on how I can go about this Mike?

Greg.
|||Yes, in v2005. I haven't used it extensively, but you can get the information on how to here: http://msdn2.microsoft.com/ms141823(en-US,SQL.90).aspx|||

What do you mean by raw rows? The raw dest produces a binary file that is for use by the dataflow. If you want a readable file then use the flat file destination, which will create a text file.

HTH,

Matt

|||Thanks guys. Got it figured out using the flat file destination.

Greg.

record server and agent start-up status

hi,
When the sql server and sql agent are first started, are
their status being written to any system tables or logs?
Many thanks.
JJThe SQL Server error log has the startup info for SQL
Server. The default location is the Log directory under your
SQL instance directory. The most recent file name is
errorlog. Previous logs have a number appended to the file
name.
The SQL Agent log has the startup info for SQL Server Agent.
It's also in the Log directory. File name is SQLAgent.Out
-Sue
On Mon, 13 Oct 2003 17:18:07 -0700, "JJ Wang"
<jwang@.leapwireless.com> wrote:
>hi,
>When the sql server and sql agent are first started, are
>their status being written to any system tables or logs?
>Many thanks.
>JJ