[T-SQL] tsql query - Count the number of spaces in a string

---

tsql query - Count the number of spaces in a string Posted: 19 Jun 2012 06:45 AM PDT How do I write a query that tells me how many spaces (...or any character for that matter) are in a particular string?Example: David H Rogers would return a 2 since there are 2 spaces.

Storing dynamic sql Posted: 19 Apr 2013 04:09 AM PDT DECLARE MY_CURSOR CursorFOR SELECT [DB_NAME],[SCHEMA_NAME],[TABLE_NAME],[COLUMN_NAME] FROM dbo.Data_Profile_StatsOpen My_CursorDECLARE @DB_NAME nvarchar(500),@SCHEMA_NAME nvarchar(500),@TABLE_NAME nvarchar(500),@COLUMN_NAME nvarchar(500), @QUERY NVARCHAR(MAX)DECLARE @MAX_NUMBER INT, @MIN_NUMBER INT, @NULL_COUNT INT, @BLANK_COUNT INT, @ZERO_COUNT INT,@DISTINCT_COUNT INTFetch NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME While (@@FETCH_STATUS = 0)BEGINSELECT @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAMESET @QUERY='SELECT MAX('+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME+'.'+@COLUMN_NAME+') FROM '+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME--SET @MAX_NUMBER=exec sp_executesql @QUERYEXEC SP_EXECUTESQL @QUERY, N'@MAX_NUMBER NVARCHAR(255) OUTPUT', @MAX_NUMBER=@MAX_NUMBER OUTPUTSELECT @MAX_NUMBER--SET @QUERY='UPDATE [dbo].[Data_Profile_Stats] SET MAX_NUMBER='+CAST(@MAX_NUMBER AS VARCHAR(10))+' WHERE [DB_NAME]='+''''+@DB_NAME+''''+' AND SCHEMA_NAME='+''''+@SCHEMA_NAME+''''+' AND TABLE_NAME='+''''+@TABLE_NAME+''''+' AND COLUMN_NAME='+''''+@COLUMN_NAME+''''--exec sp_executesql @QUERYFETCH NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME ENDCLOSE MY_CURSORDEALLOCATE MY_CURSORGOIn the above query, I am seeing the result of the EXEC SP_EXECUTESQL but not able to see the value of @MAX_NUMBER in the next lineCan someone tell me what am I doing wrong?Thanks in advance

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL server issues] Created Issue: AdventureWorksDW2012 Download file corrupt [19063]

There seams to be an issue with the AdventureWorksDW2012 Data file download. When I try to run this SQL statement:

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

This is the error I get:

Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Thanks,Dan

There seams to be an issue with the AdventureWorksDW2012 Data file download. When I try to run this SQL statement:

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

This is the error I get:

Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Thanks,Dan

.

sqlserversamples.codeplex.com

[SQL Server Data Warehousing] T-SQL SCD2

Here is the query i am using in case if it helps.

To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.

My first update statments goes and finds an employee based on a logic of Data Entry

Update dbo.SFWP 
Set EmployeeID  = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and  Src_EntryDate >= we2.EffectiveBeginDTM and 
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records

and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM

So, my new query i am trying to take a previous or Next record and find employee ID and update it.

wITH CustCTE as
(
select  
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where 
 Src_EntryDate < we2.EffectiveBeginDTM or 
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select 
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum
    
From CustCTE a
Union All 
Select 
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum
    
From CustCTE Curr
Inner Join      CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..

Any help in this matter will be highly appreciated.

thank you in advance

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] How would my fact and dim tables look like in this example?

Please bear with me.

This is a very simplified example, but it's more or less the foundation.

We get hourly data for several cells, for example ORLFL-1-123-1. This cell is a combination of AREA-CAB-SITE-SECTOR.

Data analysts want to get several KPIs from all the data coming from these cells, but by AREA, CAB, SITE, SECTOR (not cell). And the smallest date range is daily (daily, weekly, monthly, etc).

Let's say the KPIs are KPI_ABC, KPI_NDO, KPI_DRT, KPI_QRR.

I have DimDate, DimSite, DimArea, DimCab, DimSector. I assume that's correct.

My question are the Fact tables. I was thinking of creating one Fact table for each KPI (FactKPI_ABC, FactKPI_NDO, etc). Each Fact table would have the total per site per day, plus the attributes for the site. For example the fields for FactKPI_ABC would be:

