Problem In Execution Dts(sql 2000) Using Vb.net
Sep 5, 2005
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim objDTSPackages As DTS.Package
objDTSPackages = New DTS.Package
objDTSPackages.LoadFromSQLServer("(local)", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "NEW")
objDTSPackages.Execute()
Catch ex As Exception
lblmsg.Text = ex.Message
End Try
End Subusing above coding i am getting error . can anyone solve this problem?
View 3 Replies
ADVERTISEMENT
May 21, 2002
I have a procedure (used to create a report) and was used in sql 7.0 service pack 3.
Problem is that we are upgrading to SQL 2000 and this procedure now takes 1 minute and 30 seconds to execute vs. 10 seconds previously.
Everything is same between the sql 7 and sql 2000 server. i.e. database size, indexes, hardware etc.
I looked at the query execution plan and it seems to do a sort which is taking majority of the resources on sql 2000 even though there is no sort stmt issued in the procedure itself.
Any help would be appreciated?? I am more curious to find out why this is the case when all the variables are same between the two servers yet sql 2000 performance is much worse than sql 7.0. It should be the other way around!!
View 2 Replies
View Related
Feb 10, 2006
please kindly help me for making a database
My problem is that
firstly when we create databse through enterprise manager
then the second step is i want to create a tables.
when i am creating any one table in data base after this process when execuet the Query/show the table then the message is displayed
" the query cannot executed because some files are either missingor not register
Run setup files again to make the required files are register."
how to handle this problem what can i do ?:shocked: :shocked:
View 3 Replies
View Related
Jul 20, 2005
Hi All,I have a table that currently contains approx. 8 million records.I'm running a SELECT query against this table that in somecircumstances is either very quick (ie results returned in QueryAnalyzer almost instantaneously), or very slow (ie 30 to 40 seconds toreturn results), and I'm trying to work out how I improve performance.Essentially the query I'm running is nothing more complex than:SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id[tier] is a smallint column with a non-clustered, non-unique index onit. [member_id] is a numeric column with a clustered, unique index onit.When I supply a [tier] value of 1, it returns results instantaneously.I have no idea if this is meaningful, but the tier = 1 records wereloaded first into the table, and comprise approximately 5 millionrecords.When I supply a [tier] value of 2, the results take 30 to 40 seconds.tier =2 records were loaded second, and comprise approximately 3million records.I've tried running an execution plan, and while I'm no expert, itappears to me that the index on tier isn't being used, even if I use:tier = CAST(2 as SMALLINT)I'm wondering if anyone can give me ANY advice on how to get anybetter performance out of this SELECT statement?Also, out of curiosity, can a disk defragment have a positive impacton SELECT query performance?Any help very much appreciated!Much warmth,Murray
View 4 Replies
View Related
Sep 19, 2007
Hi,
when i execute a dts to copy one database from one server to another i am getting the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB 'SQL OLEDB' reported an error.Authentication Failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE/DB provider returned message: Invalid authorization specification]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDB Initialize: Initiliaze returned 0x80040e4d: Authentication failed.].
Please help me solve it.
View 1 Replies
View Related
Aug 23, 2007
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies
View Related
Dec 7, 2005
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Nov 29, 2004
Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
View 3 Replies
View Related
Jul 7, 2006
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Jan 29, 2007
As per coding below. Who's can show me how to execute this coding to my sql data source? I already setting my data source, but i can't call my data source to my table. I wanna show the data in my data grid when i type ID Number in textbox but when i click the command button, no record appear. Is it got something error in my coding or no connection between my sql data source? Plssss....help me.....I'm still new in asp.net and no experience with that.
Dim strSQL As String = "SELECT * FROM anggota"
Dim strWhere As String = String.Empty
Dim strCriteria As String() = txtIC.Text.Split(" ")
For Each str As String In strCriteria
If Not String.IsNullOrEmpty(str) Then
If Not String.IsNullOrEmpty(strWhere) Then strWhere &= "AND"
strWhere &= String.Format(" (Column LIKE ' %" &{0}& " % ') ", str)
' %" & myvar & " % '
End If
Next
If Not String.IsNullOrEmpty(strWhere) Then
strSQL &= String.Concat(" WHERE", strWhere)
End If
MsgBox(strSQL)
View 8 Replies
View Related
Dec 7, 2001
Hi!
I need to calculate how many times stored procedure executes and keep the information for several months. What is the best way to do it if I cannot use global variables.
Thank you,
Elena.
View 1 Replies
View Related
Jul 23, 2004
When a SP errors with a deadlock, does the SP stop executing or does it fail the @@error and continue through the next steps in the stored proc?
View 3 Replies
View Related
Feb 17, 2006
Hello,
I need to execute a package from a ASP page.
SQL server and Webserver are on different machines.
It is possible?
If yes How?
Thank you
View 2 Replies
View Related
Aug 26, 2004
Hai..
I have two systems. SQL server 2000 is installed in one system (SERVER ) and the client tools are installed in a seperate system (Client)
My question is from which system should i have to execute the bcp command line utility, from the server or from the client. ?
Thanks in advance...
with regards
Sudar
View 4 Replies
View Related
Dec 13, 2005
Is there a way to tell if a stored proc has ever been executed? I've taken over another system where the developers backed up there procs by creating them with a different name. Of course there's no pattern to the naming convention either. :mad:
View 11 Replies
View Related
Mar 29, 2007
I'm having trouble with dts. I used the dtswizard to create a dts package, yet when I use dtsrun with the package name, I get a message saying that the package cannot be found. I've even copied the .dtsx file into the same folder as dtsrun and still gotten the same message. Can someone shed some light on what I might be messing up. Btw, I'd love to run the dtsrunui, but apparently that file wasn't part of my distribution.
View 3 Replies
View Related
Jan 15, 2004
Can i write a trigger to execute a SP When one row is inerted into the table.If yes,can anyone let me know the syntax.
Thanks.
View 1 Replies
View Related
May 24, 2007
Greetings.
We generate table exports on a SQL Server 2005 instance at irregular intervals. Often when exports are required we have a number of them that need to be run. We've found that the exports job run sequentially. Is there a way of simultaneously executing the jobs ?
Thanks.
alan
View 3 Replies
View Related
Nov 20, 2007
HI all
How can i execute UDF's in MS SQL SERVER 2005 can u any body plz explain me
Regards
subu
View 1 Replies
View Related
Jan 11, 2006
Hi ,
For how long is the value of Execution ID valid? Is it valid after the session also?
Can i store the value of Execution ID in some place and use it after some time/day to get the last excution done on the report?
Thanks
View 5 Replies
View Related
Apr 24, 2008
Hi,
I just installed SQL Server 2005 RS Samples to be able to create and use the Execution Log as it says on
http://technet.microsoft.com/en-us/library/aa964131.aspx#rsmnrptexpf04
but when it comes to the point that I just go and open the Solution named "Execution Log" I don't find it! I don't have it at all and neither the reports which are shown in the solution in the above link such us "Todays Reports.rdl", "Report Size.rdl" or "Longest Running Reports.rdl".
WHy is that? Is it a bug in the Samples file?
Please help me!
If someone has the reports or the entire solution I would appreciate if I could get it!
Thanks
Enrico
View 7 Replies
View Related
Sep 12, 2007
I've got a scheduled job that runs a package every couple of minutes, and I also have a method in an application that calls a stored proc that can also execute the same scheduled job. My question is what will happen if I call the stored proc to execute the job at the same time that the job is executing on its regular schedule. Is it possible that the an instance of the package will execute in parallel?
View 3 Replies
View Related
Jan 10, 2008
Hello Anybody !
I want to get the execution time of a query, I mean I will run the one sql statement like this " SELECT * FROM tblname WHERE field1 = '009' and then I want to get from my program execution time of this query. I think I just keep the sys time before run it and compare with sys time when finished it. But I don't like this one, So, can I get the execution time from sql server by running their sys s-procedure or something like.
Thanks.
View 4 Replies
View Related
Nov 16, 2001
I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:
rebuild the indexes
dbcc indexdefrag
update statistics
I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.
Any ideas would be greatly appreciated.
Thanks.
-Scott
View 1 Replies
View Related
Jan 18, 2002
Is it possible that a stored procedure runs slower when called by an application,and runs faster when executed as 'exec xxxxx' on query analyzer?
It's actually happening to us.Any clue??
thanks.
Di.
View 1 Replies
View Related
May 15, 2001
I have already created package which loads a text file to database.I wanted to execute that package based on the availability of the file using Visual Basic,Perl or VBSCRIPT whichever is easier.Please advise.Thanks
View 4 Replies
View Related
Jun 5, 2001
I want a task to run last after all of the other task have run. How do I ensure that this task will run last?
View 3 Replies
View Related
Dec 21, 2000
Hey guys,
I've got a problem here. I gave execute permission to an
user on a particular stored proc which has simple select queries.
(The tables have no select permissions for that user.) Now, the user was able to run it fine and got the results. When hdynamically creates sql queries inside the proc (Based on the input parameters) sqlserver is checking for the permissions on the tables and got permission denied message.
Is there any solution to avoid this problem?..please let me know
and i'd appreciate that..
thank you very much.
View 3 Replies
View Related
Oct 30, 2000
I wrote a stored procedure that calls a DTS package that imports a file into a SQL Server table, then executes some commands on that table. How can I tell that the DTS package has completed, so that I can continue with the next command? Here is my code:
-- Run the DTS package
EXEC @Result = msdb..sp_start_job @job_name = 'MyJob'
WAITFOR DELAY '00:00:02'
-- Call the table that the package created
SELECT * FROM <imported_table> WHERE ...
I had to insert a WAITFOR statement to make this work. Any other suggestions as to how to know when to continue?
Thank you.
View 4 Replies
View Related
Oct 16, 2000
Execution of a scheduled package fails however, direct execution of the package is successful. The package owner and job owner are the local admin account under which SQLAgent and SQLService are starting.
Ideas?
View 8 Replies
View Related
Oct 2, 2002
Hello,
How do I get quiry result in variable? exp. -
DECLARE @varVariable varchar(50),
@varFld varcahr(50),
@varSel varchar(1000)
SELECT @varVariable = (SELECT Field1 FROM Table1 WHERE Field2 = 1234)
This will give value of Field1 in @varVariable
But how I will get this value in Dynamic Execution?
It will work? -
SET varFld = 'Field1'
SELECT @varVariable = (SELECT @varSel = 'SELECT '+@varFld+' FROM Table1 WHERE Field2 = 1234')
EXEC (@varSel)
PRINT @varVariable
************************************
This will work? what is the alternative?
help please.
ANB
View 4 Replies
View Related
