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

Hi

i have installed sql 2014 with a named instance on a server which has also sql 2008 running there. i'm facing a timeout issue every second time when i try to login with a DSN (ODBC). saying "unable to complete the login process due to delay..."

Please advise.

Joseph
0
Hello Folks,

I've a stored procedure in SQL Server along with lots of calculation & computation. Once, I execute it takes about 11 seconds.
But once concurrent users executes then it takes average 42 seconds.
FYI,
1. I am having SQL Server 2017 at local and production with Azure' DB as a service.
2. Isolation level is "Read Committed Snapshot"
3. I've marked NOLOCK hint with all physical tables in stored procedure.
4. Yes, the procedure is also using Temp Tables too.

Do you have any thoughts, why it is taking lots of time?

Best Regards
0
Hi,
This query works if the ISAdmin and Ispoweruser is false but fails when ISAdmin or IspOwerUser =True .
ALTER PROCEDURE [dbo].[RecentJobs_St] (

  @SelectedDateTime			DATETIME
, @AccountName				NVARCHAR(200)
)
AS
BEGIN

SET NOCOUNT ON 

DECLARE @SQL	NVARCHAR(2000)
DECLARE @IsPowerUser		BIT
DECLARE @IsAdmin			BIT
DECLARE @UserID				INT

-- This query gets a UserID if the user is a power user or an admin
-- If they are then they can see all records, otherwise just their own.

SELECT @UserID = ID
, @IsAdmin = IsAdmin
, @IsPowerUser = IsPowerUser
FROM Users U
WHERE U.AccountName = @AccountName

IF @IsAdmin IS NULL
  SET @IsAdmin = 0
IF @IsPowerUser IS NULL
  SET @IsPowerUser = 0 

SET @SQL = 'SELECT TOP 50 * 
FROM JOB_Recnt R
WHERE CONVERT(NVARCHAR(20),R.[Date Time],112) <= ' + CONVERT(NVARCHAR(20),@SelectedDateTime,112) + ' '

IF NOT (@IsPowerUser=1 OR @IsAdmin=1)
  SET @SQL = @SQL + ' AND UserID IN (SELECT ID FROM USERS WHERE UserGroupName = ''XYZ'')'

SET @SQL = @SQL + ' ORDER BY CAST([Date Time] AS datetime) DESC '

Open in new window

0
Hello,
How to get an array  of  result and use the array in another query in a stored procedure.
Please find the scenerio in the attachment.

Regards,
Example1.xlsx
0
I'm trying to write a SQL Pivot Query and can't get it quite right.  I feel like it's not grouping correctly.


CREATE VIEW [Report]
AS
SELECT

        [report].[Type],
      [table].Sales,
      [table].Service
      
      FROM [Report_Status] [report]
      
      FULL JOIN
      (
      Select

          Type,
          Min([Sales]) as Sales,
            Min([Service]) as Service
                        

      From
      (
            SELECT
            'Sales' [Header],
            [details].Type,
            [details].StatusCount            
            FROM Report_Status [details]            
            WHERE
              [details].[ManType] = 'Sales'

            UNION


            SELECT
            'Service' [Header],
            [details].Type,
            [details].[StatusCount]
            FROM Report_Status [details]            
            WHERE
               [details].[ManType] = 'Service'

            ) t1

            PIVOT

            (
                  MIN(StatusCount)
                  FOR Header IN ([Sales], [Service])
            )

            as PivotTable

            GROUP BY Type
            ) [table]

      ON [report].[Type] like  [table].[Type]
0
Hi

Using SQL 2008

I have put together the following query

select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
cc.[Dept]
FROM Employee.Appointment_History h
    JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
    JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
      JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
    JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]
'
where p.[Informal Name] in ('John Smith', 'Sarah Smith')
group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept]
 order by  p.[Informal Name] , sdate

Example outputs is below
             
Person number      Post Name      Pattern Number      SDate                                     Dept      
10921                      Solicitor              15760                      2013-11-13 00:00:00.000      xxxx             
10921                      Solicitor              15760                      2014-04-28 00:00:00.000      xxxx                     
10921                      Solicitor              15760                      2014-05-16 00:00:00.000      xxxx                    
10921                      Solicitor              14766                      2018-05-25 00:00:00.000      xxxx             
12205                      Handler           15760                      2015-08-17 00:00:00.000      xxxx                  
12205                      Paralegal      15760                      2018-06-25 00:00:00.000      xxx          
I want to add an enddate to the query, I have put the …
0
Hi ,

Please Give me Solution for my Below Issue,

Got Issue like The OLE DB provider "MSDASQL" for linked server "MYSQLDB" reported an error. The provider ran out of memory.
0
I am trying to copy several SQL jobs from our test server to production. When I run the scripts to drop and create these jobs I get messages like these.  What must I do to these scripts to get them to run successfully on the production server?

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 43
The specified @job_id ('C09EF46A-E6EC-4074-B5F3-A932F5B5B495') does not exist.
Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 203
The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).
0
Hello,