Datekey
Site
KPI_ABC
Area
Cab
Site
Sector

The problem I see (if it's a problem) is that all the tables would be exactly the same, since the only value that changes is the actual KPI total.

Is this the correct way to go? Any help is greatly appreciated.

Thanks.

---

VM

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SSIS Auditing Mechanism?‏

I'll help if I can...

In my approach, I have 2 audit tables; one for logging package execution and the other for logging table processing.  Before each data flow I log the package like this:

INSERT INTO AuditPkgExecution (
PkgName, PkgGUID, PkgVersionGUID, PkgVersionMajor, PkgVersionMinor, ExecStartDT, ParentPkgExecKey)
Values (@PkgName, @PkgGUID, @PkgVersionGUID, @PkgVersionMajor, @PkgVersionMinor, @ExecStartDT,
@ParentPkgExecKey)

On the insert my PkgExecKey increments, so I grab it and store it in a user variable to use later in my table logging.

SELECT MAX(PkgExecKey) AS PkgExecKey
FROM AuditPkgExecution
WHERE PkgName = @PkgName AND ExecStartDT = @ExecStartDT

Now, when loading the source file I count the input records and store that as a variable, then create the initial audit record for that source's destination table and store that TableProcessKey in a variable to use later.
INSERT INTO AuditTableProcessing (
PkgExecKey, TableName, TableInitialRowCnt)
Values (@PkgExecKey, 'FactTicket', @RowCount)

SELECT Max(TableProcessKey) AS TableProcessKey
FROM AuditTableProcessing
WHERE PkgExecKey = @PkgExecKey
AND TableName = 'FactTicket'

When I load the source data, I first count the starting number of rows in the destination table and store it in a variable.  Then I record the number of rows inserted, updated, errored on insert, and errored on update and store those in variables.  Finally, after the load I count the final number of rows in the destination table, then update the audit table...

UPDATE AuditTableProcessing
SET
ExtractRowCnt = @RC_Xtrct ,
InsertStdRowCnt = @RC_Normal,
UpdateRowCnt = @RC_Updated,
InsertErrorRowCnt = @RC_InsertError,
UpdateErrorRowCnt = @RC_UpdateError,
TableFinalRowCnt = @RowCount,
SuccessfulProcessingInd = 'Y' --if we made it here, we're cool
WHERE TableProcessKey = @TableProcessKey

The final step is, after the data flow task completes, to update the audit table for package execution:

UPDATE AuditPkgExecution
SET ExecStopDT = getdate()
, SuccessfulProcessingInd = 'Y'  
WHERE PkgExecKey = @PkgExecKey

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Optimum DR strategy for datawarehouse DBs

We have a SQL Server 2008R2 servers with Datawarehouse DBs which gets populated by  SSIS packages for ETLs. The database sizes are around 380GB - 400GB. The ETLs run throughout the night and load the data and we load around 1 million rows of data and no activity happens during the day time. We can afford a data loss of 1 day at any point. The business wants to design an DR stretegy for our DWH databases.  I want to keep into account the performance factor, network bandwidth for this environment.

I do not want to just rely on full backups as the size of each full backup for DWH dbs is around 80GB (After compression enabled) and we have 5 DBs and it will choke network bandwidth if i copy the backups to DR site over the network.

i have proposed the following solution

1. All the DBs in simple recovery model  

2. Take full backups (compression enabled) every weekend

3. Take differntial backups(compression enabled) during the weekday

Can you guys suggest if this is a correct DR strategy for Datawarehouse DBs considering performance,network and dataloss affordable and please help out with any other stretegy you can think of...

-sqluser

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Star Schema modeling on Order Header/Details data

In my situation I have a Order Header which contains info like order date, expected ship date, expected arrival date, vendor, buyor, etc... and that is my PO dimension.  I have an Order Detail that contains the line items with qty and cost.  I have a Receiver Header that includes receipt date and receipt status, and I have a Receiver Detail that includes qty and cost of received goods.

For Orders I calculate things like Avg Lead Time, Fill Rate %, and show current On Order.

Depending on how you handle your accounting, a discount that is applied to an order and NOT the line items on the order can skew your margin reporting.  If I had that situation, I would likely allocate the order-level discount to the cost of the line items on a pro-rata basis.

.

social.technet.microsoft.com/Forums