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

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
[Webinar On Demand] Database Backup and Recovery
LVL 11
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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
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
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,

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
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
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.

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
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
We have numerous processes that use a specific view:   MyDatabase.dbo.My_View

I'm interested in changing this view from a VIEW to a TABLE by use of running a stored procedure each night that will take the view code and create a literal / actual table in the DB to be used in place of the VIEW in all of our processes.

My question is this:

If I change a view by CREATING a new TABLE with the exact SAME NAME, will I need to do any code changes in any of my processes in order to have the processes use the VIEW code...?

(Note my plan is to DELETE the original VIEW and ONLY have the new table be active in the DB which will have the exact same name as the original view...)

I look forward to any feedback.

Thanks
0
Hi
I have a got a list of files in a folder text file with names like this:
baseFile_03_17_2017 .txt
baseFile_04_17_2017  .txt
baseFile_05_17_2017  .txt

Keep in mind>> there is no date column within each the text file.

 I want to a do a SSIS (SQL Server Integration Services)  loop where I loop through each file and insert it into a stage table which has an extra empty “Reportdate” column at the very end.

The SSIS job then gets the date from the file name and inserts it into the very last “ReportDate” column in that stage table


How do I do that?

Thanks
0
I am trying to write a query to get the DATEDIFF between consecutive rows, could be 2 or more based on the value in the first column (grouping).

For example, I have two people who recorded times and I need the sum of the duration between the times for each individual.  I can get the following results (see attached).  I am looking to return Bob > 119 minutes and Steve > 105 minutes

example.xlsx
0
Hello guys,

I need to do a process where I need to have two triggers disabled on the start process and after finishng it, start the trigger again without afecting the other users that is using the table.

is it possible? if so, how to?

thanks
alex
0
ServerA (source server) is running Windows server 2008 with SQL server 2005 SP4 which is hosting a 90GB database.  ServerB (destination server) is fresh, scratch built VM running Windows server 2012 R2 with SQL server 2008 R2.  I've researched various ways to migrate the database (backup/restore, log shipping, etc).  Which is the easiest method to migrate this 90GB database?   I have a 2 hour maintenance window to get this done.  This is my first time being tasked with migrating a SQL database, so I do not know what to expect nor do I know of any gotchas that may occur during the process.Any tips/advice would be greatly appreciated.
0
Free Tool: IP Lookup
LVL 11
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Hi Experts,

I have this:
CONVERT(varchar(8), DATEADD(MILLISECOND, SUM(TimeProcessing), 0), 8)/Count(Name) as TimeTook

Open in new window


But am getting error:
Conversion failed when converting the varchar value '03:18:20' to data type int

Please help and thanks
0
Experts - I need to find week numbers from a specific month number and a start day.

Ex:

In SQL Table,  I have FiscalDate as smalldatetime column which will have dates from 2017 start to 2030 end.

Now, I need an SP which should have a parameter of month number and starting day.

Ex:
Exec SP_UpdateFiscalWeek(7, 'Sunday')

The above sp should consider July as the first month of the year & calculate the fiscal week's accordingly for the whole datasets & update Sunday as the starting day for week's calculation.
0
I have following table ,

 CREATE TABLE StockTransferDetails    
    (    
          StockTransferDetailsId numeric(18,0),
          StockTransferID numeric(18,0),
          SerialNo smallint,
          ItemMasterId smallint,
          Unit varchar(10),
          UnitQuantity decimal(18,3),
          TransactionType char(1),
          ReferenceStockTransferDetailsId  numeric(18,0)
    )
   
    INSERT INTO StockTransferDetails VALUES    
    (79189,      9360,      1,      11111,      'BOX',      52,      'I',      0),    
    (79190,      9360,      2,      6777,      'BOX',  52,      'I',      0),    
    (79191,      9360,      1,      11438,      'BOX',      2,      'R',      79189),
    (79192,      9360,      1,      9117,      'BOX',      2,      'R',      79190),    
    (79193,      9360,      1,      11361,      'BOX',      34,      'O',      79189),
    (79194,      9360,      1,      1172,      'BOX',      33,      'O',      79190),
    (79195,      9360,      2,      11433,      'BOX',      1,      'O',      79190),
    (83657,      9776,      1,      16035,      'BOX',      1,      'I',      0),    
    (83658,      9776,      2,      11284,      'BOX',      5,      'I',      0),    
    (83659,      9776,      1,      609,      'BOX',      1,      'O',      83657),    
    (83660,      9776,      1,      478      ,   'BOX',      4,      'O',      83658),    
    (83661,      9776,      2,      6195,      'BOX',      1,      'O',      83658),    
    (101,      9000,      1,      1000,      'BOX',      10,      'I',      0),    
    (102,      9000,      2,      11284,      'BOX',      5,      'I',      0),    
    (103,      9000,      1,      1000,      'BOX',      1,      'O',      101),    
    (104,      9000,      1,      1000,      'BOX',      4,      'O',      101),    
    (105,      9000,      2,      1002,      'BOX',      1,      'O',      101)

I prepared one SQL,

 Create table #FinishedReceipt
    (
          StockTransferID numeric(18,0),
          StockTransferDetailsId numeric(18,0),
          SerialNo smallint,
          ItemMasterId …
0
Collation Error while joining the tables. can somebody help.
0
I have a database server that is about 4 years old.  we have a bunch of users that point their applications to this server.

the name of the server is    Alpha

We want to upgrade the sql server version from 2012 to 2016.   I think i would like to create a new server and do a clean install of sql server instead of doing an in-place upgrade.

So the new server would be bravo......  (if we do not do an in-place upgrade)
isn't it true that if i get the permissions right on bravo and restore all the databases on bravo.  that i could thereotically change the name of Alpha to lets say Alpha1 and then rename Bravo to Alpha that the users would not need any configuration changes.

Can someone please verify or send me to a document that outlines the best way to do this.
0
SQL Server 2012

Question
1) Why does SSMS duplicate Role permission when I add it through a SQL statement?
2) Is it a problem?

Detail
If I add permission to a Role in SQL Server through ssms I can find the object and and check the permission (eg grant Insert and Update)
However I have noticed if I add the permission through a SQL statement,  I end up with 2 Insert check boxes and 2 Update check boxes.
         GRANT Insert ON  [dbo].[tbl_Menu_Items] TO [RL_Admin]
         GRANT Update ON  [dbo].[tbl_Menu_Items] TO [RL_Admin]

SQL Roles Issue
Thanks in advance for any help
LJG
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.