Instead Of Trigger - Works In Mgment Console But Fails In Live Test
Feb 4, 2008
Hi, I wasn't sure if this was the correct place for this question since it involves both T-SQL and ODBC but i thought I would start here. A little system information to start. I'm using SQL 2005 on a Windows 2003 server. An application is connection to my database through ODBC and adds records to a table one at a time (no batches).
I have an Instead of Trigger created on a table that tests for the existence of a valid foriegn key. If the key doesn't exist in the related table I want to insert the record into a "bad records" table.
My trigger works perfectly when I add a bogus record to the table directly in the SQL Management Console table view or thorugh an insert query. However, when I try a live test with an application connected via ODBC that is adding records to the table the trigger fails to catch the errors and will not update the "bad records" table. If a record is legitmate (i.e. has a valid foreign key) then the final bit of trigger code fires without a problem.
Does anyone know of a problem with Instead Of Triggers working on multiple tables when connection through ODBC? It just seems weird that the trigger works perfectly in one situation but then doesn't work in another.
Thanks and advance for any help you can give.
-Will
View 1 Replies
ADVERTISEMENT
Dec 12, 2005
This may not be correct request but I have SQL Server Management Studio Express. How do I connect the Adventure Work Database to the console?
View 14 Replies
View Related
Jun 16, 2015
I have two SQL Databases on separate servers, live and test. I have been asked to copy the data from the live system and put it into test. They are SQL Management Studio 2008 running on MS Server 2008R2.
Could a simple backup of the database, then copy that file to the test system and restore the database from that point work or it there more to it?
View 3 Replies
View Related
Feb 1, 2007
Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?
I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.
So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?
View 1 Replies
View Related
Sep 11, 2007
Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.In my Web.Config file, I have this:<connectionStrings> <add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True" providerName="System.Data.SqlClient" /></connectionStrings>I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.I tried this in my Global.asax, but it told me the value was read_only: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True" Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site? Thanks.
View 2 Replies
View Related
Dec 5, 2006
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
Oct 19, 2015
I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.
I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."
I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.
View 2 Replies
View Related
Sep 12, 2007
Hello I am receiving the dreaded mail error listed above. I can send out a test E-mail from Enterprise Manager to operators, but I cannot run this Transact query:
EXEC master.dbo.xp_sendmail @RECIPIENTS = araz***@***.com(removed email address),
@SUBJECT = 'test'
I receive:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
I have stopped/restarted the SQL SERVER AGENT but haven't done much else as I haven't been able to.
Should it work through transact SQL if the test email works from Enterprise Manager?
This is SQL 2000 SP4 running on Win2K in the domain. Thank you.
View 8 Replies
View Related
Dec 18, 2007
Hi
I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.
SQL Server agent is running with same account "xxx"
DTS connects to SQL Server with sa authentication
Job owner is same account "xxx"
Job error log
Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.
I copied the DTS to another one and scheduled it
This time I got the error log
Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.
Please help!
Thanks in advance
View 3 Replies
View Related
Mar 25, 2008
Hi..
I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error
Error: 2008-03-24 13:52:40.22
Code: 0xC0202009
Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.".
I am able to run the package outside the sql job and also connect to the oracle.
I have oracle 9i client installed on the server and sql server is 2005.
Any help would really be appreciated..
View 4 Replies
View Related
Mar 4, 2007
I have a query that works fine but fails as a sproc.
QUERY:
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = 'Brown'
SPROC:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetProfileId]
@UserName nvarchar
AS
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = @UserName
The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0
View 2 Replies
View Related
Jun 7, 2004
I have a SQL DTS package that imports a text file as one wide column into a two column SQL table, one is an identity seed. So essentially I import then parse the data using the index key as it is 3 seperate lines of data. All works fine by running the DTS package using execute. When I schedule this as a job it fails. It indicates that I have additional white space after a column. Any thoughts on why this can ocurr?
View 4 Replies
View Related
Jan 3, 2007
Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.
My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.
I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!
View 4 Replies
View Related
Apr 20, 2004
Hey all,
I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
thanks in advance,
pete
View 2 Replies
View Related
Jul 23, 2005
This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.
View 17 Replies
View Related
Mar 28, 2007
I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails. What could I doing wrong could I be missing a setting in IIS? Any ideas??Here's my webconfig if that helps at all: <?xml version="1.0"?><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <connectionStrings> <add name="studentprofilesConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <roleManager defaultProvider="AspNetWindowsTokenRoleProvider" /> <compilation debug="true" defaultLanguage="c#" /></system.web></configuration>
View 1 Replies
View Related
May 11, 2006
I have a simple ASP.NET application that allows the user to enter a Query in a TextArea and submit that to the database. Queries that contain Integer, String or other filters seem to work fine, but when the Query contains a Date or DateTime I have problems.
Here is an example that works fine in Query analyzer but fails when executed in ASP.NET.
"select top 10 * from Subscribers where StartDate < '09-03-2002'"
In Query Analyzer no problem, in ASP.NET I get this error...
Line 1: Incorrect syntax near ';'
However if the Query uses '=' instead of a '<' or '' then it works without error
"select top 10 * from Subscribers where StartDate ='09-03-2002'"
Any help would be appreciated
View 3 Replies
View Related
Jun 2, 2006
I'm new to DTS packages, but managed to create one that successfully empties a table in SQL Server then imports data from a Foxpro file on another server into it. It runs fine if I execute it from DTS, but fails if I schedule it to run in SQL Server Agent (using the "Schedule Package" option in DTS). I think the relevant portion of the error returned when the job fails is:
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'hrpersnl.dbf' does not exist.
The file does exist. I also tried to execute it from a stored procedure, but got a similar error. Any thoughts on why it runs one way but not the other?
TIA
View 14 Replies
View Related
Jul 20, 2005
Hi,I am having a problem with a replication over a Modem-Connection,which works fine over LAN. Has anyone experienced this problem before?Settings are:2 SQL Servers 2000, SP3on Windows 2000Publischer Database ist about 3GB, Subscriber DB about 1,5GBPublisher and Subscriber are connected via 56Kbit Dial-Up--> The Replication worked fine now this way for about 6 Months now!overall the publisher has about 20 Subscribers all connected viadial-up and they all work fine.Behavior now is like this:- Dial Up works fine- Synch starts- Synch takes some time (Upload works fine)- Synch fails- Error Message(german):Der Prozess konnte die Zeilenmetadaten auf 'Subscriber' nichtabfragen.{call sp_MSgetmetadatabatch(?,?,?)}Allgemeiner Netzwerkfehler. Weitere Informationen finden Sie in derDokumentation über Netzwerke.Der Prozess wurde erfolgreich beendet.If I connect the Subscriber-Server directly to the publisher servervia LAN it all works fine.My thesis is, that there might be some kind of "timeout" or something,as it cant be the dial-up network, because we tried different modemsand different server locations. And it all works fine on most of theother connedted subscribers. Only 2 other very big subscribersexperience the same problem, therefore i assume it could havesomething to to with slow connection speed, timeouts and an inabilityof the SQL server to handle this.Anyone seen this before? Anyone can help me?Thanks and Best Regards,Gerd
View 1 Replies
View Related
May 20, 2008
Hi,
I can use Http connection to Analysis Services 2005 from Excel but when I try to use IIS it fails.
AS and IIS are on different machines and not in the same domain.
In the event log of the server with AS i can see following event:
Event Type: Information
Event Source: MSOLAP ISAPI Extension: \?C:Inetpubwwwrootolapmsmdpump.dll
Event Category: (269)
Event ID: 10
Date: 5/20/2008
Time: 10:17:02 AM
User: N/A
Computer: *******
Description:
The description for Event ID ( 10 ) in Source ( MSOLAP ISAPI Extension: \?C:Inetpubwwwrootolapmsmdpump.dll ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: .
I assume it's permission issue. Excel and IIS must be using different user to connect to AS. I tried with different authentication methods on the iis but without success.
How can I make IIS connect with proper permissions?
Dariusz
View 1 Replies
View Related
Apr 15, 2008
When attempting to connect to Reporting Services (SQL2005) with FQDN, the logon prompt fails after 3 attempts:
http://sqlReportServer.mydomain.net/ReportsManager/Pages/Folder.aspx
If use the IP, it prompts for credentials and works:
http://192.168.0.23/ReportsManager/Pages/Folder.aspx
I have checked the SQL instance name and seems good. RDP by FQDN works fine, etc. Any hints?
CW
--
View 1 Replies
View Related
Mar 18, 2008
Hi,
I am executing a SSIS package using dtexec. 64 bit version of dtexec works fine. But when i use 32 bit version of dtexec, it fails. i have local admin rights. Following is error description. Please help.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:24:30 AM
Error: 2008-03-18 09:24:32.54
Code: 0xC0202009
Source: IMALCRM Connection manager "IMAL SRC"
Description: An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC020801C
Source: Load Fund Detail V_FUND_DETAIL [16]
Description: The AcquireConnection method call to the connection manager "IMA
L SRC" failed with error code 0xC0202009.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC0047017
Source: Load Fund Detail DTS.Pipeline
Description: component "V_FUND_DETAIL" (16) failed validation and returned er
ror code 0xC020801C.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC004700C
Source: Load Fund Detail DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC0024107
Source: Load Fund Detail
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:24:30 AM
Finished: 9:24:32 AM
Elapsed: 2.078 seconds
View 5 Replies
View Related
Mar 1, 2007
I have some code in a SQL CLR stored procedure that calls out to a web service at UPS to obtain tracking information for a package.
The code fails on the last line, in the call to WebRequest.GetRequestStream(), with the following exception:
"System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---
at System.Net.HttpWebRequest.GetRequestStream()
at UPSTracking.TrackShipment(String TrackingUri)"
Here is the relevant code:
WebProxy proxyObject = new WebProxy();
WebRequest request = WebRequest.Create("https://www.ups.com/ups.app/xml/Track");
request.Proxy = proxyObject;
request.Method = "POST";
request.ContentLength = strXMLData.Length;
request.ContentType = "application/x-www-form-urlencoded";
StreamWriter sw = new StreamWriter(request.GetRequestStream());
This same code works just fine when not running in the SQL CLR assembly. The assembly is marked as External, but I've also tried marking it as Unsafe, which did not work either.
Any suggestions on what the problem might be or how to troubleshoot this further would be greatly appreciated.
Thanks,
Steve F.
View 4 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Jul 4, 2007
I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement.
All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing'
As you can see, nothing that complex. The line that is causing the problem is the case:
custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END
all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value.
Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page.
No exceptions are being thrown, it just doesn’t update the data.
Any ideas?
Thanks
View 1 Replies
View Related
Jul 18, 2005
I have an ASP.NET webform:This connection works: "Server=localhost;uid=sa;pwd=;database=pubs"but this connection DOES NOT work: "Server=dnrsqlt1;uid=sa;pwd=;database=pubs"dnrsqlt1 is a sql server my network.Do I have to do something to users ASPNET or IUSER_Machinename on the remote machine
View 6 Replies
View Related
Nov 16, 2004
I am having the most baffling problem with DTS.... :confused:
I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000
The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.
The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.
If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.
View 6 Replies
View Related
Jun 4, 2007
I have a report which I have tested and works fine. now I'm trying to use it as a subreport. the "outer" or main report is very simple: it just has a company standard banner and some header/footer information, and then a single subreport. there is no passing of parameters between main report and sub report. the subreport does have its own parameter to govern its dataset, and provides its own default for that.
The error that I'm getting is this:
[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜subreport1€™: An error has occurred during report processing.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. The data extension returned an error during reading the field.
[rsNone] An error has occurred during report processing.
Of course, this doesn't happen when I execute the subreport by itself. What kinds of things should I be looking at to get to the bottom of this. Thanks!
View 3 Replies
View Related
Apr 4, 2008
I have a stored procedure which is run through MS Access (yuck). It does not appear to fail, but it does not populate certain tables, and is also rather complex. I did not write it, and am trying my best to fix it. However, when I run the same procedure with the same parameters from within Management Studio (database state is the same, I restore from backup before trying out each execution) it populates the tables correctly. I have profiled both executions (from MS and from Access) and it is running with the same NTUsername, and thus the same permissions. The process used to work and then some changes were made to the DB which seem to have broken it from Access. As far as I know, the Access code has not changed, although it may have done so. The proc call from Access uses the same parameters, and does not throw an error, although it does not appear to close the connection to the DB (I'm looking into this). Access uses an ODBC connection to connect to SQL Server.
Any ideas?
View 3 Replies
View Related
Apr 15, 2008
Hi all,
I am having a few problems with Database Mail and wondered if anyone could assist. It sends test mails fine, but when I run the following script:
(I've changed my email address in this post to test@test.com they are accurate in the scripts.)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test Mail',
@recipients = 'test@test.com',
@body = 'Sent by sp_send_dbmail',
@Subject = 'SQL Server Email Test Email';
It gives the following errors.
(from the above script)
mailitem_id = 16
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = SQL Server Email Test Email
body = Sent by sp_send_dbmail
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header = 1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 10:50:03.827
send_request_user = COMPANYTest.Test
sent_account_id = NULL
sent_status = failed
sent_date = 2008-04-15 10:50:04.000
last_mod_date = 2008-04-15 10:50:04.513
last_mod_user = sa
(from the test mail)
mailitem_id = 11
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = Database Mail Test
body = This is a test e-mail sent from Database Mail on UKDEVSQL1
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header = 1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 09:51:46.530
send_request_user = COMPANYTest.Test
sent_account_id = 4
sent_status = sent
sent_date = 2008-04-15 09:51:46.000
last_mod_date = 2008-04-15 09:51:46.610
last_mod_user = sa
sysmail_event_log gives this error for mail item 16 (the scripted email):
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2008-04-15T10:11:41). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )
View 2 Replies
View Related
Aug 16, 2007
I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:12:43 AM
Error: 2007-08-15 11:12:45.63
Code: 0xC0202009
Source: Load Dimension Data Connection manager "ora"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2007-08-15 11:12:45.65
Code: 0xC020801C
Source: CopyCustomers CustomerSource [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2007-08-15 11:12:45.65
The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd:
"c:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
I have managed to find two work arounds, but they are quite ugly:
1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package.
2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'
Both work and use the 64 bit dtexec. Not that elegant though..
CmdExec and the 32 bit version of dtexec works, but is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1?
Any help would be greatly appreciated. Thanks!
Btw, I am using Windows Server 2003 x64 and SQL Server with SP2.
View 9 Replies
View Related
May 30, 2007
I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:
declare @starttime as datetime
declare @endtime as datetime
declare @timezone as integer
declare @date as datetime
set @timezone = 1
set @date = '5/1/2007'
set @starttime = dateadd(hh, @timezone, @date)
set @endtime = dateadd(d, 1, @starttime)
select @Starttime, @endtime from site
Error Message:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.
------------------------------
ADDITIONAL INFORMATION:
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).
Any help would be greatly appreciated!
View 4 Replies
View Related
Mar 31, 2015
We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC
We have checked the configuration on and the source box is set to No authentication required same for destination.
We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.
Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.
View 2 Replies
View Related
