Microsoft SQL Server 2008

49K

Solutions

23

Articles & Videos

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Share tech news, updates, or what's on your mind.

Sign up to Post

Consider this example:

use msdb
 SELECT * FROM backupset  /* 1st step */
 SELECT @@ROWCOUNT /* 2nd step: low execution cost */
 SELECT COUNT( DISTINCT (name)) FROM backupset /* 3rd step: cost is alike 1st step */

The goal is to output the SELECT, then output the count of the result set, and lastly the distinct value count of a specific column.

now substitute a complex query instead of restorehistory.
how can you produce the result without exectuting the complex query twice in expecution plan cost.

is there a way to simplify/modify the query, as to not run the execution plan and joins twice to get the 2 resultsets..

(would creating a temp table and then doing 3 statement with different filters efficient/advised?)
0
Ransomware-A Revenue Bonanza for Service Providers
LVL 4
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

I have a SQL query where i attempted to use the solution presented in the thread, "Get the last 13 week from SQL query". It works WELL but I have two issues.
1.  When I use that solution in the where clause, any calculation that I have ROUNDS to 1 or 0, never the decimal
2.  How do you get the last 13 weeks using just the week number?

My data only shows week and year.
0
I have a table (User_Values) where I need to create 613 rows of data into columns "User_ValueKey" (primary key) and User_Values (int).  The values are sequential, from 333 to 948.  The data would look like this in the table itself

User_ValueKey      User_Value
333                          333
334                          334
335                          335

And so forth.

Is there a quick way to do this?  I have SQL Server 2008 R2 Express, if that helps.

Thank you!
0
Hi experts
I have a Windows Server 2012 R2 - 64 bit, 32 GB RAM.
On this runs a SQL Server 2008 R2 Enterprise Edition 64 bit

The issue is that the sqlservr.exe service consumes almost 95% of memory (approx 28 GB).
I was checking and the configurations of min and max memory are not configured, I see the values like this:

Min server memory = 0
Max server memory = 2147483647
These are the default values.

My question is: what is the correct way or what should I take into account to give the appropriate values to my BD in relation to memory. Or how to tell if these 32GB of RAM I have is what I really need or are already running short.
0
Hello Experts,
I am trying to debug a Stored Procedure and I get the following error as soon as I Start Debugging.  Any idea what is going on?

Error Message:  Unable to start T-SQL debugging. Could not attach to sql server process on XXX.  The object exporter specified was not found.

Thank you in advance!
0
hi,

for any SQL server install SSL security , under what situation your customer will do it ? usually company I work with install SSL only in web server login page.

to protect DB backup from getting restore to other DB ? so using TDE ?  but TDE must use SSL cerification from a known provider like symantec ?
0
Hello,
Can't figure out what is wrong with this SP.
The first condition does not fire at all.
 @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @ColumnName2 varchar(100) = null,
	 @ColumnName3 varchar(100) = null,
	 @Value varchar(100) = null,	
	 @Value2 varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	
          
			 	
			if  @ColumnName3 ='' 
				SET @SQL = 'SELECT DITINCT '  
			   
				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName2 + ']  LIKER ' +   '''' +'%' +  @Value + '%'+''''
	
	        if @ColumnName3 <> '' 
				SET @SQL = 'SELECT DISTINCT '  
	         

				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE   CAST(CollectionDateTime AS Date) =  ' +   '''' + @value  +''''  +
				 ' and   [' +  @ColumnName3 + ']   like ' +   '''' +'%'+  @Value2 +'%' +''''
	
	  SET @SQL = @SQL  	
	  
	  EXEC (@SQL)	
	  PRINT(@SQL)	
   

Open in new window


Cheers
0
Hi Experts ,

Today morning i have noticed below error .any thoughts on that how to solve the problem?

Error: 7884, Severity: 20, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
0
Hi experts
In May I had installed the sql server express with the ssdt. SSDT showed error, see attached file

In June a new installation was made and now I have the sql server 2012 developer
Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
Sep 23 2016 15:49:43
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack

However the error is the same as it showed in May and I can not create packages in the SSIS
www.png
0
Problem Statement : I have a performance issue with the Update query , I am able to update the data (100 Million rows)  for one calendar day , but  unable  to update the data for 5 or 10  calendar days (1 Billion Rows ) , because  the data volume in the tables  is so huge.

Please Note : All the indexes are in place , have enough I/O , RAM  and CPU including the TEMPDB space

So please fine tune this query(attached) and this statement  is causing the issue . Will appreciate your quick reply in this regard.

Thanks,
SRK
0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

I need to know how much data travels (Send and Receive bytes per second) through my SQL server for a period of time (this can be a daily one)
this fact I need because I want to budget in Megas how much traffic we have in order to take the test database to the cloud (Azure).
0
I have 90 stored procedures and I would like to  grant  an EXECUTE permission to a user instead of manually doing it one by one.
What sql syntax or way to do it.
0
would both these syntax be exact and correct? what are pros/cons?
             
              SELECT count(*)
        FROM tableA
        INNER JOIN tableB
               ON A.key = B.Key
               AND EXISTS (SELECT col1 FROM tableC WHERE C.COL1 <> b.COL1 )
               
              SELECT count(*)
        FROM tableA
        INNER JOIN tableB
               ON A.key = B.Key
               AND B.col1 NOT IN (SELECT col1 FROM tableC)
               
               tableA has 3000 records.
              select distinct B.Col1 from tableB --9872
              select count(0) from tableB --9872
                            select count(1) from tableC --58M Records                            