Need a little tweak here
DECLARE @LoggedDate1 AS DATE = '2018-06-11'
DECLARE @LoggedDate2 as DATE = '2018-06-12'




SELECT  Ref as [Ref No], Garage, EstFinishDate,Resolution ,IsNotActive as [Job Completed]
FROM  TABLE1 WHERE IsDeleted is null AND  (  LoggedDate >= @LoggedDate1    AND LoggedDate  <= @LoggedDate2)

Open in new window


In the results how can I display
[Job Completed]= 'Completed' when IsNotActive = True

Thanks
0
I have a table that has 86 GB, I want to purge it and keep only one-year data. After purging,  about 50 GB removal.

Questions

1. My deletion operation is committing every 1000 rows to delete till to the end.  I want to know if the deletion process itself will cause any or huge performance issue?

2. After deletion process, to reclaim the disk space, I need to shrink the database. Is it a way only shrink a table or entire database?

3. During the shrinking database process, I want to know if the process cause any or huge performance issue? how to avoid or minimize the impact?

4. During the shrinking database process, I want to know if I need to stop all the backup jobs ( Full, Diff, Log)?

5. After the shrinking process, I need to rebuild all indexes in the database. I want to know if the process will cause any or huge performance issue? how to avoid or minimize the impact?

6. If I run the purge, shrink, rebuild indexes on  a stage machine, then import the table into production, then swap it with the original table, then drop the original 86 GB table. Dropping a big table will automatically reclaim a disk space. Is this a better way to do this way than directly running deletion, shrink and rebuild index on production?


My server is mssql 2012
0
Hi,

I want to print sequentially in a column. my query displaying select,1,10,11,12,etc.

But, i want to display select,1,2,3,4,5,6,7,etc

how to do this...?

below is my SQL query.

Select ' Select ' Emplevel,'' EmpId Union
select EmpLevel, ISNULL(EmpId,'') EmpId
from tbl_Employee order by Emplevel
Sample.png
0
I need to find all of the SQL Server (2008 R2) objects which one user has SELECT or Execute permissions on which another user doesn't have the same permissions.
0
Hello Experts,
I am trying to rewrite an existing DTS package to SSIS package.  The very first step is (ActiveX Script Task Properties), where I have vb scripts, which picks up a text file and writes the data in a SQL table.  

1.  I am using VS 2008 to create the SSIS package.  The question is, what is the option in Toolbox, I need to select to do the job?   I need to select in SSIS tools
2.  Also, I have many steps, where I used Execute SQL Task Properties for coding the SQL Statements.  In SSIS, what is the option I need to select.

Please try to help.  Thank you very much in advance.
0
I have the following query:
with orders as(
	select SalesOrderID, CustomerID, OrderDate
	from Sales.SalesOrderHeader
	where OrderDate = '2005/07/01'
)
select c.CustomerID, orders.SalesOrderID, orders.OrderDate
from Sales.Customer as c
left outer join orders on c.CustomerID = orders.CustomerID
order by OrderDate desc

Open in new window


Can we achieve the same thing using just Left outer join without using CTE?
I did something like below, but it didn't work well.  I thought that the following query gets all the customers regardless if he/she placed an order on 2005/07/01.
select c.CustomerID, h.SalesOrderID, h.OrderDate
from Sales.Customer as c
left outer join Sales.SalesOrderHeader h
on c.CustomerID = h.CustomerID
where OrderDate = '2005/07/01'
order by OrderDate desc

Open in new window


Can you please explain what I did wrong ?
0
I had this question after viewing SSIS error VS_NEEDSNEWMETADATA.


have just run my SSIS package through SQL Agent and recieved the following error.

"component "Schedules01" (426)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".  End Error  Error: 2009-05-24 04:16:10.30     Code: 0xC004700C     Source: AllSchedules DTS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-05-24 04:16:10.32     Code: 0xC0024107     Source: AllSchedules      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  04:15:12  Finished: 04:16:10  Elapsed:  58.828 seconds.  The package execution failed.  The step failed.

I tried below solution :

deleted the data source and recreated it.
refresh the mapping  
recreated the whole task

I am not sure what it all means. Can anybody help?
0
Hi EE,

We have several databases of various compatibility levels on SQL server 2008 we want to trial on SQL server 2017.
Other than setting up a  sandpit environment of SQL server 2008 and upgrading the databases to SQL server 2017 is anything we can to restore these individual databases to SQL server 2017.  

Any suggestions are appreciated I am willing to look at tools as well.

Thank you
0
Three databases: one restored with 2017, two created with 2014. I can attach the first but not the second two - I get a 5123 error. All three have the same security settings. All three are in the same directory and SQL Server has access to that directory.
0
I have a csv flat file full of customer names and addresses. One of the goals of this import process is to clean up addresses. In SQL I have added an additional field called MOD_ADDRESS. This is an exact copy of the ADDRESS field. It will be this field I will be correcting via other scripts. Is possible to populate both the ADDRESS and MOD_ADDRESS fields during the initial import task from the one input column? Since I can map the input column called ADDRESS to only one SQL column, how can I also populate the MOD_ADDRESS column at the same time?
0
Hello,
I have a field 'Ref' set to autonumber but it does not increment with addition of a new record.

