Overview: I am trying to automate remote admin on a db. I have a dev db where tables are added/altered etc. I need to sync the structures. So I create a text file with the templated structure and load that to a table. I then want to run a sql query to load another table with the current stucture, compare them and generate the appropriate sql (ceate table, alter table) to duplicate the structure...
I am to write a stored procedure where I load a file via Bulk Insert(ok there), then I am trying to run a query that returns a bunch of records and I am not sure how to handle the resultset. I need some pointers on how to move thru the resultset...
If someone knows of a good tutorial with this type of functionality, could you point me to it?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
IF (@i_WildCardFlag=0)BEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END PRINT @SQLString SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)' EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric. REQUIREMENT: The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks
IF @@ERROR = 0 BEGIN -- Success. Commit the transaction. Commit Tran END ELSE Rollback Tran
return GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the "SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
I have a DTS setup that copies a couple of databases from one server to another in order to have a hot backup server. When I execute the DTS package from the DTS portion of the Enterprise Manager tree it runs fine. When I schedule it, it fails with the following error:
Much of the data in the Comments field already has carriage return in it. When I use bcp out -c, it uses as a new record to figure out when the new row stats. However, the data in that column has carriage returns! Hence, when it detects the carriage return in the user data(comments) itself, it is pushing the remainder of that text to the 2nd line. Then, when I try to BCP IN, it is trying to push it into ID column!
The text file looks something like this:
ID COMMENTS -- --------- 1 This is a very long comment with a line return now. User hits return here. This should be part of the previous record but gets detected as a new row 2 Some Text 3 Some Text
So, in the BCP IN, it is trying to put 'This should be part of..' into the ID column and I am getting an error.
Is there any way I can get BCP to NOT recognize in user data as end of a record??
We've had RS running on an internal network for a while now and initially installed it with no SSL certificate. We recently decided that we would like to make some reports available through the web, and that we should secure the data with SSL. So, we registered and set up the subdomain, bought and installed the certificate, closed off all but port 443 on the box in question, and opened it up to the world. We tweaked a few options in the RS config files (after reading around):
RSReportServer.config: - SecureConnectionLevel changed from 0 to 2 (recommended) - this changed all but the help file links to https://. - UrlRoot changed from HTTP to HTTPS, and also changed from internal to external name (to match the SSL certificate)
RSWebApplication.config: - ReportServerUrl changed from HTTP to HTTPS and changed from internal to external name.
After this was changed, along with some IIS tweaks, we were able to get to the report manager through the web, and force SSL only. The problem right now is that we are intermittently getting "Underlying connection closed" errors (described here: http://support.microsoft.com/kb/915599). ; The odd thing is that we first get the Windows login prompt, wait about a minute then get the error (which is encapsulated by the Report Manager page style). After a quick browser refresh, the Report Manager appears almost instantly, with no 2nd request for a windows login.
My question: Are there any IIS or RS config settings I can look at, or any log file entries I should look for in order to determine the cause of this problem? My guess at this point is the error has to do with some sort of timeout, but to be honest, I have no clue.
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
I want to be able to intersect many tables. I am building my query from vb code in asp .net based on key fields entered in a search engine box.my query should look like this, which will return the rows that will have the values var, var_2, var_3 in any columns. All three must be in a row for it to be a hit. I cant get this to work in ms sql. I don't know if it supports this feature.select * from t where column1 Like '%var%' or column2 like '%var%' or column3 like '%var%'intersect select * from t where column1 Like '%var_2%' or column2 like '%var_2%' or column3 like '%var_2%'intersectselect * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%' I also googled around and found a where exists... But cant seem to figure out how to do multiple tables:select * from t where exists (select * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%') i would like to add multiple conditions to the where exists table. Could anyone please tell me what I am doing wrong.
Taking the advise from one of the postings I removed the setting for checkpoint log on checkpoint. I have set up a batch job that does a dbcc checktable (syslogs) and a dump transaction with no_log every 15 minutes. We are a development shop and do not need the transaction log, I would have liked to use the truncate log on checkpoint. I found that the transaction log seems to grow indefinitely by viewing DBCC sqlPerf and sp_spaceused if I do not first issue the checktable command. Why is this needed? This is the same problem I had with the truncate log on checkpoint option. Has anyone else encountered a similar problem? We have an application that does single row inserts multithreaded at a rate of 2500 rows a second. Without performing the dbcc checktable the log filled to over 80% in a 45 minute period. Running the stored procedure with the two commands the logs stays under 40%.
Trying to get my jobs to send mail when job fails. Should be easy but it's giving me headache
Had a whole slew of issues. Outlook is installed with a n outlook mail profile set up that can send mail in outlook. I can create a SendMail DTS and execute it to send mail
Email works in these scenarios 1. I create a DTS package in SQL Server with just SendMail with the same Profile "ABC" and click Execute and it sends 2. I can send using Outlook with the same profile "ABC" 3. I can run DTSRun with command prompt calliing the DTS package and it sends fine
However it wont send in these 3 scenarios (where I need it!):
1. I go to Operator, and put my Profile "ABC" in the Email Name, click Test and I get this error "Error 22022: SQLServerAgent Error: The SQLSErverAgent mails ession is not running; check the mail profile and/or the SQLServerAgent service startup account in the SQLSErverAGent Properties sQLServer Agent is running
2. If I add the DTS Package "EmailTest" to one of my jobs as a step to go to if the 1st step fails, nothing gets sent
3. In JObs -- Notificatioin, If I set the E-maill operator to my operator, nothing gets sent
I set the Mail Profile to the Mail Profile (in SQL Server AGent' properties) However when I click Test, I get this error: Error 0: Cannot load the DLL sqlmap70.dll, or one of the DLLs it references
I am using SQLServer Version 8.00.194, OS Windows 2003
I don't know if anyone can help me but I have two issues with SQL Server 2000 SP4 (version 8.00.2039 - Desktop Engine) running on W2K and W2K3. I'm also running SQLXML 3.0 (msxml2.dll version is 8.30.9530.0).
Is it me or is sp_xml_preparedocument a crippled fat dog that is blind?...not that I have anything against crippled fat dogs that are blind :)
In all the stored procs I have developed, I pass a text var as an input parameter and return an IStream to ADO (using an sqlxml provider) in COM+. All has been very well and fine...until the passed text parameter resembles a data object of any decent size.
The first error I was noticing was a "XML Parsing Error: not enough storage is available to complete this operation". Well, I thought I would debug logically in a step fashion and just prepare the doc first and then do a return and then do a return on the next segment of code to find out where the issue is. I was amazed to find that sp_xml_preparedocument is taking 7 seconds to load a simple 1MByte text input var and around three minutes to load a 7 MByte file.
I believe these long load times are causing issues with transaction timouts etc so I thought I would try to solve the speed issue with sp_xml_preparedocument and then see if the "XML Parsing Error" continues.
So, my first question is:
Should sp_xml_preparedocument take 7 seconds to load a 1MByte text variable and nearly three odd minutes to load a 7 MByte file? Surely there is something wrong somewhere?
I'm also running these tests on two machines - one is 2 GHz and the other is 2.4 GHz P4's.
SELECT T1.* FROM Cust_Table T1 INNER JOIN ( SELECT Family_Name FROM Cust_Table WHERE Cust_Name IN ('Billy', 'John') AND RowNum < 100 GROUP BY Family_Name HAVING COUNT(*) > 1 ) T2 ON T1.Family_Name= T2.Family_Name WHERE RowNum < 100
( This code above finds all the familys that contain either multiple billys and/or multiple Johns and displays all the duplicates ordered by the family_name. )
The problem is that what I want it to do is search through the whole table and find within each Family (Family_ID) who has both a sibling called Billy AND John (Cust_Name) wether they have multiple johns and multiple billys I don't mind as long as they have a minimum of 1 of each. I then want to just output all those examples only not anything else. An example of the table is bellow, I hope this helps. Thanks for your advice.
BEFORE Family_Name CUST_Name Bruce Billy Bruce John Bruce Mike Bruce Oli Smith Billy Smith Billy Harold John
AFTER Family_Name CUST_Name Bruce Billy Bruce John
Trying to get my jobs to send mail when job fails. Should be easy butit's giving me headacheHad a whole slew of issues. Outlook is installed with a n outlookmail profile set up that can send mail in outlook. I can create aSendMail DTS and execute it to send mailEmail works in these scenarios1. I create a DTS package in SQL Server with just SendMail with thesame Profile "ABC" and click Execute and it sends2. I can send using Outlook with the same profile "ABC"3. I can run DTSRun with command prompt calliing the DTS package andit sends fineHowever it wont send in these 3 scenarios (where I need it!):1. I go to Operator, and put my Profile "ABC" in the Email Name,click Test and I get this error "Error 22022: SQLServerAgent Error:The SQLSErverAgent mails ession is not running; check the mail profileand/or the SQLServerAgent service startup account in theSQLSErverAGent PropertiessQLServer Agent is running2. If I add the DTS Package "EmailTest" to one of my jobs as a stepto go to if the 1st step fails, nothing gets sent3. In JObs -- Notificatioin, If I set the E-maill operator to myoperator, nothing gets sentI set the Mail Profile to the Mail Profile (in SQL Server AGent'properties)However when I click Test, I get this error:Error 0: Cannot load the DLL sqlmap70.dll, or one of the DLLs itreferencesI am usingSQLServer Version 8.00.194,OS Windows 2003Help!
I'm baffled by this error but I'm guessing once someone points it out, it will a oh duh! moment. I have a proc. I want to get the results of the proc into a temp table. I've tried both SELECT INTO and INSERT INTO. Both give me the error "Invalid object name #HACK" which is the tmp table name I've used.
I'm experiencing a problem using transactions within a package, and would be grateful if anyone can help out. A search on the forum has turned up a number of similar posts but I don't think any of them deal specifically with my problem (and I didn't find an existing solution), but apologies if this is considered a duplicate!
I have two Sequence Containers in a package, each of which contains a number of Data Flow Tasks responsible for copying data from one SS2k5 database to another (on the same server). There is a precedence constraint between the two Sequence Containers (within each container the Data Flow Tasks run in parallel, although I don't think this is relevant).
I need each Sequence Container to execute transactionally i.e. within each Container the Data Flows must either all succeed or all fail. However I don't want the package as a whole to execute transactionally i.e. I don't want the two Sequence Containers within a single transaction, but rather to each start a transaction of their own. So, accordingly I have set the TransactionOption property of the package to Supported, and the TransactionOption property of each of the Sequence Containers set to Required. Each Data Flow Task has TransactionOption of Supported.
All is well when I run the package by itself, but unfortunately within my ETL this package is invoked as the child of another package, and when this happens the first sequence container fails with error message:
"The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.""
Some quick Googling turned up the following MS Support article that seems to pretty much describe my situation and acknowledge it as a SSIS bug:
http://support.microsoft.com/?kbid=914375
However there is a bit of a problem: the article claims this problem is fixed in Service Pack 1 ... but I'm already running Service Pack 1 (Build 2047). Additionally, the alternative workaround given doesn't help me (unless I'm misunderstanding) since I don't want the two Sequence Containers running in the same transaction and so can't set their TransactionOption to Supported and rely on them joining an existing transaction.
I am aware of the post-SP1 SS hotfix but had some problems with my AS installation when I upgraded to this a while back, so I'm keen to stay with my fresh SP1 unless someone can assure me that the hotfix addresses this issue (and I can't see any mention of an issue such as this in the hotfix notes).
I guess an alternative workaround would be to use native SQL Server transactions instead of MSDTC by including explicit T-SQL transaction commands within my Sequence Containers (and RetainSameConnection on the Connection Manager), but I'm reluctant to modify my packages to do this unless necessary, since I believe the intrinsic transaction support should be able to cover what I'm trying to achieve?
Has anybody else experienced this problem even on SP1, or am I perhaps misunderstanding how to use transactions in SSIS (this is the first time I've used them)?
