http://support.microsoft.com/kb/969271
Try these steps to fix the problem:
1. Open Visual Studio 2008.
2. Open the local copy of the solution file using File > Open Project/Solution. (Note that no source control glyphs are visible.)
3. Choose the File -> Source Control menu in Visual Studio, and the "Go online" menu item. The source control glyphs now appeared.
4. Choose File> Source Control> Change Source Control and make sure the binds are correct. Unbind and rebind if they are not pointing to the TFS server.
5. Check out a file, modified it, and checked it back in to source control.
6. Exit Visual Studio and restart. (This time Visual Studio connect to Team Foundation Server automatically.)
7. Open the local copy of the solution file. The solution should be under source control now
ALL OF US DO NOT HAVE EQUAL TALENT. YET,ALL OF US HAVE AN EQUAL OPPORTUNITY TO DEVELOP OUR TALENTS. ~ Ratan Tata
Thursday, August 11, 2011
Wednesday, June 1, 2011
Friday, April 29, 2011
Linked Server to SSAS
EXEC sp_addlinkedserver
@server='CALXXXX',
@srvproduct='CALXXXX' ,
@provider='MSOLAP',
@datasrc='calCALXXXX',
@catalog='JournalEntryPosting'
/* Add login credentials for linked server */
EXEC sp_addlinkedsrvlogin
@rmtsrvname= 'CALXXXX',
@useself= 'False',
@locallogin= NULL,
@rmtuser= 'corp\nbkXXX',
@rmtpassword= 'rajXXX'
@server='CALXXXX',
@srvproduct='CALXXXX' ,
@provider='MSOLAP',
@datasrc='calCALXXXX',
@catalog='JournalEntryPosting'
/* Add login credentials for linked server */
EXEC sp_addlinkedsrvlogin
@rmtsrvname= 'CALXXXX',
@useself= 'False',
@locallogin= NULL,
@rmtuser= 'corp\nbkXXX',
@rmtpassword= 'rajXXX'
Thursday, April 28, 2011
SSAS MetaData Info
MDSCHEMA_CUBES Lists the cubes in an SSAS database
MDSCHEMA_MEASUREGROUPS Lists measure groups
MDSCHEMA_DIMENSIONS Lists dimensions
MDSCHEMA_LEVELS Dimension attributes
MDSCHEMA_MEASUREGROUP_DIMENSIONS Enumerates dimensions of measure groups
MDSCHEMA_MEASURES Lists measures
http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/
http://tssasm.codeplex.com/
MDSCHEMA_MEASUREGROUPS Lists measure groups
MDSCHEMA_DIMENSIONS Lists dimensions
MDSCHEMA_LEVELS Dimension attributes
MDSCHEMA_MEASUREGROUP_DIMENSIONS Enumerates dimensions of measure groups
MDSCHEMA_MEASURES Lists measures
http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/
http://tssasm.codeplex.com/
Sunday, February 6, 2011
1. Partition -- instead of select into ... use alter table switich partition
to move partition data.
alter table PartitionedTable switch partition partitionnr
to dumpPartitionedTable partition partitionnr
2. Data Compression -- Page level , Row level
3. DMV's
4. Dimensional modeling versus ER modeling -- Raphl Kimball
5. http://peddireddy999.blogspot.com/
to move partition data.
alter table PartitionedTable switch partition partitionnr
to dumpPartitionedTable partition partitionnr
2. Data Compression -- Page level , Row level
3. DMV's
4. Dimensional modeling versus ER modeling -- Raphl Kimball
5. http://peddireddy999.blogspot.com/
1. Partition -- instead of select into ... use alter table switich partition
to move partition data.
alter table PartitionedTable switch partition partitionnr
to dumpPartitionedTable partition partitionnr
2. Data Compression -- Page level , Row level
3. DMV's
4. Dimensional modeling versus ER modeling -- Raphl Kimball
5. http://peddireddy999.blogspot.com/
to move partition data.
alter table PartitionedTable switch partition partitionnr
to dumpPartitionedTable partition partitionnr
2. Data Compression -- Page level , Row level
3. DMV's
4. Dimensional modeling versus ER modeling -- Raphl Kimball
5. http://peddireddy999.blogspot.com/
Friday, January 14, 2011
Data Page Level Compression on a Heap Table with Statistics
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'DBArchive'),object_id('FactMTDPrior_Dec2010'), 0, NULL , 'DETAILED');
Compression Whole Table
USE DBArchive
go
ALTER TABLE FactMTDPrior_Dec2010
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
Compressing Specific Partition
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
Important values are taken into consideration
Pagecount Min Max Avg Compression page count
Record Size
b4e compression 1945864 171 174 171.987 0
after compression 777966 29 133 63.759 777959
after comp & shrinking 800030 29 134 65.574 759933
Before Compression
dbarchive.mdf 15,586,304 kb
dbarchive.ldf 353,216 kb
After Compression
dbarchive.mdf 21,815,296 kb
dbarchive.ldf 353,216 kb
After Shrinking the compressed database
dbarchive.mdf 6,402,560 kb
dbarchive.ldf 1,024 kb
1. (Pagecount before compression / Pagecount afer page compression and shrinking)
(1945864.00 / 800030.00) = 2.43223879104
Conclusion >>> We can Say PageCount was reduced to 60% after applying Page Level Compression and Shrinking on the Heap Table (which does not have any indexes)
2. Calculated the mdf file size after pagelevel compression and shrinking.mdf file reduced by 60% on the Heap Table (which does not have any indexes)
select 6402560 * 2.43223879104 = 15572554.79 (almost equal to 15,586,304 kb)
Compression Whole Table
USE DBArchive
go
ALTER TABLE FactMTDPrior_Dec2010
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
Compressing Specific Partition
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
Important values are taken into consideration
Pagecount Min Max Avg Compression page count
Record Size
b4e compression 1945864 171 174 171.987 0
after compression 777966 29 133 63.759 777959
after comp & shrinking 800030 29 134 65.574 759933
Before Compression
dbarchive.mdf 15,586,304 kb
dbarchive.ldf 353,216 kb
After Compression
dbarchive.mdf 21,815,296 kb
dbarchive.ldf 353,216 kb
After Shrinking the compressed database
dbarchive.mdf 6,402,560 kb
dbarchive.ldf 1,024 kb
1. (Pagecount before compression / Pagecount afer page compression and shrinking)
(1945864.00 / 800030.00) = 2.43223879104
Conclusion >>> We can Say PageCount was reduced to 60% after applying Page Level Compression and Shrinking on the Heap Table (which does not have any indexes)
2. Calculated the mdf file size after pagelevel compression and shrinking.mdf file reduced by 60% on the Heap Table (which does not have any indexes)
select 6402560 * 2.43223879104 = 15572554.79 (almost equal to 15,586,304 kb)
Labels:
Data Compression,
Heap Table,
Page Level Compression
Friday, January 7, 2011
Partition Number for a specific Partition Table
select PartitionNbr = $Partition.PartitionFunction('01/01/2011')
Merge / Split Partition Script
DECLARE @DateDt Datetime
, @SQLTXT VARCHAR(256)
SELECT @DateDt = '2011-12-26T00:00:00.000'
while @DateDt < '01/01/2012'
begin
ALTER PARTITION SCHEME SCHEME_SnapShotDt NEXT USED FG_SnapShotDt
ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() SPLIT RANGE (@DateDt);
--ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() MERGE RANGE (@DateDt);
SELECT @DateDt = @DateDt + 1
SELECT @DateDt
end
, @SQLTXT VARCHAR(256)
SELECT @DateDt = '2011-12-26T00:00:00.000'
while @DateDt < '01/01/2012'
begin
ALTER PARTITION SCHEME SCHEME_SnapShotDt NEXT USED FG_SnapShotDt
ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() SPLIT RANGE (@DateDt);
--ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() MERGE RANGE (@DateDt);
SELECT @DateDt = @DateDt + 1
SELECT @DateDt
end
Wednesday, December 1, 2010
bcp IN and OUT
EXEC xp_cmdshell 'bcp "select * from Reporting..FactMTDPrior with (nolock) where DataDt = ''2010-10-31'' " queryout "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
GO
GO
EXEC xp_cmdshell 'bcp Reporting..bcpTest in "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
go
GO
GO
EXEC xp_cmdshell 'bcp Reporting..bcpTest in "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
go
Tuesday, October 12, 2010
SQL Partitions
/** Table Name + Schema Name + Function Details. **/
SELECT OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.index_id IN(0,1);
GO
/** Function Insight Details. **/
SELECT
PF.function_id
, PF.name
, PF.fanout AS NumPartitions
, CASE WHEN PF.boundary_value_on_right = 0
THEN 'LEFT' ELSE 'RIGHT' END AS RangeType
, PP.parameter_id
, CASE WHEN PP.system_type_id = PP.user_type_id
THEN T1.name ELSE T2.name END AS ParameterDataType
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
order by PF.name,PRV.Value
go
/** switch out the parition **/
alter table switch partition @partitionnr to partition @partitionnr
go
/** Adding a Partition on the existing Table **/
CREATE CLUSTERED INDEX [ix_DataDt_tab1] ON [dbo].[tab1]([DataDt] ASC)ON [SCHEME_Name]([PartitionKey])
CREATE NONCLUSTERED INDEX [ix_LoanId_tab1] ON [dbo].[tab1]([LoanId] ASC) ON [SCHEME_Name]([PartitionKey])
SELECT OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.index_id IN(0,1);
GO
/** Function Insight Details. **/
SELECT
PF.function_id
, PF.name
, PF.fanout AS NumPartitions
, CASE WHEN PF.boundary_value_on_right = 0
THEN 'LEFT' ELSE 'RIGHT' END AS RangeType
, PP.parameter_id
, CASE WHEN PP.system_type_id = PP.user_type_id
THEN T1.name ELSE T2.name END AS ParameterDataType
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
order by PF.name,PRV.Value
go
/** switch out the parition **/
alter table
go
/** Adding a Partition on the existing Table **/
CREATE CLUSTERED INDEX [ix_DataDt_tab1] ON [dbo].[tab1]([DataDt] ASC)ON [SCHEME_Name]([PartitionKey])
CREATE NONCLUSTERED INDEX [ix_LoanId_tab1] ON [dbo].[tab1]([LoanId] ASC) ON [SCHEME_Name]([PartitionKey])
Tuesday, July 6, 2010
SQL SERVER 2005 Alert System
Declare @i int
select @i = count(*) from dbname.dbo.tab1
if @i = 0
begin
exec msdb.dbo.sp_send_dbmail @recipients = 'vpeddireddy@alert.com',@subject = 'test email'
end
select @i = count(*) from dbname.dbo.tab1
if @i = 0
begin
exec msdb.dbo.sp_send_dbmail @recipients = 'vpeddireddy@alert.com',@subject = 'test email'
end
Thursday, June 24, 2010
Understanding SQL 2005 DMV and DMF
sys.dm_exec_query_stats -
Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here.
sys.dm_exec_sql_text -
Returns the text of the sql statement based on the SQL handle
sys.dm_exec_query_plan -
Returns the showplan in XML format for a batch or module based on the plan handle.
dm_db_index_usage_stats –
displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans
dm_db_index_physical_stats –
replace DBCC SHOWCONTIG used to check index fragmentation.
dm_db_index_operational_stats –
use to check for index contention and blocking
dm_os_wait_stats –
Used to determine the types of waits (signal or resource that are occurring)
dm_os_waiting_tasks –
Used to help identify blocking
dm_tran_locks –
Used to help indentify blocking
dm_os_performance_counters –
Query Access to SQL Server Performance counters
Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here.
sys.dm_exec_sql_text -
Returns the text of the sql statement based on the SQL handle
sys.dm_exec_query_plan -
Returns the showplan in XML format for a batch or module based on the plan handle.
dm_db_index_usage_stats –
displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans
dm_db_index_physical_stats –
replace DBCC SHOWCONTIG used to check index fragmentation.
dm_db_index_operational_stats –
use to check for index contention and blocking
dm_os_wait_stats –
Used to determine the types of waits (signal or resource that are occurring)
dm_os_waiting_tasks –
Used to help identify blocking
dm_tran_locks –
Used to help indentify blocking
dm_os_performance_counters –
Query Access to SQL Server Performance counters
Wednesday, June 23, 2010
List of SQL 2005 DMVs
-- OS Related
select * from sys.system_objects
where name like 'DM_OS%'
GO
-- Exec Related
select * from sys.system_objects
where name like 'DM_exec%'
GO
-- Transaction Related
select * from sys.system_objects
where name like 'DM_tran%'
GO
-- Replication related
select * from sys.system_objects
where name like 'DM_repl%'
GO
-- IO Releated
select * from sys.system_objects
where name like 'DM_IO%'
GO
-- clr Releated
select * from sys.system_objects
where name like 'DM_CLR%'
GO
-- db index related
select * from sys.system_objects
where name like 'DM_db%'
GO
-- broker related
select * from sys.system_objects
where name like 'DM_broker%'
GO
-- fts related
select * from sys.system_objects
where name like 'DM_fts%'
GO
-- qn related
select * from sys.system_objects
where name like 'DM_qn%'
GO
select * from sys.system_objects
where name like 'DM_OS%'
GO
-- Exec Related
select * from sys.system_objects
where name like 'DM_exec%'
GO
-- Transaction Related
select * from sys.system_objects
where name like 'DM_tran%'
GO
-- Replication related
select * from sys.system_objects
where name like 'DM_repl%'
GO
-- IO Releated
select * from sys.system_objects
where name like 'DM_IO%'
GO
-- clr Releated
select * from sys.system_objects
where name like 'DM_CLR%'
GO
-- db index related
select * from sys.system_objects
where name like 'DM_db%'
GO
-- broker related
select * from sys.system_objects
where name like 'DM_broker%'
GO
-- fts related
select * from sys.system_objects
where name like 'DM_fts%'
GO
-- qn related
select * from sys.system_objects
where name like 'DM_qn%'
GO
Tuesday, June 22, 2010
Wednesday, June 2, 2010
DSQuery
Following command gives OU details of a server
c:\winnt\system32>dsquery computer -name xxxxxxxxx
c:\winnt\system32>dsquery computer -name xxxxxxxxx
Wednesday, May 26, 2010
SQL 2005 Transaction Logs
The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared).

