[SQL Server] SQL commands to learn

---

• SQL commands to learn
• Using AND
• defferred name resolution

SQL commands to learn Posted: 04 Jun 2013 12:44 PM PDT Can anybody recommend a good online guide to SQL commands? Or at least a list of the ones I should learn first?

Using AND Posted: 03 Jun 2013 08:20 PM PDT Hi,I am confused with a query that:SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025'When I run this query it return nothing. WHy?TableName:transactionID, StoreID, STNNo, VoucherBookletNo, Quantity, AllocatedDateTableData:1 1004 1004000032 VVB0000021 16 2013-06-04 11:11:19.7872 1004 1004000032 VVB0000022 16 2013-06-04 11:11:19.7873 1004 1004000032 VVB0000023 16 2013-06-04 11:11:19.7874 1004 1004000032 VVB0000024 16 2013-06-04 11:11:19.7875 1004 1004000032 VVB0000025 16 2013-06-04 11:11:19.7876 1004 1004000032 VVB0000026 16 2013-06-04 11:11:19.787

defferred name resolution Posted: 03 Jun 2013 09:03 PM PDT Dear AllI am using sql 2008 . While compiling the procedures it does not give error even if table not present.I need to trap this error. How to achieve it?Regards

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies 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 Data Warehousing] SSAS Cube not taking the data

if you see updated data as result of querying views, but you cannot see those updated data in cube after processing, then this might be because of this fact that cube pointing to another database (which has old data). if it is not the case, then check does the processing occurs on FULL process? do you get any error during process?

---

http://www.rad.pasfu.com

.

social.technet.microsoft.com/Forums

[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

[Articles] The Joy of Technology

---

The Joy of Technology Posted: 03 Jun 2013 11:00 PM PDT Steve Jones enjoys his career in technology and hopes that you do as well.

You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial 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] Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]

Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]

pavanbarnala wrote Feb 23, 2012 at 10:47 AM

Hello,
Is this been taken care? If so, please share the latest data for DimDate. I'm working on some POC which requires dates in between 2008 and 2010 as well. If it is not already done, can someone let me know is there is any way I can generate the data for those missing dates in that table?

petermyers wrote Feb 26, 2012 at 5:21 AM

The attached script will fix the problem. Use the stored procedure to extend to future dates if needed.

.

sqlserversamples.codeplex.com

[MS SQL Server] SQL Server and Oracle on same SAN

---

• SQL Server and Oracle on same SAN
• Batch Request/Sec Bottleneck? How do you know?
• SQL Error Log AWE not enabled
• Previously registered servers awol

SQL Server and Oracle on same SAN Posted: 04 Jun 2013 05:13 AM PDT HiWe have a NetAPP iSCSI SAN, with 120 disks, we already have made some aggregates for Oracle, and now we have some extra capasity on our iSCSI SAN.We want to use this for SQL Server installationsIs there a problem with io patterns, causing in performance degrade?We have apporx 1mio transactions pr min, or 35 GB in one hour. We have talked about moving our Sharepoint server..My major concern is performance, and if any issues are known, I would like to know them, now in front, and not when we have a problem

Batch Request/Sec Bottleneck? How do you know? Posted: 04 Jun 2013 01:35 AM PDT Hello all, I have been reading about batch requests/sec as a stat for measuring performance but what I fail to find is a way to determine what is a good level of Batch requests/sec. Can someone point out what it can be compared against or is there a general number that indicates a bottleneck?For instance if cpu utilization is pinned at 90% it is an indicator of a bottleneck. If batch requests/sec is at 2,000 during peak is that good or bad?

SQL Error Log AWE not enabled Posted: 03 Jun 2013 09:13 PM PDT Spotted this in SQL ERROR log"SQL Server is not configured to use all of the available system memory. To enable SQL Server to use more memory, set the awe enabled option to 1 by using the sp_configure stored procedure."So off I wentsp_configure 'min server memory', 1024RECONFIGUREGOsp_configure 'max server memory', 3072RECONFIGUREGOConfiguration option 'min server memory (MB)' changed from 0 to 1024. Run the RECONFIGURE statement to install.Configuration option 'max server memory (MB)' changed from 2147483647 to 3072. Run the RECONFIGURE statement to install.Did I do the right thing? Its only a laptop with 4GB of memory! Why was i set at 2147483647 ? I think I have seen 2147483647 as the MAX memory before.Am I ngoing bonkers or is this some kind of forgot to convert to MB kind of issue?CheersE

Previously registered servers awol Posted: 03 Jun 2013 08:15 PM PDT Hi all,Wondered if anyone could assist?I've logged on to one of my SQL Server 2008 R2 instances and have received the following message:TITLE: Registered Servers------------------------------Unable to read the list of previously registered servers on this system. Re-register your servers in the 'Registered Servers' window.------------------------------ADDITIONAL INFORMATION:Serialization output is invalid. (Microsoft.SqlServer.Management.Sdk.Sfc)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.5500.0+((Katmai_PCU_Main).110922-0336+)&LinkId=20476------------------------------'None' is an invalid XmlNodeType. Line 7, position 9. (System.Xml)The 'help' link in the additional information directs to another message basically saying there's no additional information available! so I had a look at BOL and could only find one reference to this issue but it's to do with upgrading from SQL 2005. My instance has been running 2008 for easily 2-3 years now!! The instructions ([url]http://support.microsoft.com/kb/955648[/url]) advise to open SSMS, open registered servers window, expand the Database Engine node, R/Click Local Server Groups, point to tasks and click Previously Registered Servers..... this would be great but my instance isn't displaying anything under any of the nodes (Database Engine, Analysis Services, Reporting Services etc etc).I can still view the DB's in object explorer but can't query any of the databases using T-SQL (even system stored procedures!), I get the following error:- 'An error occurred while executing batch. Error message is: The directory name is invalid.' Can anyone suggest anything please as I'm royally stumped.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration 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: SQL Server 2008 Developer edition sample database install error [18174]

Created Issue: SQL Server 2008 Developer edition sample database install error [18174]

An unexpected error has occured.

description

I'm trying to install Adventureworks sample dB and i get an error.
I successfully downloaded the mdf file however when attach, add and click OK from SSMS I get the error:
 
Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

.

sqlserversamples.codeplex.com