select distinct COL1 from tableC --98003 Records
0
We have a nightly restore job that has been working flawlessly for years now. Recently, the restore has been hanging at 0 Percent Complete. (The database was taken offline prior to the restore. The status of the database never changes from offline to restoring).
Looking at the Activity Monitor, we are seeing that the restore is running with PREEMPTIVE_OS_FILEOPS wait types.

We are restoring from a DataDomain device. This is an intermittent problem. It works fine for 3 days, then it hangs up.
Rebooting the SQL Server clears up the issue (temporarily)

We are running SQL 2008 SP1 (Enterprise edition).
Our Data Domain device is a DD 2500 OS 5.7.2.0-532316

Has anyone experienced this?

Can anyone offer any tips as to what might be going on?

Thank you
0
for the query below:
SELECT count(*)
       FROM tableA
       INNER JOIN tableB
              ON A.key = B.Key
              AND B.col1 NOT IN (SELECT col1 FROM tableC)

                            currently, tableB has one [clustered, unique, primary key] index on col_x, Col1 (Col1 is in the condition)
                            tableC has one [clustered, unique, primary key] index on col_A,col_B, Col1  (Col1 is in the condition)
             
the condition is troublesome due to the big table. can you recommend on choosing appropriate index on B.Col1? or should it be on C.Col1?

but SELECT count(*)
       FROM tableA
       INNER JOIN tableB
              ON A.key = B.Key
returns in less than a second.(without condition)

stats below:

tableA has 3000 records.
              select distinct B.Col1 from tableB --9872
              select count(0) from tableB --9872
                            select count(1) from tableC --58M Records                            
select distinct COL1 from tableC --98003 Records
0
hi experts

my user database not show in the login and users. i need grant permission
www.png
0
Hello Experts
I currently have the license sql server 2012 enterprise, what problem could you have if it is under a standard. I currently only use database engine
0
in my work, I have a database user that belongs to the sysadmin role: it_support

When I create database user, these are created with all permissions, ie can view tables, run store procedure

My question is: why are they created with all the permissions?

In my house when I connect with my administrator account does not happen the same
0
The goal is to do a backup of a TDE database in Enterprise and take off encryption and thenbring a copy of standard edition.

When I ran this command
ALTER DATABASE oster
SET ENCRYPTION OFF
DROP DATABASE encryption key
and then ran this:
select * from sys.dm_database_encryption_keys  
the encryption_state status became '5', first for few minutes, and then became '1', but there was still an entry for that database in dm_database_encryption_keys .

now when i backup this new database from enterprise and restore it in standard edition, i get below:
Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.

how can we totally remove entry for that new database from dm_database_encryption_keys? maybe that is giving impression that TDE is still somewhere somehow present or
linked or alluded?

(when we create a brand new database, we dont see any entry for it in dm_database_encryption_keys). how can we bring this database to have no reference to any
entry in dm_database_encryption_keys?
0
Windows Server 2016: All you need to know
LVL 1
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

I have many columns in my report and I want to dynamically sort the columns which the user needed to sort in ascending order. How could this be done?
0
This is SQL 2014

I have this sample data

create table #temp
(
   rowId int identity,
   cId int,
   year int,
   rank int,
   cName varchar(500)
)
 
 
 
insert into #temp
select 1, 1712, 2015,3,'test1'
 
insert into #temp
select 2, 1929, 2015,2302,'test2'
 
insert into #temp
select 3, 1712, 2014,1,'test1'
 
insert into #temp
select 3, 1929, 2014,2024,'test2'

Open in new window


I get 4 row. This is how I want it to look like. (I wanted to loop thru the #temp table, compare the "cid" and then maybe concat and dump the result into another table but that can't be the correct way of doing this)

I need the data in "Rank" column to be from earliest year (2014) to latest year (2015)

CId                           Year                                        Rank                              Cname

1712                    2014,2015               1,3                                  test1

1929                     2014, 2015                           2024,2302                              test2
0
I have an Individuals table with FName, LName, Address1, EMailAddress, Phone in it... (Among other things)
I have an inbound record that I need to match against those items

So that if any 3 of the 5 match
I get a @dupe=1 message
0
hi experts

I have a table with 100,000 records, as I should modify it to support temporary system tables

this create code
CREATE TABLE dbo.Manager
( ManagerId int NOT NULL PRIMARY KEY,
  ManagerName nvarchar(50) NOT NULL,
  ManagerPassword varbinary(200) NOT NULL,
  ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
  ChangedBy sysname NOT NULL CONSTRAINT DF_Employee_ChangedBy DEFAULT  (SUSER_SNAME()),
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ManagerHistory));
GO

But my table already exists and has data, so my question is: how do I modify the table
0
when a backup from SQL 2012 enterprise is placed in a Standard edition for RESTORE,

Database1)
Database cannot be started in this edition of SQL Server because part or all of object 'SOSPgrm' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
 
Database2)
Transparent Data Encryption is not available in the edition of this SQL Server instance. Part or all of object 'tblDDLProgram' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

can you recommend a way to work around this?
is it possible to make a RESTORE in the enterprised edition with different names, then remove the features on those databases and then BACKUP/RESTORE TO standard edition?
0
I have 50+ Views in SQL server and while connecting to the same server via Crystal Reports, I can only see top 12 Views (the top 12 that names start with letter "A").
How can I see all Views?
0

Microsoft SQL Server 2008

49K

Solutions

23

Articles & Videos

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.