A log record is no longer needed in the transaction log if all of the following are true:
The transaction of which it is part has committed.
The database pages it changed have all been written to disk by a checkpoint.
The log record is not needed for a backup (full, differential, or log).
The log record is not needed for any feature that reads the log (such as database mirroring or replication).
A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.
This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not.

The check whether log truncation can take place under either of the following circumstances:
When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
When a log backup completes.
There are two common issues that can prevent log truncation:
A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.

A log record is no longer needed in the transaction log if all of the following are true:
The transaction of which it is part has committed.
The database pages it changed have all been written to disk by a checkpoint.
The log record is not needed for a backup (full, differential, or log).
The log record is not needed for any feature that reads the log (such as database mirroring or replication).
A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.
This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not.

The check whether log truncation can take place under either of the following circumstances:
When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
When a log backup completes.
There are two common issues that can prevent log truncation:
A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.
Thursday, May 20, 2010
Changing Database COLLATION
1. Litespeed uninstall.
2. set Password and collation execute the following.
D:\Installs\SQL2005ENTSELX64dvd\Servers>start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=******* SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
3. APPLY CU3
2. set Password and collation execute the following.
D:\Installs\SQL2005ENTSELX64dvd\Servers>start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=******* SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
3. APPLY CU3
Friday, May 7, 2010
SQL 2005 execute permissions
SQL2005
SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Subscribe to:
Posts (Atom)
