Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Microsoft SQL Server 2008

49K

Solutions

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

Repair Corrupt SQL Database with Stellar Phoenix SQL Database Repair Software
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovery of Deleted records and recovers all components of SQL Database.
0
What is SQL Server and how does it work?
LVL 1
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Hello ,
Need help in this query, please see the excel sheet for the expected result.

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
	SELECT   
			 Z.Name ,
			 Z.Allocation, 
			  Z.Allocated ,
			 Z.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,

Open in new window

0
My task is to archive records from these original tables to the archive tables as shown below; however the original tables have foreign keys define and table 2 has no date to be used in the INSERT where clause predicate -12 week. what would be your approach here, can you give me the directions and script.  thanks,
 



      Original Tables

(1)
CREATE TABLE [dbo].[JSServicesCheckListLog](
         [ServiceLogID],
      FK-[CSC],
      FK-[ServiceTypeID],
         [Comments],
         [ChecklistCompletedDate],
         [ChecklistCompletedBy],
         [CleaningDate],
         [NoShow],

(2)
CREATE TABLE [dbo].[JSServiceChecklistDetailsLog](
      PK-[ServiceDetailsLogId],
      FK-[ServiceLogID],
      FK-[ServiceWorkAreaDetailsID],
      FK-[RatingID],
         [Comments],



      Archive Tables

(1)
CREATE TABLE [Archive].[JSServicesCheckListLog](
      [JSServicesCheckListLogID],
      [ServiceLogID],
      [CSC],
      [ServiceTypeID],
      [Comments],
      [ChecklistCompletedDate],
      [ChecklistCompletedBy],
      [CleaningDate],
      [NoShow],
      [LastUpdateTimestamp],


                         
(2)

CREATE TABLE [Archive].[JSServiceChecklistDetailsLog](
      [JSServiceChecklistDetailsLogID],
      [ServiceDetailsLogId],
      [ServiceLogID],
      [ServiceWorkAreaDetailsID],
      [RatingID],
      [Comments],
      [LastUpdateTimestamp],
0
I want to try to optimize some code by placing combining a call to a stored procedure with a SQL select statement.
The stored procedure is called taSopLineIvcInsert.  I have a temporary dataset stored in SOPLINES and SOPHEADR. I am passing down the order number to another stored procedure and I want to select all the lines that belong to this order. As each line is found I then want to call the stored procedure taSopLineIvcInsert that will insert each record into the final production database. I want to do this without using a cursor. How can I restructure the attached stored procedure to accomplish this?
SQL_Loop.sql
0
Hi,

My MS SQL database has 27819.00 MB size with 19878.16 MB Space Available, by my calculation this means that the actual database size is 7940MB. How can i reduce the amount of diskspace that the database is using without loosing any data?

Thank you
0
While i try to copy data from one table to another table in sql server 2008 i am getting below error.

There is already an object named 'table123' in the database.

Just i am copying this table to another database.

i already created schema in another database with same table name.
0
We want to run a store procedure (that displays tables of current database) without creating the SP in every DB.  How can we do this?
0
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x00000000). It occurred during a read of page (1:4825942) in database ID 5 at offset 0x000009346ac000 in file 'G:\DATA1\MSSQL\Data\AdMail50_Data.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Please help me how to fix.
0
Hi Team,

SQL DB corrupted i want to rename this db and restore from backup.
But this DB is part of Transactional Replication.
Please suggest.
0
SQL-MPSQL-MPHello,

Question on SQL 2008 backup files when using a manual backup process (right-click on database, choose "Tasks" -> "Backup") versus using a maintenance plan in SQL Server Management Studio.

I've noticed that when I use a SQL 2008 maintenance plan the backup files for a particular database, the .bak is only ~ 9 GB. But when I manually backup this same database by right-clicking on the database entry and choosing "Tasks" -> "Backup" that the .bak file is ~ 18 GB. I've confirmed the maintenance plan is targeting the correct database, and the MDF and LDF files are approximately 9 GB and 2 GB respectively. Screenshot of the maintenance plan structure is attached.

Could this be considered normal?

Thank you in advance.

Jason
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

hi all.  I need to compare source_table with target_table.  if target table has same rows as source then delete the those rows from target, and then insert the new  rows from source into target.  What is the best way to do this?  I tried merge but I am having issues.

thanks!
0
I have a request of how to write an Execute SQL task in SSIS.   More of a fail safe for when a vendor's file does not make it to our system.    This is causing the below issue.

I have a SQL job Step 1 that successfully uploads a CSV File from a WINSCP Windows task script to retrieve the latest CSV file from a SFTP site.   Then the SQL job Step 2 sends the file to the SSIS package for loading into SQL.   The problem occurs when the SFTP site does not receive a new file.  
Step 1 does not fail because it successfully retrieved the latest file, however, Step 2 using the package fails because the file that was pulled already exists from the previous pull.   I need a way to check in the SSIS package to see if the dynamically named CSV file exists before processing.    I am very green to writing variables and I have never used the Script task command.  Is there a way to do this check before my package kicks off the For each file task by using the Execute SQL task  ?    

As a side note, I do not have permissions to delete older files off of the SFTP site.    And, even though the SQL Job Step 2 says it failed, it still executes the CSV file so now I have duplicate records for the same day.

Please advise exact steps as I am a beginner.
0
Points of My Scenario
1. I am admin of a Reporting Services (2008 R2) website that has been working until this morning.
2. Issue: users are now unable to authenticate/access it by its FQDN (fully qualified domain name) - it keeps prompting for credentials
3. When users type IP address instead of the FQDN, they can access the website

QUESTION: How can I resolve this issue so that users can once again access it via the FQDN?
0
hi,

what are differences between index rebuild vs index reorg while getting statistics

when to use which one. Advantages, disadvantages of each

please advise.
0
hi,

when we use below optins like

verify backup integrity and
 set up back up compression

what it mean by setting
 set up back up compression  to server level?
how defragmentation different from decompression?
please advise
0
hi,

how mirroring and alwayson are diferent. What are various types of mirroring. what are advantages of each.

what i rpo and rpt and ms hcl means
please advise
0
===================================

Failed to start debugger

===================================

The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476

------------------------------
Server Name: FINGRE-PRINT\SQLEXPRESS
Error Number: 229
Severity: 14
State: 5
Procedure: sp_enable_sql_debug
Line Number: 1


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior …
0
Hello ,
Please find the query which needs a condition to be added :
;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT   
                   Z.Name ,
                   Z.Allocation, 
                    Z.Allocated ,
                   

Open in new window

0
SQL Server 2008
0
Free learning courses: Active Directory Deep Dive
LVL 1
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

I have 3 servers for this scenario. webserver1, newwebserver, sqlserver.
webserver1 uses SQLserver 2008 R2 for SQL Reporting Services
newwebserver uses SQLserver 2012 R2 for SQL Reporting Services
sqlserver uses SQLserver 2008R2.
webserver1 have been connected for SQL Reporting services to sqlserver.
Today, we tried to add newwebserver for SQL Reporting service to sqlserver, and for some reason it screw our current webserver1 SQL REporting services.
I need to be able to reconnect my webserver1 via SQL reporting services with my sqlserver. I have the "SWL REport key.snk" and password that was originale used to create this service. i have been trying to restore and error below.
encryption keyserrori need to be able to reconnect back. I already disconnect newwebserver, but i believe it is still connected for some reason. what i can do to reconnect back to ReportServer database.
0
Hello,

I have this query which works perfectly but need few modifications :

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     TABLE_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM   TABLE_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
      

Open in new window

0
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as well as instant password recovery. 
0
 

Expert Comment

by:Pantea tourang
Comment Utility
I have used it and i can say Good software to deal with i will surely recommend this to my colleagues
1
Good morning experts! As I fumble my way around sql I have what I think is a very simple join between a table and a view. Below is my sql statement but when I attempt to run I receive the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I have zero clue on how to resolve. What am I missing?


select dbo.MasterCostCodeList.Level1, dbo.MasterCostCodeList.Level2, dbo.MasterCostCodeList.Level3, dbo.MasterCostCodeList.Description, dbo.MasterCostCodeList.Active, dbo.CPtoTCP_ImportB.Project,
dbo.CPtoTCP_ImportB.[2nd Level], CPtoTCP_ImportB.[3rd Level], CPtoTCP_ImportB.[Desc], CPtoTCP_ImportB.ORG_ID, CPtoTCP_ImportB.Expr1
From dbo.MasterCostCodeList
inner join dbo.CPtoTCP_ImportB on dbo.MasterCostCodeList.Level3=dbo.CPtoTCP_ImportB.[3rd Level]
where dbo.MasterCostCodeList.Active = '1'
and CPtoTCP_ImportB.Expr1 = 'Y'
0
How to search multiple email address in below SQL query.....

------------------
Declare @EmailId varchar (max)
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

Declare @String4 varchar (Max)

Set @String4 ='select * from EmailSummary ES
where ES.'+@EmailIdWhere+'
----------------------------------------------------------------------------------
0
Good Afternoon,

I’m having an issue with my maintenance plan that I need help with.

My environment is as follows:
SQL Version: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
OS Version: Windows Server 2008 R2 Standard x64

I have two sub-plans in my maintenance task:

Subplan1 (starts at 6pm):
  • Check Database Integrity Task (checks 1 database and has the option checked to include indexes)
  • Maintenance Cleanup Task

Subplan2 (starts at 8pm):
  • Back Up Database (Full) (The database being backed up is about 126 Gbs in size)

Upon checking one of our SQL servers the other day, I noticed that both tasks in subplan1 were disabled. Only subplan2 was enabled. The backup was functioning properly from what I could see.

I checked with my colleagues to see why these tasks in Subplan1 were disabled but no one knew, so thinking nothing of it, I enabled both tasks in Subplan1.

The next morning I was greeted with an error with subplan1 regarding the failure of the “Check Database Integrity Task”. This error below:

Error Number: -1073548784
Error Message: Executing the query "DBCC CHECKDB(N'MYDATABASE')  WITH NO_INFOMSGS
" failed with the following error: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The transaction log for …
0

Microsoft SQL Server 2008

49K

Solutions

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.