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
[Webinar] Learn How Hackers Steal Your Credentials
LVL 8
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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
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
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
hi experts

my user database not show in the login and users. i need grant permission
www.png
0
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
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
I have 8 servers and want to archive database of 10 years data. I want to create a procedure that accepts 2 parameters original database name and archival database name. Procedure should select and insert the data in archival db from original db and delete that selected data from original db.
0
I am using MS SQL Server 2008 as my Database and I created a Stored Procedure that looks like this.

    USE [DB_Question]
    GO
   
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER Procedure [dbo].[Check_ExamID]
    @ExamID NVARCHAR(MAX)
    as
    SELECT * FROM TBL_ExamTimer WHERE ExamID = @ExamID

and here is my PHP code that suppost to be the target is to display data in my PHP site by using some criteria also.

    <html>
    <head>
   
    </head>
    <body>
    <?php
   
          include('config.php');
          include('adodb/adodb.inc.php');
          $db = ADONewConnection($dbdriver);
          $db->Connect($dsn, $username, $password);
          
          $procedure = $db->prepareSp('Check_ExamID');
          if (!$procedure)
        die ('Invalid or inaccessible stored procedure name');
          
          $parameter1Name = '@ExamID';
          $ok = $db->inParameter($procedure,$parameter1Name,'1234');
          $result = $db->execute($procedure);
     
   
    ?>
    </body>
    </html>

I am trying to display data where `ExamID =@ExamID` or `1234` for param of `@ExamID`

My Target here is to use my stored procedure to select data in my table and return it back in my php file but nothings happening. TYSM
0
Three Reasons Why Backup is Strategic
LVL 4
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Hello,

is there any way to modify chart arrowheads in SSRS? The proposed ones (Triangle, Sharp triangle, Lines) are quite ugly... I'd like to display axes with thinner and smaller arrowheads.
0
When installing SQL 2008 server R2 SP3 update it fails on the instances, here is the boot strap error

Overall summary:
  Final result:                  The patch installer has failed to update the following instance: MICROPOWER. To determine the reason for failure, review the log files.
  Exit code (Decimal):           -2068024447
  Exit facility code:            1212
  Exit error code:               29569
  Exit message:                  The patch installer has failed to update the following instance: MICROPOWER. To determine the reason for failure, review the log files.
  Start time:                    2017-05-15 23:30:06
  End time:                      2017-05-15 23:32:16
  Requested action:              Patch

Instance MICROPOWER overall summary:
  Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
  Exit code (Decimal):           -2068024447
  Exit facility code:            1212
  Exit error code:               29569
  Exit message:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
  Start time:                    2017-05-15 23:31:41
  End time:                      2017-05-15 23:32:13
  Requested action:              Patch
  Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup …
