Microsoft SQL Server 2008Sponsored by Jamf Now

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

In SQL Server 2008/2012, how  do i check whether this patch KB4057114/KB4057116 is installed or not?
1
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I am calling a table valued function which is returning multiple columns in select subquery, yet I know that select query returns single column at a time only.
So how is it possible to do

subquery
     SELECT TOP (1) CRFCA.Name, CRFCA.Rate, CRFCA.ChemicalFamily, CRFCA.WHP, CRFCA.qty, CRFCA.totalMixer
            From fnChemicalRecordForChemicalApplication(@companyID) as CRFCA           
            WHERE .ChemicalApplicationID = chemicalApplication.ChemicalApplicationID
            ) AS value8

Open in new window


Function
   create function fnChemicalRecordForChemicalApplication(@companyID int)
returns table
as
return (
		SELECT  chemicalApplicationChemical.ChemicalApplicationID as ChemicalApplicationID, chemical.Name as Name, chemicalApplicationChemical.Rate as Rate,                                               chemical.ChemicalType as ChemicalFamily, chemical.Whp as WHP, chemicalApplicationChemical.Quantity as qty, chemicalApplicationChemical.TotalMixture as totalMixer
                FROM ChemicalApplicationChemical AS chemicalApplicationChemical              
                INNER JOIN Chemical AS chemical ON chemical.ChemicalID = chemicalApplicationChemical.ChemicalID
				WHERE  (chemical.CompanyID = @companyID)
)

Open in new window

0
Hi,