CREATE TABLE [dbo].[tABLE1](

	
	[Ref] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Vece] PRIMARY KEY CLUSTERED 
(
		[Ref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window


Thanks
0
We are using SSAS 2016 Enterprise

We are building a mega cube with multiple companies data and we want to partition by Company.
We also will want to schedule separate ETL schedules for each company.


Question is in the event there is failure from a partition perspective will that take down the entire cube? In the event the data from one company goes wonky?
0
hi,

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.
0
Hello!

I have some performance problems with the following query:

exec DBP_Obtener_Reporte_Productividad_Trabajador @pcodi_operacion=N'02',@pcodi_lugar=N'',@pfech_inicio=N'2017/04/01',@pfech_termino=N'2018/04/30',@codi_usuario=N'SISTEMAS'

Open in new window


USE [PACKING]
GO

/****** Object:  StoredProcedure [dbo].[DBP_Obtener_Reporte_Productividad_Trabajador]    Script Date: 05/23/2018 12:34:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

  
 
  
CREATE PROCEDURE [dbo].[DBP_Obtener_Reporte_Productividad_Trabajador] (      
@PCODI_OPERACION   VARCHAR(3),      
@PCODI_LUGAR       VARCHAR(3),      
@PFECH_INICIO      VARCHAR(20),      
@PFECH_TERMINO     VARCHAR(20) ,      
@CODI_USUARIO      VARCHAR(100)    
)      
AS      
BEGIN       
    
SELECT A.*     
  INTO #DETALLE_ORDEN_PDT    
  FROM DETALLE_ORDEN_PDT  A  
    
    INNER JOIN  (    
    SELECT DISTINCT CODI_OPERACION,CODI_LUGAR  
    FROM BDGESTIONUSR.DBO.INFORMACION_USUARIO     
    WHERE  CODI_OPERACION = @PCODI_OPERACION    
    AND CODI_LUGAR    LIKE '%' + @PCODI_LUGAR +'%'       
    AND CODI_USUARIO  = @CODI_USUARIO ) USER_    
  ON A.CODI_OPERACION = USER_.CODI_OPERACION    
  AND A.CODI_LUGAR = USER_.CODI_LUGAR      
  WHERE CODI_COMPANIA IS NOT NULL AND     
 A.CODI_OPERACION =  @PCODI_OPERACION  AND     
 A.CODI_LUGAR  LIKE '%' + @PCODI_LUGAR +'%'      AND    
  CODI_SECTOR IS NOT NULL AND     
  LOTE_KEY IS NOT NULL AND     
  CODI_LINEA IS NOT NULL AND     
  CODI_TRABAJADOR IS NOT

Open in new window

0
Hi, I have a sample SQL Server 2016 database back up and would like to restore it on Azure SQL Database. Any ideas?

Thank you in advance.
0
Question - How do I use a variable for a table in the below SQL
      (The below errors out on -->  set @vcSQL)
      declare @intNewRecords_OutPut AS int
      declare  @vcTemp_Table  as varchar(50), @vcSQL  AS  varchar(max)      
      set @vcTemp_Table= 'tbl_zr_Invoice_LineItems_Add_Products'
      set @vcSQL= 'Select ' +  @intNewRecords_OutPut + ' = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products'
      exec (@vcSQL)
      print @intNewRecords_OutPut

This Works
      declare @intNewRecords_OutPut AS int
      Select  @intNewRecords_OutPut = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products
      print @intNewRecords_OutPut

Why do I want to do this
I have a stored proc that I use a temp table (#tbl_Temp).  But in case I want to do some debugging in the future, I want to be able to use a real table.  Hence send a parameter that would allow me to change tables on the above.  I know I can use an If statement with two statements, but I like to know the above.

Thanks in advance for any help.
LJG
0
Hi all,

firstly thank you for taking a look at my question - your help is invaluable!!

basically if I have the following table called a_debt:

row_id      de_account_number      de_listed_date
1      123    1/01/2018
2      124    1/01/2018
3      126    1/01/2018
4      128    1/01/2018
5      129    1/01/2018
6      123    2/01/2018
7      124    2/01/2018
8      126    2/01/2018
9      128    2/01/2018
10      129    2/01/2018
11      123    3/01/2018
12      124    3/01/2018
13      126    3/01/2018
14      129    3/01/2018
15      130    3/01/2018
16      131    3/01/2018
17      132    3/01/2018
18      123    4/01/2018

The data I am trying to pull is this.

column 1: count of the different account numbers loaded for each date
column 2: the date that has been counted
column 3: a count of the files for each date, that do not have a prior listed date, ie for the 4/1/2018 this column would = 1, for the 1/1/2018 the value would be 5, the 2/1/2018 would be 0 etc.

Is this something I could get help with?

many thanks!!

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