If you like to use the keys for HOME, END, PAGE UP, PAGE DOWN in the Terminal you have to change following setting under Terminal – Preferences – Settings – Keyboard:
This sets the right escape key sequences.
Problem: Mac OSX does not offer a tool to copy or backup the BootCamp Windows partition. E.g. the Disk Utility is not able to copy or backup NTFS partitions. Besides that the selection of Disk Management tools like partition manager is from my perspective very poor.
Solution: Happily Mac OSX is based on Unix. This offers the possibility to use the tool dd. Following steps need to be followed:
diskutil list
You will see a table with four columns showing your connected disks and partitions. Locate the BootCamp partition, and make a note of its identifier, e.g. “/dev/disk0s3“.
diskutil unmount /dev/disk0s3
Make sure to replace “disk0s3” with your BootCamp partition identifier.
diskutil unmount /dev/disk1s1
Make sure to replace “disk1s1” with your external disk partition identifier. Do not unmount that partition if you intend to create an image from the BootCamp partition.
sudo dd if=/dev/disk0s3 of=/dev/disk1s1
Make sure to replace “disk0s3” with your BootCamp partition identifier and “disk1s1” with your external hard drive partition identifier.
sudo dd if=/dev/disk0s3 of=~/Bootcamp.iso
Make sure to replace “disk0s3” with your BootCamp partition identifier.
It is recommended to compress the image in order to save disk space. Instead of using above mentioned command, please use that following.
sudo dd if=/dev/disk0s3 | gzip > ~/BootCamp.iso.gz
Problem:
PS3 Controller (Playstation 3 Gamepad) is not working out of the box with Fifa 12.
Solution:
1. Download following zip (http://www.mediafire.com/?uxcso3f03buh9jq) and extract contents to “/Applications/FIFA12.app/Contents/Resources/transgaming/c_drive/Program Files/EA Sports/EA SPORTS FIFA Soccer 12/Game” for Mac or “C:/Program Files/EA Sports/EA SPORTS FIFA Soccer 12/Game” for PC.
2. Replace Contents of buttonData.ini (in same folder) with following Code.
AddController "myController"
AddAlias ""
AddAlias "XBOX 360 For Windows (Controller)"
AddAlias "XBox 360 For Windows (Controller)"
AddAlias "Controller (XBOX 360 For Windows)"
AddAlias "Controller (Xbox 360 Wireless Receiver for Windows)"
AddAlias "Xbox 360 Wireless Receiver for Windows"
AddAlias "XInput Controller"
AddAlias "PLAYSTATION(R)3 Controller"
AddMap XENON_LEFT_ANALOG_STICK_RIGHT VB_AI_LS_RIGHT
AddMap XENON_LEFT_ANALOG_STICK_UP VB_AI_LS_UP
AddMap XENON_LEFT_ANALOG_STICK_LEFT VB_AI_LS_LEFT
AddMap XENON_LEFT_ANALOG_STICK_DOWN VB_AI_LS_DOWN
AddMap XENON_RIGHT_ANALOG_STICK_RIGHT VB_AI_RS_RIGHT
AddMap XENON_RIGHT_ANALOG_STICK_UP VB_AI_RS_UP
AddMap XENON_RIGHT_ANALOG_STICK_LEFT VB_AI_RS_LEFT
AddMap XENON_RIGHT_ANALOG_STICK_DOWN VB_AI_RS_DOWN
AddMap XENON_BUTTON_DPAD_RIGHT VB_AI_LDPAD_RIGHT
AddMap XENON_BUTTON_DPAD_UP VB_AI_LDPAD_UP
AddMap XENON_BUTTON_DPAD_LEFT VB_AI_LDPAD_LEFT
AddMap XENON_BUTTON_DPAD_DOWN VB_AI_LDPAD_DOWN
AddMap XENON_LEFT_ANALOG_STICK_RIGHT VB_FE_LS_RIGHT
AddMap XENON_LEFT_ANALOG_STICK_UP VB_FE_LS_UP
AddMap XENON_LEFT_ANALOG_STICK_LEFT VB_FE_LS_LEFT
AddMap XENON_LEFT_ANALOG_STICK_DOWN VB_FE_LS_DOWN
AddMap XENON_RIGHT_ANALOG_STICK_RIGHT VB_FE_RS_RIGHT
AddMap XENON_RIGHT_ANALOG_STICK_UP VB_FE_RS_UP
AddMap XENON_RIGHT_ANALOG_STICK_LEFT VB_FE_RS_LEFT
AddMap XENON_RIGHT_ANALOG_STICK_DOWN VB_FE_RS_DOWN
AddMap XENON_BUTTON_DPAD_RIGHT VB_FE_LDPAD_RIGHT
AddMap XENON_BUTTON_DPAD_UP VB_FE_LDPAD_UP
AddMap XENON_BUTTON_DPAD_LEFT VB_FE_LDPAD_LEFT
AddMap XENON_BUTTON_DPAD_DOWN VB_FE_LDPAD_DOWN
AddMap XENON_BUTTON_B VB_AI_B
AddMap XENON_BUTTON_A VB_AI_A
AddMap XENON_BUTTON_X VB_AI_X
AddMap XENON_BUTTON_Y VB_AI_Y
AddMap XENON_BUTTON_B VB_FE_CANCEL
AddMap XENON_BUTTON_A VB_FE_SELECT
AddMap XENON_BUTTON_X VB_FE_X
AddMap XENON_BUTTON_Y VB_FE_Y
AddMap XENON_BUTTON_LS VB_AI_LB
AddMap XENON_BUTTON_RS VB_AI_RB
AddMap XENON_BUTTON_LT VB_AI_LT
AddMap XENON_BUTTON_RT VB_AI_RT
AddMap XENON_BUTTON_LS VB_FE_LB
AddMap XENON_BUTTON_RS VB_FE_RB
AddMap XENON_BUTTON_LT VB_FE_LT
AddMap XENON_BUTTON_RT VB_FE_RT
AddMap XENON_BUTTON_BACK VB_AI_BACK
AddMap XENON_BUTTON_START VB_AI_START
AddMap XENON_BUTTON_L3 VB_AI_L3
AddMap XENON_BUTTON_R3 VB_AI_R3
AddMap XENON_BUTTON_BACK VB_FE_HELP
AddMap XENON_BUTTON_START VB_FE_START
AddMap XENON_BUTTON_L3 VB_FE_L3
AddMap XENON_BUTTON_R3 VB_FE_R3
AddController "Controller_025"
AddAlias "AUTO PAD"
AddAlias "Dual Trigger 3-in-1"
AddAlias "Dual Trigger 3-in-1 Rumble Force"
AddAlias "FireStorm Wireless Gamepad"
AddAlias "Firestorm Wireless Gamepad"
AddAlias "Logitech Cordless RumblePad 2"
AddAlias "Logitech Cordless RumblePad 2 USB"
AddAlias "Logitech Dual Action"
AddAlias "Logitech Dual Action (USB)"
AddAlias "Logitech Dual Action USB"
AddAlias "Logitech Rumblepad 2 USB"
AddAlias "Logitech RumblePad 2 USB"
AddAlias "Run 'N' Drive Rumble Force"
AddAlias "Run 'N' Drive Wireless"
AddAlias "Run'N' Drive"
AddAlias "Run'N' Drive Rumble Force"
AddAlias "Run'N' Drive Wireless"
AddAlias "Run'N'Drive 3-in-1 Rumble Force"
AddAlias "Saitek P2900 Game Pad"
AddAlias "Saitek P2900 Pad"
AddAlias "Saitek P2900 USB Pad"
AddAlias "Saitek P2900 Wireless Pad"
AddAlias "SpeedLink D-Struct"
AddAlias "SpeedLink Strike Cubed"
AddAlias "SpeedLink Turbo Pad"
AddAlias "T Mini Wireless"
AddAlias "T-wireless 3-in-1 Rumble Force"
AddAlias "TURBO PAD"
AddAlias "Thrustmaster FireStorm(TM) Wireless"
AddMap PC_CONTROL_BUTTON02 VB_AI_A
AddMap PC_CONTROL_BUTTON02 VB_FE_SELECT
AddMap PC_CONTROL_BUTTON03 VB_AI_B
AddMap PC_CONTROL_BUTTON03 VB_FE_CANCEL
AddMap PC_CONTROL_BUTTON04 VB_AI_Y
AddMap PC_CONTROL_BUTTON04 VB_FE_Y
AddMap PC_CONTROL_BUTTON01 VB_AI_X
AddMap PC_CONTROL_BUTTON01 VB_FE_X
AddMap PC_CONTROL_BUTTON05 VB_AI_LB
AddMap PC_CONTROL_BUTTON05 VB_FE_LB
AddMap PC_CONTROL_BUTTON06 VB_AI_RB
AddMap PC_CONTROL_BUTTON06 VB_FE_RB
AddMap PC_CONTROL_BUTTON07 VB_AI_LT
AddMap PC_CONTROL_BUTTON07 VB_FE_LT
AddMap PC_CONTROL_BUTTON08 VB_AI_RT
AddMap PC_CONTROL_BUTTON08 VB_FE_RT
AddMap PC_POV_0_UP VB_AI_LDPAD_UP
AddMap PC_POV_0_UP VB_FE_LDPAD_UP
AddMap PC_POV_0_LEFT VB_AI_LDPAD_LEFT
AddMap PC_POV_0_LEFT VB_FE_LDPAD_LEFT
AddMap PC_POV_0_DOWN VB_AI_LDPAD_DOWN
AddMap PC_POV_0_DOWN VB_FE_LDPAD_DOWN
AddMap PC_POV_0_RIGHT VB_AI_LDPAD_RIGHT
AddMap PC_POV_0_RIGHT VB_FE_LDPAD_RIGHT
AddMap PC_CONTROL_BUTTON11 VB_AI_L3
AddMap PC_CONTROL_BUTTON11 VB_FE_L3
AddMap PC_CONTROL_BUTTON12 VB_AI_R3
AddMap PC_CONTROL_BUTTON12 VB_FE_R3
AddMap PC_AXIS_0_UP VB_AI_LS_UP
AddMap PC_AXIS_0_UP VB_FE_LS_UP
AddMap PC_AXIS_0_LEFT VB_AI_LS_LEFT
AddMap PC_AXIS_0_LEFT VB_FE_LS_LEFT
AddMap PC_AXIS_0_DOWN VB_AI_LS_DOWN
AddMap PC_AXIS_0_DOWN VB_FE_LS_DOWN
AddMap PC_AXIS_0_RIGHT VB_AI_LS_RIGHT
AddMap PC_AXIS_0_RIGHT VB_FE_LS_RIGHT
AddMap PC_AXIS_2_UP VB_AI_RS_UP
AddMap PC_AXIS_2_UP VB_FE_RS_UP
AddMap PC_AXIS_2_LEFT VB_AI_RS_LEFT
AddMap PC_AXIS_2_LEFT VB_FE_RS_LEFT
AddMap PC_AXIS_2_DOWN VB_AI_RS_DOWN
AddMap PC_AXIS_2_DOWN VB_FE_RS_DOWN
AddMap PC_AXIS_2_RIGHT VB_AI_RS_RIGHT
AddMap PC_AXIS_2_RIGHT VB_FE_RS_RIGHT
AddMap PC_CONTROL_BUTTON10 VB_AI_START
AddMap PC_CONTROL_BUTTON10 VB_FE_START
AddMap PC_CONTROL_BUTTON09 VB_AI_BACK
AddMap PC_CONTROL_BUTTON09 VB_FE_HELP
All in all, you need the “x360ce.ini”, “xinput1_3.dll” and the modified “buttonData.ini” in that folder.
Very hot topic: After the difficulties with Novell and Attachmate the developers who created great products like mono, mono touch, monodroid, .. will continue their work within the new firmed company Xamarin (http://www.xamarin.com).
All the best!
[Source: http://tirania.org]
I discovered the tool WWW SQL Designer (created by Ondrej Zara) years ago and was happy to notice that it still exists.
Great work!
SQL Online ERD/DB Modeler
[Source: http://ondras.zarovi.cz/sql/demo/]
Common Functions
Ctrl + r Refresh active panel
Ctrl + t Mark single file
Ctrl + x c Opens chmod dialog for marked file
Ctrl + x o Opens chown dialog for marked file
Alt + ? Opens search dialog
Ctrl-x a Open VFS list. If a ftp session times out, you can use this to free the open vfs so you can log in again.
Panel Functions
TAB Switch focus between left and right panel
Insert Marks or removes mark on file(s)
Alt + g Marks first file or directory in active panel
Alt + r Marks middle file or directory in active panel
Alt + j Marks last file or directory in active panel
Alt + s Incremental search
Ctrl + / Look for filename given in last line of active panel (not whole screen), and jumps on first file
* Marks removes marking for all files in active panel (not directories)
+ (Plus) Brings up the input box where it can be given regular expression. All files with names that fill condition(s) in regular expression will be marked.
\ (Backslash) Remove marking for more data (opposite to + )
Shell Functions
Alt + Enter Copies selected filename in command line
Ctrl + Shft + Enter Copies full path of selected file in the command line
Alt + H Shows command line history
Function Keys
F1 Help
F2 Opens user menu
F3 View selected file content
F4 Opens file in internal text editor
F5 Copies selected file. Default is to another panel, but it asks first.
F6 Moving file. Default is to another panel, but it asks first.
F7 Make directory.
F8 Delete file or directory.
F9 Opens main menu at the top of the screen.
F10 Ends current action; editor, viewer, dialog window or ends mc program.
The IsNumeric function has a very variable interpretation algorithm. In order to get only numeric values following statement should be used:
CASE WHEN (NUM_VALUE LIKE '%[0-9]%' AND ISNUMERIC(NUM_VALUE) = 1) THEN CAST(NUM_VALUE AS FLOAT) ELSE NULL END AS NUM_VALUE
How to add a Excel Export Link to a Report
Add an item (e.g. TextBox, Image, etc.) and open the properties dialog. Under the navigation tab use the setting Go to URL and add following comand.
=Globals!ReportServerUrl.ToString() + "/Pages/ReportViewer.aspx?"
+ System.Web.HttpUtility.UrlEncode("https://ReportServer/ReportFolder/Report.rdl")
+ "&rs:Command=Render&rs:Format=EXCEL&rc:OmitFormulas=true"
+ "&[Parameter_Name1]=" + Join(Parameters![Parameter_Name1].Value, "&[Parameter_Name1]=") --> Multivalue Parameter
+ "&[Parameter_Name2]=" + System.Web.HttpUtility.UrlEncode(Parameters![Parameter_Name2].Value) --> Normaler Paraemter
It is important to use the HttpUtility.UrlEncode() function in order to pass the correct values.
In the properties dialog of the report add a reference to the System.Web assembly.
There are two options executing SSIS packages:
- xp_cmdshell command (not recommended for security reasons)
- sp_start_job command
The main difference between both options is the execution method. The xp_cmdshell command is a synchronous call and the sp_start_job command is an asynchronous call.
xp_cmdshell command (synchronous)
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "PackageNameWithFullPath.dtsx"'
sp_start_job (asynchronous)
[msdb].dbo.sp_start_job @job_name='JobName'
SELECT
[server],
[start_execution_date],
[stop_execution_date],
[run_date],
[run_duration],
[run_status],
[message]
FROM
MSDB.DBO.SYSJOBS Z
INNER JOIN
MSDB.DBO.SYSJOBACTIVITY A
ON Z.JOB_ID = A.JOB_ID
INNER JOIN
(
SELECT
MAX(SESSION_ID) AS SESSION_ID
FROM
MSDB.DBO.SYSSESSIONS
) AS B
ON A.SESSION_ID = B.SESSION_ID
LEFT JOIN
MSDB.DBO.SYSJOBHISTORY C
ON A.JOB_HISTORY_ID = C.INSTANCE_ID
WHERE
Z.NAME = 'JobName'
There is following way to make the call of sp_start_job synchronous.
sp_start_job (synchronous)
ALTER PROCEDURE [dbo].[AGENT_JOB_CHECK2]
-- Add the parameters for the stored procedure here
DECLARE @job_name nvarchar(100)='',
DECLARE @maxwaitmins int = 5
AS
BEGIN
set NOCOUNT ON;
set XACT_ABORT ON;
BEGIN TRY
declare @running as int
declare @seccount as int
declare @maxseccount as int
set @maxseccount = 60*@maxwaitmins
set @seccount = 0
set @running = 0
declare @job_owner sysname
declare @job_id UNIQUEIDENTIFIER
set @job_owner = SUSER_SNAME()
-- get job id
select @job_id=job_id
from msdb.dbo.sysjobs sj
where sj.name=@job_name
-- invalid job name then exit with an error
if @job_id is null
RAISERROR (N'Unknown job: %s.', 16, 1, @job_name)
-- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
declare @xp_results TABLE ( job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- start the job
declare @r as int
exec @r = msdb..sp_start_job @job_name
-- quit if unable to start
if @r<>0
RAISERROR (N'Could not start job: %s.', 16, 2, @job_name)
-- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
WAITFOR DELAY '0:0:10';
set @seccount = 10
-- check job run state
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
while @running<>0 and @seccount < @maxseccount
begin
WAITFOR DELAY '0:0:10';
set @seccount = @seccount + 10
delete from @xp_results
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
end
-- result: query
SELECT
[server],
[start_execution_date],
[stop_execution_date],
[run_date],
[run_duration],
[run_status], -- 0: failed, 1: success, null: running
[message]
FROM
MSDB.DBO.SYSJOBS Z
INNER JOIN
MSDB.DBO.SYSJOBACTIVITY A
ON Z.JOB_ID = A.JOB_ID
INNER JOIN
(
SELECT
MAX(SESSION_ID) AS SESSION_ID
FROM
MSDB.DBO.SYSSESSIONS
) AS B
ON A.SESSION_ID = B.SESSION_ID
LEFT JOIN
MSDB.DBO.SYSJOBHISTORY C
ON A.JOB_HISTORY_ID = C.INSTANCE_ID
WHERE Z.NAME = @job_name
-- result: not ok (=1) if still running
--if @running <> 0
--return 0
--else
--return 1
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- original error number.
@ErrorSeverity, -- original error severity.
@ErrorState, -- original error state.
@ErrorProcedure, -- original error procedure name.
@ErrorLine -- original error line number.
);
END CATCH
END
[Source: http://blog.boxedbits.com/archives/124]
Kurzanleitung für Mercedes W123: Lüftungsgitter ausbauen und wechseln
W123 Luftgitter: Bild 1
W123 Luftgitter: Bild 2
W123 Luftgitter: Bild 3
W123 Luftgitter: Bild 4
In the following you find a compact catalog of steps which should be considered in a SQL Server environment.
It is a conclusion of information from different sources.
Check List
Installation & Patching
Configuration: Surface Area Reduction
SQL Server Agent
Security: Authentication (general)
Security: Authentication (administrative access)
Security: Authorisation
Security: Scanner Tools
Security: Password Policy
Database Properties
Schema Properties
Catalog Views
Remote Data Source Execution
Execution Context
Data Encryption
Auditing
Fazit
My recommendation is to use following Test Protocol in order to check the several steps.
Download: SQL Server: Check List Best Practices
Wireshark Logo
Wireshark is the successor application of Ethereal. Wireshark is a free (GNU General Public License) network traffic analyser (packet sniffer).
This tool is very useful to test encrypted connections of own applications or to analyse active connections and data flows on the own desktop.
Here is an application screenshot:
The application layout consists of 3 parts (windows) – packet list, packet details and packet bytes.
Wireshark Application Screenshot
A sample ICQ data flow:
This data flow contains the message: “Gratulation zum Motoradführerschein 
“
ICQ Data Flow
Supported Systems:
Wireshark runs on Unix-like systems (e.g. Linux, Solaris, HP-UX, FreeBSD, NetBSD, OpenBSD and Mac OS X) and on Microsoft Windows.
[Source: Wireshark]
Linked SQL Server (applies to SQL Server 2005 / SQL Server 2008 / later)
It can happen that an application may need data stored in second database which is not located on the same instance of SQL Server. That instance could be on the same physical machine or it could be on another machine. There are many solutions to this scenario, but it depends on your requirements which solution you should choose. If there is no need to transport a huge amount of data between the two SQL Server instances then you should consider to register the second instance as Linked SQL Server into the first instance.
The advantage is that the Linked Server can provide seamlessly data access to the application. By the way, Linked Servers can be also used to register Non-SQL-Server instances.
Basic Architecture Example
The following illustration shows a basic architecture sample. As you can see the Client is only accessing the Application Server and the Application Server is only accessing the first SQL Server.
Linked SQL Server: Basic Architecture
This means the Application Server is submitting only one query to the first SQL Server instance and gets the result back only from the the same SQL Server instance. The data retrieval from the second SQL Server instance is processed by the first SQL Server instance.
More detailed: If the application running on the Application Server submits a query to the frist SQL Server instance that needs to retrieve data from a database hosted on the second SQL Server instance, then a so called Distributed Query is fired. An Example for such a query:
SELECT SERVER2.[DB].[SCHEMA].[TABLE].[FIELD]
FROM SERVER2.[DB]
In this sample the SERVER2 (= Linked Server Registration name on SERVER1) identifies that I am starting a distributed query.
How to setup the Linked Server
You can register a Linked Server instance within Management Studio as the following illustration shows.
Linked SQL Server: Management Studio
For a greater flexibility I recommend to use the stored procedure.
EXEC master.dbo.sp_addlinkedserver
@server = N'REGISTRATION_NAME',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'SERVER\INSTANCE',
@provstr=N'',
@catalog = 'DATABASE_NAME'
Linked Server Options: Server Options
Following Server options are available:
Linked Server Options: Security
For logins which are not defined in the mapping you can specify the behavior for the connection to the linked server. There are four different options that are available by choosing the corresponding radio button.
Encryption of Connection stream
SQL Server is able to encrypt the connection between Client and Server. Since SQL Server 2005 you do not necessarily need to use SSL certificates. However, it makes sense to have certificates, because the Client can not check the identity of the Server. By the way, if you use a certificate which was generated from the Server itself then it will not be trustful.
The Login packets are always encrypted since SQL Server 2005, if the Client supports it. Encrypted communication between Client and Server is supported since MDAC 2.6 and third party Client tools should be ckecked. It is important to know that encryption takes additional CPU time and that encrypted data streams cannot be compressed anymore.
The encryption option can be activated in the Configuration Manager (SQL Server Network Cobnfiguration). Choosing the Properties dialog for an Instance you can set the option “Force Encryption” to true.
With this option set to true the SQL Server instance only accepts encrypted connections. If you want to have both connection options (encrypted / non-encrypted) then you should not set this option to true.
If you do not choose a certificate (under certificate tab) then SQL Server 2005 (or later) generates a self-signed certificate. This means that the communication between Client and Server is encrypted and that spoofing of data is not possible anymore.
Encryption of Linked Server Connection Stream
To enable the encryption of a Linked Server connection stream you should use the provider string option “Encrypted=YES”. The following stored procedure includes this option.
EXEC master.dbo.sp_addlinkedserver
@server = N'REGISTRATION_NAME',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'SERVER\INSTANCE',
@provstr=N'Encrypt=yes;',
@catalog = 'DATABASE_NAME'
The Linked SQL Server connection is now encrypted using a self-signed certificate or if available a Server certificate.
How to check encrypted connection for SQL Server
I recommend the tool Wireshark (former known as Ethereal) to check the packages and look into the data. Just run this tool, capture a stream and fire a SELECT statement.
Conclusion
Registering another SQL Server instance as Linked Server allows you to submit T-SQL statements on one SQL Server instance, which retrieves data from a second instance. Moreover it is possible to fire linked statements so that you can join data between several instances.
Manchmal ist es schwierig, jemanden den Geschmack von Wein zu beschreiben. Eine coole Suchmaschine Weinsuchmaschine hilft dabei: aromicon.
aromicon ist eine Geschmackssuchmaschine für Wein. Sie können Weine nach den Suchkriterien Wunscharomen, passende Speisen, Süße, Rebsorte, Tannine und Region anzeigen lassen. Die Suche erfolgt in tausenden von Weinen aus zahlreichen Onlineshops.
[Quelle: http://www.aromicon.com]
If you experience following error “Executed as user: MyDomain\SQLServer. The package execution failed. The step failed.”
then check that your Service User for the SQL Server Agent has full file permissions to all packages, config files and log files!
Moreover this service user should be member of the SQL Server DTS Group.
Importing Date values e.g. from Flat Files it can happen that the format of the date is different to the Host System date formatting of the regional settings. Especial the date setting for English (UK: day/month/year) and English (US: month/day/year) are often very problematic.
In SSIS a solution for this issue can be realized with a ScriptComponent as follows.
Dim dt As DateTime
Dim culture As Globalization.CultureInfo
culture = Globalization.CultureInfo.CreateSpecificCulture("de-DE")
Try
If DateTime.TryParse(CStr(Row.BIRTHDAY_STR), culture, Globalization.DateTimeStyles.None, dt) Then
Row.BIRTHDAY_DATE = dt
Else
Row.BIRTHDAY_DATE_IsNull = True
End If
Catch ex As Exception
Row.BIRTHDAY_DATE_IsNull = True
End Try
This example should help you to import the dates of different regional settings.
Traurig aber wahr: Michael Jackson ist verblasst.
Seine Musik begleitet uns aber weiter und momentan spiegelt sich dies stark in den Verkauscharts (z.B. bei Amazon) wieder.
Amazon Top 10 Bestsellerliste
[Quelle: Amazon.de]
Here I would like to show you a solution for following problem.
One source row coming from Excel contains several values within one column. Each one of these values should be reflected in a separate row in the data destination.
So the basic problem is that there we have a “list column” whose contents should be result in separate rows.
To make the problem more complex: The number of values in the “list column” is variable and there is a second column which contains corresponding values.
Excel Source
Excel Sheet
solution: SSIS Script Component
To implement a solution for the problem we use the most adequate item – the Script Component.
SELECT
F1,F2,F3
FROM
[sheet1$B1:D5]
KEY, PERSON, DEPARTMENT.
Excel Source
Derived Column
In order to replace the CRs with commas we replace the imported columns with following formula:
REPLACE(REPLACE(REPLACE(REPLACE(PERSON,"\n",",")," ",""),",,",","),",,",",")
REPLACE(REPLACE(REPLACE(REPLACE(DEPARTMENT,"\n",",")," ",""),",,",","),",,",",")
Derived Column Settings
Script Component
Script Component Columns
Script Component Settings
Script Component Input / Output rows
Script Component - Design Script
' Variables
Dim i As Integer = 0
Dim itemList_PERSON As String = Row.PERSON
Dim itemList_DEPARTMENT As String = Row.DEPARTMENT
Dim delimiter As String = ','
If Not (String.IsNullOrEmpty(itemList_PERSON) Or String.IsNullOrEmpty(itemList_DEPARTMENT)) Then
Dim inputListArray_Wer() As String = itemList_PERSON.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)
Dim inputListArray_Abt() As String = itemList_DEPARTMENT.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)
If (inputListArray_Wer.Length = inputListArray_Abt.Length) Then
' new rows
For Each item As String In inputListArray_Wer
With Output0Buffer
.AddRow()
.KEY = CInt(Row.KEY)
.PERSON = item
.DEPARTMENT = inputListArray_Abt(i).ToString()
End With
' increase counter
i = i + 1
Next
Else
' Filtered Rows
With Output1Buffer
.AddRow()
.KEY = CInt(Row.KEY)
.PERSON = Row.PERSON
.DEPARTMENT = Row.DEPARTMENT
End With
End If
End If
Union ALL
Data Flow completed
Data Viewer valid rows
Data Viewer invalid rows
download
Of course I provide you the solution as download.
problem
If I have a column with several values (separated by CR) which I need separated into rows then following solution can help:
possible solution: T-SQL
One possible solution using T-SQL is following statement.
The function fn_Split returns all values in a list and the replace function converts the CR (carriage returns) to commas.
SELECT
[Key]
,[b.Value]
,[c.Value]
FROM
[dbo.TableExcel] AS a
CROSS APPLY
fn_Split(REPLACE(a.Persons, CHAR(10)+CHAR(13),','), ',') AS b
CROSS APPLY
fn_Split(REPLACE(a.Departments, CHAR(10)+CHAR(13),','), ',') AS c
The corresponding fn_Split function:
CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ')
RETURNS @Strings TABLE (
position int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings
VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
When Excel data is not coming into SSIS right, then do the following:
IMEX=1” in ConnectionString (Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1")TypeGuessRows=8” to a much higher valueSELECT * FROM [sheet1$A5:M2000]“)NOTE: The setting IMEX=1 tells the Excel driver to use Import mode. This mode reads the registry setting “ImportMixedTypes=Text” which forces mixed data to be converted to text. To achieve a more reliable column type recognition, the registry setting “TypeGuessRows=8” should be increased. By default the ISAM driver analyzes the first eight rows and determines the column datatypes from this sampling. If the first analysed rows contain only numeric values, then setting IMEX=1 will not convert this column datatype to Text – it will remain numeric.
ATTENTION: The IMEX setting has some other modes:
Registry Setting Location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel
supplement
If you experience following error the above described solution could fix it.
1. An OLE DB error has occurred. Error code: 0x80040E21.
2. Failed to retrieve long data for column “XYZ”.
3. There was an error with output column “XYZ” (55488) on output “Excel Source Output” (109). The column status returned was: “DBSTATUS_UNAVAILABLE”.
4. The “output column “XYZ” (55488)” failed because error code 0xC0209071 occurred, and the error row disposition on “output column “XYZ” (55488)” specifies failure on error. An error occurred on the specified object of the specified component.
5. [DTS.Pipeline] Error: The PrimeOutput method on component “Info Source” (101) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
6. [DTS.Pipeline] Error: Thread “SourceThread0″ has exited with error code 0xC0047038.