ActiveX Script Task being ran twice?

I currently have a DTS package that contains an ActiveX Script Task. This script creates a report and saves the report in the database (for scalability reasons.) When I execute the step in Enterprise Manager it generates two entries in the database, it should only create one. If I take the script out of the DTS package and run it using CScript.exe it only creates one entry in the database.

Has anyone run into this problem before? Could I have done anything to the package that would cause it to execute the ActiveX Script Task twice?

Thanks for any advice to resolve this issue.

Have a great day,

CraigI figured it out, I was making the following call, "Call Main()" in my script and didn't realize that by default DTS packages call Main().

accessing result of sp_tables

When I run exec sp_tables it give me a "list" of tables in databases. In fact the result is a table where one of the columns contains the database table names. It is no problem to access it in a client application but I need to access that column and verify existance of a certain table in SQL Server's user stored procedure. So far anything I tried did not work. FETCH pertains to a cursor and I cannot declare a cursor for this command. The only thing I got so far was @.@.ROWCOUNT but it is of no use for me. Perhaps there is another way to verify if a table exists from inside a stored procedure.

I have the same problem in terms of verifying existence of a user stored procedure from inside another user stored procedure but if I get help on the first point, this one will be no brainer.

Thanks.

WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens K. Suessmeyer wrote:

WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hi Jens,

I do use INFORMATION_SCHEMA in my C# app but now I need to get this information in a stored procedure in SQL Server itself. Everything is confined to the same database boundary. The problem is: I am trying to optimize some operations in terms of performance and disk space. My records are first written in a common table. There might be a up to 100,000 records per day or more. All records are dated of course, so I can pull records for one day at will. All records for a day may be broken into up to 20 categories (symbols). They have a column (field) called symbol, After accumulating records for a few days, let say 5, I want to select all records for the latest date and insert them into a SEPARATE tables for each symbol. Then these records will be purged from the common table for good. The symbol column is absent in those tables but the symbol is part of their name, e.g "table_MSFT" Now, I do not want to create those tables in advance since it may lead to confusion. I want my stored procedure to be able to create a table once it found that a new symbol just appeared in the first, common table. It may be tomorrow or two months from now.

The reason I try to implement a design like this is that if I have to create tables half a year from now it will be hard to recall all the details and the errors will be inevitable. I want to invest time now and make it automatic for all time.

I actually solved the problem in terms of what I've tried to achieve. I do not know why it did not work for me the first time around, some goofy bug got on the way perhaps, but now it works very well. The way I do it is as follows:

exec sp_tables <tableName> This gives me just one row. I check @.@.ROWCOUNT and if it is 0, the table is created on the fly. I have done it many times already.

HOWEVER, I really want to get access to the columns in that row (or rows if I use exec sp_tables with no argument). I want to be able to access the fields and test the values if I need it. I know I will need it in the future. I am wondering if you are aware of a simple way to accomplish it.

If you do not know how to do it or it is not feasible to do, I want to consider CLR stored procedures. In other words I can write a C# code that will do it and store it in SQL Server. I have never done it and have a vague understanding how they work, how they are stored and how the information is retrieved from them. Remember, I want to use those stored procedures inside other stored procedures on the Server. In the client app it is all very simple.

Many thanks for your help.

|||

Jens K. Suessmeyer wrote:

WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

OK Jens,

This is the idea I have decided to try. What if I take a system stored procedure sys.sp_stored_procedures, change its name and modify it in such a way that it will store resulting table of the existing stored procedures either in a temporary table or even in a special permanent workbench table created specifically for this purpose. I had to change the name of the procedure to dbo.mine_sp_stored_procedures because otherwise I was getting an error on the first line but then the things proceeded smoothly (seemingly) in terms of its being executed with no errors.

However, when I tried to run it with parameters or without it broke down because it could not find certain objects. One of them is sys.numbered_procedures$. It is on line 118. The line numbering I found meaningless. The line number you get in the messages window does no correspond to anything in the code and if you try to find this line with Edit-->Go TO you will never find what you want.

Anyway, my knowledge base is too week at this point to find a way around it, how to handle it. Perhaps you may have an idea.

Thanks.

|||Hi Alex,

I don′t know if I got you right, but here is an option for getting the results from a stored procedure and looping through the details:

IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL

Drop Table #SomeTable

CREATE TABLE #SomeTable

(

Ident INT IDENTITY(1,1),

A int,

B varchar(60),

C varchar(255)

)

DECLARE @.I INT

DECLARE @.ROWCOUNT INT

SET @.I = 0

INSERT INTO #SomeTable

(

A,B,C

)

EXEC SP_Server_info

SET @.RowCount = @.@.ROWCOUNT

WHILE @.I<= @.RowCount

BEGIN

Select * From #SomeTable

Where Ident = @.I

SET @.I = @.I +1

END

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Hi Alex,

I don′t know if I got you right, but here is an option for getting the results from a stored procedure and looping through the details:

IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL

..........................................
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I just tried it. Thanks you very much. It worked. It looks like it is what I may use. I think this is the answer. So much for mine_stored_procedures:) But it is a good news.

Accessing Queries in sqlDataAdapter

Hello Dears;

I have an SqlDataAdapter which contains may queries. One query contians 3 query parameters as follows:

SELECT SUM(Amount) AS Total
FROM Boxes
WHERE EntryDate BETWEEN @.date1 AND @.date2 ANDArea=@.Area

the query cannot be ran unless the queries parameters were provided. I have to access this query in code to add the parameters from controls. Is it possible to access it or use sqlDataSource instead?

Thanks alot

Here is one example I grabed from tutorial with a little modifications for your reference:

Sub GetAuthors_Click(SenderAs Object, EAs EventArgs)

Dim DSAs DataSet
Dim MyConnectionAs SqlConnection
Dim MyCommandAs SqlDataAdapter

Dim strSelectCommandAsString = "select * from Authors where state = @.State"

MyConnection =New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("PubString").ConnectionString
MyCommand =New SqlDataAdapter(strSelectCommand, MyConnection)

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.State", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@.State").Value = someValue 'you need send in

DS =new DataSet()
MyCommand.Fill(DS, "Authors")

Mygv.DataSource=DS.Tables("Authors").DefaultView
Mygv.DataBind()
End Sub

You can add all your parameters to your SqlDataAdapter from your code.

|||Thanks Limno