I have this query but it seems to take a really long time to complete. is there any modifications I could do that could perhaps make it a little more efficient?  I was envisaging for a year and a halfs worth of data, perhaps taking a few minutes, but I stopped it before at 23 minutes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @fromdate date
declare @todate   date
set @fromdate = '2017-01-01'
set @todate   = '2018-05-05'
SELECT 
c.de_number
,client.cl_number
,c.de_status
,Max(CASE  WHEN a1.co_type = '2'  THEN a1.co_description  ELSE c.de_status END) Prev_Stat
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments Netprin
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,CASE  WHEN c.de_cell_phone IS NOT NULL  THEN 'X'END Good_cell
,CASE  WHEN c.de_home_phone IS NOT NULL  THEN 'X'END Good_landline
,CASE  WHEN c.de_email IS NOT NULL  THEN 'X'END Good_Email
,Max(case when (co.co_description = 'DMOT Request for payment letter' OR co.co_description = 'DMOT Small Balance Letter') AND co.co_type = '0' then co.co_date end) first_demand_letter
,Max(case when co.co_description = '1st Demand Email' AND co.co_type = '0' then co.co_date end) first_demand_email
,Max(case when co.co_description = 'DMOT 48 Hour Notice' AND co.co_type = '0' then co.co_date end) 'DMOT-48hour'
,Max(case when co.co_description = '48 Hour  -Email' AND co.co_type = 

Open in new window

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

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
I set my sql server to sql server mixed mode authentication

Question
I add this Windows account, for example domain name\johnsmith to SQL server login
If I want to log into SQL server by this Windows account like domain name\johnsmith on SQL authentication, not on Windows authentication
Can I do that?

My SQL server is SQL 2012
0
Logon Login failed for user . Reason: Attempting to use an NT account name with SQL Server Authentication.

I have a Windows domain account like gooled\solarexpert that is set as sysadmin on mssql database, connect to Solarwind application.  The application connection is not successful. I check the database log, it is "....Reason: Attempting to use an NT account name with SQL Server Authentication."

I use this account gooled\solarexpert  on other database with same setting, it's no issue to connect. Most are nnncet5ed successfully, only few of them is with this error message.

Any advice?

I have tried almost all suggestions posted from web about this error, it still doesn't work.

My server is mssql 2012
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,

Anyone know how to scale out R server/service in MS SQL 2016 and later?
0
Hello ,

Could you please help me with this problem ?

Somebody change the Temp DB size and I want to know if is any method to find  that person .(sql log not help )

Thank you !

C.
0
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

I have several computers that use  SQL Express on their computers because of an EKG program which needs it to operate.  For some reason, I noticed that the SQL Server (SQLExpress) service stops randomly and I have to manually turn it back on.  This seems to happen only on the Windows 10 computers. The computers are using  SQL Server 2012, which was installed when the EKG program was installed.  What setting do I need to use to keep this from happening.  The makers of the EKG software told me it was our issue and not theirs.
0
As part of our failover process in SQL Server 2008 R2 we need to change the registry on our sharepoint servers so they are pointed to the right SQL box on the mirror. This has worked fine until we created 2 vm sharepoint boxes.  The step that changes the registry is in a text doc for each sharepoint box and is accessed using  Operating System (CmdExec) with 'regini -m \\SHP01 d:\scripts\SwitchToAppN1_SHP01.txt '  . With the VMs  the command is regini -m \\VMPRDWEBAPP01 d:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt. Error is
Executed as user: ########. REGINI: SetValueKey (CLSQLN1\Applications) failed (5)  REGINI: Failed to load from file 'd:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt' (5).  Process Exit Code 1.  The step failed.

Searches said this has to The problem was w ith permissions on the remote server.  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurePipeServers\winreg so we have added permission to the reg key for the user/svc-account but are still getting the error.

Any help would be appreciated.
0
Hi,
    We are facing some strange issues on our SQL server 2008 R2 environment where suddenly SQL Agent job which was executing DTS packages weren't returning any error or successful status . When we checked in back-end the file which were supposed to get processed by DTS weren't processed. we haven't changed any configuration change or package upgrade done. the same package was copied to other environment which ran successfully.
To test whether DTS tool are having any issue we created some test package which was executed properly without any issues.
 No error logs are getting logged either on SQL Agent Job-->Steps-->Advanced->Error log file or on package logging as well.
Even running the DTSRUN command on command prompt doesn't show up anything.

Any help would be much appreciated!!
0
I have a tempdb, I set tempdb (tempdb.mdf) initial size too large 40 GB some time ago, now it reachs to 40 GB. I want to reduce the initial size, but it cannot allow me to do that. Even I shrinkfile it, it cannot help much. I don't want to keep running out of disk space and add disk space. I want to reset the initial size to min like 0124GB

Any advice? I want to reset the initial size to something a low number like 1024 from 40GB.

I am running MSSQL 2012
0
ms sql management studio how to query select stored proc by passign inputs graphically without writing query.

one of my colleague passed inputs in one cool graphical option in ms sql management studio
not exactly sure how he was able to do it
please advise
0
I use VFP9 in my job. We want to move data from Microsoft SQL 2008 express to  Microsoft SQL 2017  express.  I have the string connection  for SQL 2008, but not for 2017,   what would be the string connection for SQL 2017 ? .  Basically I need to read from several tables of the old SQL  2008 ,  do some calculations and then write  to some fields on the new SQL 2017  ,  The tables are practically the same in both version (same name ). Is it possible to have both versions of SQL open at the same time ?
0
How to fetch duplicate record occurred in 5- 10 minutes of time interval?

SQL Database.

I will have to identify the list of duplicate transactions happened with in the range of 5- 6 mins.

BillId        Trans   DateTime
A100125  Paid  2018-04-18 11:21:40.873 
A100125  Paid  2018-04-18 11:24:40.873 
A100125  Paid  2018-04-18 11:30:40.873 
A100125  Paid  2018-04-18 12:30:40.873 

Open in new window

I can generate a report for date range. This is about average difference of 5-10 mins.

In the above data, expected data as output is


A100125  Paid  2018-04-18 11:24:40.873 
A100125  Paid  2018-04-18 11:30:40.873 

Open in new window

0
How do you access the global Address book in Outlook with SQL Server 2008?
0
I am trying to create a stored proc in SSMS 2008 and I'm having a performance issue. We've had accounts move from one SOR to another but not all the data transferred so I am having to use 2 linked servers (DB2 & TD-PROD) to get the data.
To build the #Acct_Base only takes a couple of seconds. The performance issue is coming from the #PMNT. There is a date that I need to use from #Acct_Base in the WHERE to get data for #PMNT but after 2 hours of running I cancel it.

How can I make this faster?

If OBJECT_ID(N'tempdb..#Acct_Base', N'U') Is Not Null
      Drop Table #Acct_Base

SELECT *
INTO #Acct_Base
FROM OPENQUERY(DB2,
'WITH LGL_STIP AS (
a.DEBTOR_NO
,a.FRST_PMT_DT
,a.ACCT_NUM
,ect
,ect......................
')

If OBJECT_ID(N'tempdb..#PMNT', N'U') Is Not Null
      Drop Table #PMNT

select x.* into #PMNT from
(SELECT distinct a.DEBTOR_NO
,a.FRST_PMT_DT
,a.ACCT_NUM
      ,p.LN_NO
      ,p.CUST_ACCT_ID
      ,p.DELTA_TRAN
      ,p.TRAN_EFCT_DT
      ,p.SEQUENCE
      ,p.PRIN_BAL_AM
      ,p.TOTAL_RECD
      ,p.INT_CHRG_AM
      ,p.TTLPMTS
from #Acct_Base  a
left join openquery([TD-PROD],'Select
CLIENT_NO
,LN_NO
,CLIENT_NO ||''-''|| LN_NO as CUST_ACCT_ID
,DELTA_TRAN      
,DELTA_PROC_DATE as TRAN_EFCT_DT
,SEQUENCE
,PRIN_PD as PRIN_BAL_AM
,TOTAL_RECD
,INT_PD as INT_CHRG_AM
,SUM(PRIN_PD + INT_PD) AS TTLPMTS
FROM EIW_S1.DLTALH02_FUNDS_PROC_CS
WHERE DELTA_TRAN in (170,175)
GROUP BY
CLIENT_NO
,LN_NO
,DELTA_TRAN      
,DELTA_PROC_DATE
,SEQUENCE
,PRIN_PD
,TOTAL_RECD
,INT_PD

')       as p      
0
Free Tool: IP Lookup
LVL 12
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.

I have a database that has a field with observations, which is entered by an application in which the user records all the comments made by the user, in which are uppercase, lowercase, letters of less than 4 digits, numbers letters, symbols.

what you want to do is extract the words that are most repeated and show it in another column.
Muestra_Aleatoria.txt
0
Hi,

I'm using SQL SERVER 2008R2, Visual Studio 2008 for SSIS.

When the parent stored procedure usp_TEST_PARM  calls a child stored procedure and there’s an error in the child stored procedure, how do we get the child stored procedure to RETURN  a value so that SSIS would know that an error occurred in the child stored procedure (so that SSIS can send an alert email that says that an error happened in the child stored procedure and not the parent stored procedure usp_TEST_PARM  )?

GO

/****** Object:  StoredProcedure [dbo].[usp_TEST_PARM]    Script Date: 03/08/2018 16:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*

EXECUTE dbo.usp_TEST_PARM 'parm1'

*/

CREATE PROCEDURE [dbo].[usp_TEST_PARM]

@PASS_PARM as varchar(50)='Opps'

AS

SET NOCOUNT ON

SELECT [Field1]='Hello',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello2',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello3',[Field2]='Seconds'

RETURN 3

SET NOCOUNT OFF


GO


/*
to run it:

declare @var1  int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO
-------------------
RESULT:

Hello	parm1
Hello2	parm1
Hello3	Seconds

----------------------
RETURN STATUS 
3

*/

Open in new window

0
Hello,

I am getting the attached error when I try to launch the SQL Sentry Client.

I login to the server as a domain admin that is supposed to have permission to the SQL server and its database.  I am using integrated windows authentication.

I login to the SQL server directly using the same domain admin account and I am able to see the database and its view tables in SQL Management Studio.

Please advise where I should look.  

Thanks.
0
What is the best way to upgrade 2008 SSIS packages to 2106 SP1

All script tasks disappear during regular upgrade . Please share some experience

thanks
Julia
0
i have a windows app exe which i want to run on another laptop on that lapop dosen't have the SQL SERVER  how should i run that exe with that databse on laptop without installing the SQL SERVER on laptop
how should i encrypt the data on the laptop
0
I will be running a MERGE SQL query to query over a million records in my source table and insert into my target table. This table that I'm doing the SELECT from in the Merge is in production. This table will have an application with many users hitting the table for SELECT, INSERT, UPDATE, DELETE at the same time. I will NOT be modifying the source table data with my MERGE statement, only the target table. I will have SQL Snapshot Isolation enabled, so no reason to use NOLOCK hint. Is there a way to have the query run in batches, or is having the MERGE statement scan the entire table more efficient? I have 2 other merge statements I'll be running after the inital INSERT to do INSERT, UPDATE, DELETE on target table for any changes that were done. Are there any precautions I need to take so as to not cause performance issues with the production application? I'm going to use a stored procedure because I will be running these queries on multiple tables that will be doing the same function over and over again.

*** My sample intial MERGE....

MERGE dl178 as TARGET USING dlsd178 as SOURCE ON (TARGET.docid = source.docid AND TARGET.objectid = source.objectid AND target.pagenum = source.pagenum and target.subpagenum = source.subpagenum and target.pagever = source.pagever and target.pathid = source.pathid and target.annote = source.annote)

WHEN NOT MATCHED BY TARGET THEN INSERT (docid, pagenum, subpagenum, pagever, objectid, pathid, annote, formatid, ftoffset, ftcount) VALUES (…
0

Microsoft SQL Server 2008Sponsored by Jamf Now

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.