0
I've been wrestling with these expressions for a while. I just don't know how to put these two expressions into one tidy expressions (if it's even possible).

=CSTR(First(Fields!DaysToExpiration.Value, "Revoked"))

=Fields!IsHidden.Value = "False"

Any help would be appreciated. Thanks!
0
Hello, was wondering if a new column is created in a tablix to give status of other column groups in case a row in any of the column groups gets blank. Am trying to put in an expression in that column created but it always gives me a null or error sometimes. any idea whats the best way to handle this. What i would like for the column to report is if other column groups have an empty field then write "missing information" on the created column for that row.
0
We are migrating over our ERP system to a new version. As part of migrating we are changing over from SQL 2008 to SQL 2014. When we create a view to a table in SQL 2008 and create a view to the same table in SQL 2014 and run them side by side, SQL 2008 finished producing the results in nearly half the time. We also notice that SQL 2014, while producing the results will pause every few seconds.

Any ideas on what is causing SQL 2014 to be running this data much slower than 2008?

(This slowness was originally noticed when reconnecting numerous MS Access 2010 applications to views that were created in SQL. They now run much slower when connected to the SQL 2014 Views) I don't believe the way the SQL views are created is the reason for this slowness due to the fact that we can connect directly to a table in SQL from Access and 2008 will still produce the results nearly twice as fast as connecting to SQL 2014 from access.


Thanks for any help or ideas!
0
sql server 11.0.2100

query is  



DECLARE  @FromBrCode INT =1001
DECLARE @ToBrCode INT =1637
DECLARE @Cdate DATE= '31-mar-2017'

SELECT a.PrdCd, a.Name, sum(b.Balance4) as Balance
FROM D009021 a, D010014 b
WHERE a.PrdCd=ltrim(rtrim(substring(b.PrdAcctId,1,8)))
AND substring(b.PrdAcctId,9,24)='000000000000000000000000'
AND a.LBrCode=b.LBrCode
AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode
AND b.CblDate=
(SELECT max(c.CblDate) FROM D010014 c
WHERE c.PrdAcctId=b.PrdAcctId
AND c.LBrCode=b.LBrCode
AND c.CblDate<=@Cdate)
GROUP BY a.PrdCd, a.Name
HAVING sum(b.Balance4)<>0
ORDER BY a.PrdCd

Open in new window


1.  this particular query taking too much time to complete execution
2. same problem in diffrent sql server
3. no table lock found , processor and memory usage normal while query running
4. normal "select top 1000 rows" working and showing output instantly in both table (D009021 , D010014)
5. reindex and rebuild done in both tables but problem not resolved (D009021 , D010014)
6. same query is working if we reduce number of branch
(
DECLARE  @FromBrCode INT =1001
DECLARE @ToBrCode INT =1001
)


7> same query is working if we replace any one variable and use value directly


AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode


changed to


AND a.LBrCode BETWEEN 1001 AND @ToBrCode


8> same query is working and giving output within 2 mins if we add "OPTION (RECOMPILE)" at end


9> tried to clean cache query execution plan and optimized new one but problem not resolved


10> found query estimate plan and actual execution plan is different please check screen shot


screenshot1.pngscreenshot2.png  
thanks in advance for any help.
0
We are running SQL Server 2008 R2 Standard Edition (SP3)

Today we successfully migrated all of our Databases from one Server to another.

The Source server was named SQLSERVER  with one default instance and we named the  Destination Server  SQlServerNew

We performed the following:
Installed a Default Instance on SQLServerNew
Scripted out logins on SQL Server SQLSERVER and recreated them in SQL Server SQlServerNew
Attached all of our DB FIles to the Destination SQL Server  SQlServerNew Instance
Shutdown the source server SQLSERVER
renamed the Host Server from SQLServerNew  to SQLSERVER
ran sp_dropserver and sp_addserver to rename the internal  SQL Instance name and restarted the Services
 
There were a few orphaned users - but other than that, everything was fine - except for replication.

Somehow, our DIstribution Database ended up as a 'user' database instead of a System Database  and when I execute sp_helpdistributor it returned all NULLS.

Also, when I execute :
exec sp_replicationdboption @dbname = N'MYDB, @optname = N'publish', @value = N'true'

I get this error:
Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 56
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'CM' has been set to false.

Can someone assist in getting Replication re-enabled on this new SQL Server Instance?
0
HELLO...

We have an MSSQL-2008 based application.  One of our tables, the "AR_CUST" table...and, in particular, the "AR_CUST.TAX_CODE" field, has seen some very strange and inappropriate updates as of late.  In fact, in a number of instances, we are seeing "NULLs" in this field, where we *KNOW* there was valid data previously.

So, our quick question...is there any way, that we can "trap" who/what is being updated to, into this field?  Perhaps some sophisticated form of a SQL Trace...or any other method, that anyone can recommend, that is *easy* to implement for troubleshooting purposes?  We just need to get to the bottom, of why/how this (and some other) fields, are getting updated, and when.

Please help...we'd appreciate it!...Thanks!...Mark
0
With VBA, in MS Access 2013, I need to be able to read a MS SQL Server database diagram. I am working with a .accdb
0
Free Tool: Port Scanner
LVL 8
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

favorite
I have 2 tables called 'table123' and 'table246'.

'table123' columns: 'ID', 'Dept_ID', 'First_Name', 'Surname', 'Salary', 'Address'.

'table246' columns: 'Dept_ID', 'Dept_Name'.

I want to find the list of employees with the lowest salary per department. Two of the ways I can do it is an Equi-Join or an Inner-Join. I've been told they can both be used to provide the desired result.

The queries I used:

Equi-Join:

SELECT First_Name, b.Dept_Name, alt.Min_Salary AS Min_Salary
FROM table123 a, table246 b,
(SELECT Dept_ID, MIN(Salary)Min_Salary
FROM table123
GROUP BY Dept_ID)alt
WHERE a.Dept_ID = b.Dept_ID
AND a.salary = alt.Min_Salary
AND a.Dept_ID = alt.Dept_ID;

Open in new window


Inner Join:

SELECT MIN(Salary)Min_Salary, Dept_Name
FROM table123 a, table246 b
INNER JOIN (SELECT First_Name, MIN(Salary)
FROM table123
GROUP BY Dept_ID)alt
ON b.Dept_ID = alt.Dept_ID;

Open in new window


The Equi-Join statement gives me the desired table, containing the columns 'First_Name', 'Dept_Name' & 'Min_Salary', with all relevant data.

However, the Inner-Join statement doesn't run because the First_Name column needs to be included in the aggregate function or GROUP BY clause. This really confuses me, as I don't know how to go about fixing it. How can I adjust the Inner-Join query, so as to give the same result as the Equi-Join query?
0
Hi,

I am not able to paragraph alighment with justify in SSRS 2014. Can you please help.
0
Hi Experts,
I need help rewriting my SQL Server query to determine whether or not to include a WHERE clause.
I have included my query below.

I want the WHERE clause to be included whenever the @Team variable is greater than 0, and exclude it if it is 0.

How can this be done?

Current Query:
Current query being used
thank you for your help,
mrotor
0
I am working on a fee collection software in multi user environment, where two users can work on same module without any conflict and clashing of data.
For example.
I am working on one computer and receipt no is 5 and same time another user is working on another computer and the receipt no is 5, and receiptno column in database is  with not null and UNIQUE features. let say if we both user want to save the records from multiple computers, how will it be allow to save. Right now it i m using locking to in C# that allow only one user can access the data from database and wont be free from locking unless I save the current records. One more thing receipt no must be consistently.

I hope u can understand my problem.


Thanks
0
Hi,

I have some users in my company that access our Production SQL instance via SSMS. I would like to know if there a way to block all domain users from using SQL Management Studio, except domain admin, as domain admin has 'sysadmin' rights.

We have software that utilizing Integrated Security.  I would still like our users to be able to utilizing our software using the SQL Windows authenticate, but not able to logon via SSMS.

Thanks!!
0
I have an Access database linked to a SQL Server 2008 R2 backend database.

Several tasks I want my users to be able to perform involve running jobs which have already been defined and which run on a scheduled bases.  Occasionally, they need to be able to perform the associated tasks at a time other than on the designated schedule.  The way I'm attempting to do this is via a pass-through query which does not return records, and the code follows:

Set qdf = DAO.QueryDef
Set qdf = GetDb.QueryDefs("qry_SQL_PassThru")    
qdf.SQL = "exec msdb.dbo.sp_start_job 'MyJobName'"    
qdf.Execute dbFailOnError

When I run this database while logged on as the SA the process runs smoothly, but when a client runs this from their desktop, they are getting the following error message:
error messageThe SQL Server is configured with Windows and SQL Server authentication, and the user(s) who are attempting to run this code from their computers have read, write permissions to the database the job applies to.  I've been unable to figure out a way to explicitly give these users permission to run this/these SQL Server jobs

Any help would be greatly appreciated.
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.