Tue 24 Nov 2009
Posted by Ralf under SQL Server
[2] Comments
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
Wed 28 Oct 2009
Posted by Ralf under SQL Server
[81] Comments
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.
Wed 30 Sep 2009
Posted by Ralf under IT, SQL Server
No Comments
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]
Wed 26 Aug 2009
Posted by Ralf under SQL Server
No Comments
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
Wed 22 Jul 2009
Posted by Ralf under SQL Server
[3] Comments
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.
Tue 7 Jul 2009
Posted by Ralf under SQL Server
[87] Comments
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.
Fri 3 Jul 2009
Posted by Ralf under SQL Server, Uncategorized
[84] Comments
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.
Fri 17 Apr 2009
Posted by Ralf under .Net, SQL Server, Uncategorized
[19] Comments
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.
Fri 17 Apr 2009
Posted by Ralf under .Net, SQL Server
[215] Comments
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
Thu 16 Apr 2009
Posted by Ralf under .Net, OpenSource, PHP, SQL Server
[2] Comments
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.
Tue 24 Mar 2009
Posted by Ralf under SQL Server, Uncategorized
[813] Comments
SQL Server: Version, Service Pack, Edition
Just run following command within Management Studio:
SELECT
@@VERSION,
SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
As result you receive something like:
Microsoft SQL Server 2005 - ... Windows NT 5.2 (Build 3790: SP 2) 9.00.4035.00 SP3 Standard Edition (64-bit)
Mon 8 Dec 2008
Posted by Ralf under .Net, SQL Server, Uncategorized
[2] Comments
How to disable caching in SQL Server Reporting Services for Visual Studio in Preview Mode?
SQL Server 2005 Service Pack 1 brings a new feature: caching data in BIDS (Business Intelligence Development Studio) Report Designer. Following objects are cached: all the data query, parameter value and credentials for previewing a report. Sometimes this feature can be annoying, if changes in preview mode should be visible immediately.
In order to disable the caching feature, following key has to be added to the config file ‘RSReportDesigner.config‘.
Key:
<Add Key="CacheDataForPreview" value="False" />
Location:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
To clear the cache you can also delete the *.data cache file which is located in the report RDL directory.
SSRS: Caching Files (Preview Mode)
Wed 27 Aug 2008
Posted by Ralf under ASP.Net, SQL Server
[59] Comments
ReportPath="/CPA_DWH_ASP_REPORTS/MPCSurvey_Plausibility"
ReportServerUrl="http://53.71.217.43/ReportServer
Wed 27 Aug 2008
Posted by Ralf under .Net, ASP.Net, SQL Server
No Comments
How to pass parameters to ReportViewer Control displaying a remote report (SSRS):
ReportParameter[] parameters = new ReportParameter[2];
protected void Page_Load(object sender, EventArgs e) {
parameters[0] = new ReportParameter("wave_id", "28");
parameters[1] = new ReportParameter("mpc_id", "1530");
ReportViewer1.ServerReport.SetParameters(parameters);
}
Thu 24 Jul 2008
Posted by Ralf under Business Intelligence, SQL Server
1 Comment
Hier eine kurze Empfehlung nützlicher SQL Server Tools:
BIDS Helper
(http://www.codeplex.com/bidshelper)
A Visual Studio.Net add-ins with features that extend and enhance the functionality of the SQL Server 2005 BI Development Studio. (BIDS)
New features for SSAS, SSIS.
SQL Dependency Director
(http://www.codeplex.com/SQLDependency)
SQL Dependency Director helps you track Microsoft SQL Server Object Dependencies.
SQL Dependency Director will help you see where a certain objects such as a Stored Procedure or Table is used in various Stored Procedures, Views, Functions, Jobs and SSRS Reports.
It is also planned to add the ability to chart the objects and their dependencies in the graphical format.
ReportServer Explorer
(http://www.codeplex.com/RSSExplorer)
This windows application lets you manage a Microsoft Reporting Servicer server. It supports both SQL 2000,SQL 2005 and SQL 2008, even when running in SharePoint Integration mode.
DTLoggedExec
(http://www.codeplex.com/DTLoggedExec)
DTLoggedExec is a tool that allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging informations of execution status and package runtime data.
SSIS Package Manager – PacMan
(http://www.codeplex.com/pacman)
PacMan – The SQL Server Integration Services Package Manager is a utility designed to permit batch operations on arbitrary sets of SSIS packages. Users can select a single package, a Visual Studio project or solution or a file system folder tree and then validate or update all selected packages in one operation.
Vulcan
(tba)
Vulcan is a Business Intelligence Framework built on top of Sql Server Integration Services and Sql Server Analysis Services for quickly creating BI solutions.
SQL Server 2005 Partition Management
(http://www.codeplex.com/sql2005partitionmgmt)
Sliding Window technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenges in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations.
Microsoft SQL Server Metadata-Driven ETL Management Studio (MDDE)
(http://www.codeplex.com/SQLServerMDDEStudio)
Originally an internal MSIT solution that has been released as an open source project, the Microsoft SQL Server Metadata-Driven ETL Management Studio (a.k.a. MDDE) provides a tool for rapidly generating SQL Server Integration Services (SSIS) packages from a shared central metadata repository. MDDE is a software factory for SSIS packages that leverages the SSIS API to provide a templating engine that accelerates ETL development.
Thu 8 May 2008
Posted by Ralf under PHP, SQL Server
[2] Comments
Here is an example using dynamic Pivot:
The Pivot columns (country short names) are dynamically generated from a query.
Single Quotations within Single Quotations can be realized by using 2 singe Quotations at once.
DECLARE @wave_id int
SET @wave_id = 2
– Populate table variable with list of MPC_SHORT_NAMES
SET NOCOUNT ON
DECLARE @T AS TABLE(y nvarchar(10) NOT NULL PRIMARY KEY)
INSERT INTO @T SELECT DISTINCT MPC_NAME_SHORT_EN_US FROM VL8C_DWH.VL8RS_DIM_MPCS WHERE WAVE_ID = @wave_id
– Construct the column list for the IN clause
– e.g., [D],[E],[F]
DECLARE @cols AS nvarchar(MAX), @y AS nvarchar(10)
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N”
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols + N’,[' + @y + N']‘
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
–SELECT @cols
– Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N’
SELECT
*
FROM
(
SELECT
KEY_TYPES.KEY_TYPE_NAME,
KEY_TYPES.KT_SORT_ORDER,
DATA.MPC_NAME_SHORT_EN_US,
DATA.K29*100 AS K29
FROM
(
SELECT
KC.KEY_TYPE_CLUSTER,
KC.KT_NAME_EU AS KEY_TYPE_NAME,
KC.KT_SORT_ORDER
FROM
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
WHERE
WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
) KEY_TYPES
LEFT JOIN
(
SELECT
M.MPC_NAME_SHORT_EN_US,
KC.KEY_TYPE_CLUSTER,
FM.K29_CNP_INDEX_TOTAL_BUSINESS AS K29
FROM
VL8C_DWH.VL8RS_FACTS_MAIN FM
INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPES K
ON FM.KEY_TYPE_ID = K.KEY_TYPE_ID
INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
ON K.KEY_TYPE_CLUSTER_ID = KC.KEY_TYPE_CLUSTER_ID
INNER JOIN
VL8C_DWH.VL8RS_DIM_WAVES W
ON FM.WAVE_ID = W.WAVE_ID
INNER JOIN
VL8C_DWH.VL8RS_DIM_COMPETITORS C
ON FM.COMPETITOR_ID = C.COMPETITOR_ID
INNER JOIN
VL8C_DWH.VL8RS_DIM_MPCS M
ON FM.MPC_ID = M.MPC_ID
WHERE
W.WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
AND C.COMPETITOR_NAME = ”MB” AND
M.EUX_MEMBER = 1 AND
(K.REPORT_RELEVANCY = ”B” OR K.REPORT_RELEVANCY = ”E”)
) DATA
ON KEY_TYPES.KEY_TYPE_CLUSTER = DATA.KEY_TYPE_CLUSTER
WHERE
DATA.K29 IS NOT NULL AND
DATA.MPC_NAME_SHORT_EN_US <> ”D”
) p
pivot
(
sum(K29)
for MPC_NAME_SHORT_EN_US
in (‘ + @cols + N’)
) as p’
PRINT @sql — for debugging
EXEC sp_executesql @sql
Tue 25 Mar 2008
Posted by Ralf under SQL Server, Windows
1 Comment
Folgender Tipp hilft, um die versteckte Instance Microsoft##SSEE von den WSS 3.0 zu entfernen:
To uninstall SSEE:
Start Registry Editor, and then locate the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\UninstallIn the left pane, click each GUID.
For each GUID that you click, look for a display name in the right pane that matches “Microsoft SQL 2005 Embedded Edition”.When you see a display name that matches the name, copy the value of the Key named “UninstallString”
Open a Command-Window (Start->run->cmd)
Paste the Copied string.
Append “CALLERID=OCSETUP.EXE” e.g. “MsiExec.exe /X {BDD79957-5801-4A2D-B09E-852E7FA64D01} CALLERID=OCSETUP.EXE”There you go
Don’t forget to reboot.
Hope that helps
Patrick
[Source: http://www.sharepointblogs.com/]
Tue 26 Feb 2008
Posted by Ralf under SQL Server
[138] Comments
USE [master]
GO
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='F:\Microsoft SQL Server\MSSQL.1\MSSQL\Log\templog.ldf')
GO
After this: Restart SQL Server.
Effect: Moving the TempDB to a more powerful storage will increase performance, especially for OLTP systems.
Thu 29 Nov 2007
Posted by Ralf under .Net, ASP.Net, JavaScript, SQL Server, VBA, Windows
[16] Comments
Unter .Net-Snippets findet man recht gute Lösungen. Sollte man immer mal besuchen, wenn man vor einem Problemchen steht.
Außerdem gibt es noch ein nettes Firefox-Search-Plugin zur schnellen Suche.
Mon 26 Nov 2007
Posted by Ralf under SQL Server
1 Comment
Die aktuelle CTP gibts hier:
https://connect.microsoft.com/programdetails.aspx?ProgramDetailsID=1384