Tuesday, March 27, 2012
Determine SQL Server service account via script
SQL Server service is using? I'd be doing this using a login in the sysadmi
n
role.
I've searched BOL, the web, and newsgroups. But I haven't found a way.
Thanks
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comJon
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:17635F3D-6CF1-4ECA-941D-DAB9652D81D7@.microsoft.com...
> Is there SQL code that will report the name of the Windows account that
> the
> SQL Server service is using? I'd be doing this using a login in the
> sysadmin
> role.
> I've searched BOL, the web, and newsgroups. But I haven't found a way.
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.comsql
Determine Previous Task Executed
I have the failed path of many tasks pointing to a single Script Task that logs a failed entry into my database. In this script task, I need to be able to determine the task that faliled as I need to write a unique error message depending on the task that failed.
In my VB script, how would I determine which task was the one that failed?
Thank You!!!
a SSIS newbie
There is no way I can see to do that.
Have you investigated the logging available in SSIS before you write your own?
If you do want a task for additional error handling work, then try the on error event handler. Event handlers can host an entire workflow of tasks if you want, and are a better way of capturing this type of event compared to workflow constraints. You have some system variables scoped to the event handler that should give you all the information you want.
|||What I do is add a derived column to each error flow. Then in that derived column, I add a new column, ErrorReason (or whatever you like), which is where I store a small string of what the error was. So then in your script component, you could look at that column and act accordingly.However, it sounds like you can do all of this outside of a script component. (You are inside the data flow, correct? I assume so because you talk of "failed paths.") If you're talking about a failed precedence constraint, then I'll defer to Darren above.|||
Hi AspUser123,
I agree with Darren.
Recently, I implemented a custom error logging mechanism for a client using the onError Event Handler. They wanted the package to continue executing, but capture information about each fault.
Since the Error events bubble, I implemented the entire custom logging solution in the Package onError Event Handler. You can use the SourceName system variable to identify the name of the task that originally generated the error - even if it's nested inside several layers of containers. You can also collect information such as the ErrorDescription and ErrorCode (error number).
In this case I ended up writing the information out to a SQL Server database for storage, reporting, and analysis - but you could easily implement a flat file output. You can use an Execute SQL Task (calling a stored procedure) to write to SQL Server, or add a Data Flow to the onError Event Handler to store the system variable values to a flat file destination.
Hope this helps,
Andy
Wednesday, March 21, 2012
detect existence of datetime fields
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)
Help greatly appreciated.."JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401022017.34730d8@.posting.google.co m...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..
Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.
To identify the datetime fields simply add
a restriction line in the WHERE bit ... such as
and t.name in ('DateTime', 'SmallDateTime')
select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length
from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3|||jonahturnedwhaler@.yahoo.com (JTWhaler) wrote in message news:<ed564348.0401022017.34730d8@.posting.google.com>...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..
Hi Jonah
The attached T-sql will create a database table that contains an entry
for each datetime column in the database
drop table datetime_column
create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @.table_schema as varchar(200)
declare @.table_name as varchar(200)
declare @.column_name as varchar(200)
declare @.query as nvarchar(1000)
DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'
open column_cursor
FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
while @.@.fetch_status = 0
begin
select @.query = ' insert into datetime_column select ' + '''' +
@.table_name + '''' + ',' + '''' + @.column_name + '''' + ',' +
@.column_name + ' from ' + @.table_name
--select @.query
exec sp_executesql @.query
FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
end
close column_cursor
deallocate column_cursor
select * from datetime_column
Monday, March 19, 2012
Detailled error description in a script component (data flow)
Hi,
I'm pretty new in SSIS and i have some problems with error log. I want to get detailled error description in a script component of a dataflow. for the moment I use thooses lines
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
and for unique constraints on a sql table I have this error : The data value violates integrity constraints.
For the same error, if i use an event handler on error, i have more row and the first of them is more explicit (Variable System::ErrorDescription)
An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert duplicate key row in object 'dbo.dimDepot' with unique index 'IX_dimDepot'.".
Is that possible to have a so detailled error text in a script componnent of a data flow? If yes, How?
Or if i use error event how can authorize the dataflow go ahead even if there is error.
thanks for you help
krest
Having written on the inadequate specificity of OLEDB error messaging available to the data flow as compared to that available to the OnError handler from the underlying SQL Native client, the following route was chosen to circumvent the problem.
Use the ADO.NET destination available in the SSIS Integration Services book written by Kirk Haselden.
Two modifications to the provided ADO.NET destination component in the book were made, to do exactly what you're looking for: inject detail error descriptions/codes as columns in the error output, to allow for their use when error row redirection is desired. They are as follows:
1. With the ADO.NET destination component, add two columns to the error output, NativeErrorMessage (type DT_WSTR ) and NativeErrorCode (type DT_I4).
2. Modify the error handler to populate the NativeErrorMessage and the NativeErrorCode from the the SqlException object.
I would post the code, but I'm not certain if that's allowed by the author or not.