Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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

I have a query, when I try to run it commenting a logic defined below, it runs fine.

But I need to include this logic and run the query, the query is taking hell lot of time. 2 hours for 1 lakh record.

PLEASE HELP ME CHAMPIONS.


SELECT
--CASE WHEN (select distinct  1
--from TBL c            
--where EXISTS (SELECT 1 FROM TBL1 leave            
--               WHERE convert(date,c1.DT) = leave.DT
--                 ))
--IS NOT NULL THEN 1 ELSE 0 END AS LEAVE

FROM (select *,
case when test= 'A' then DATEADD(hour,8,Date)  
     when test=  'B' then DATEADD(hour,2,Date)  
     when  test= 'C' then DATEADD(hour,-4,Date)
     else Date end as DT

 from STAGING c
 ) c1
0
[Webinar] Protection from Cyberattacks
LVL 10
[Webinar] Protection from Cyberattacks

In this session, we’ll dive into the complexities of modern cyber threats and why only multi-vector protection can keep today’s businesses secure through the various stages of a cyberattack, across multiple vectors. Thursday September 14, 2017 10:00 A.M. PDT

I am try to connect conquestdicomserver with sql Server 2008 R2 manually and also trying to editing "dicom.ini" file.. but this is not working..

Any One can help me..please.... Thanks in Advances

Here is DICOM.INI file code .

i am using window32

Sql Server Name :- GMDahri-PC
databse    Name :- conquest
login         Name :- conquest
password            :- conquest1415
sa                        :- 123




# This file contains configuration information for the DICOM server
# Do not edit unless you know what you are doing

[sscscp]
MicroPACS                = sscscp

# Network configuration: server name and TCP/IP port#
MyACRNema                = NICVDPACS
TCPPort                  = 5678

# Host(ignored), name, username and password for ODBC data source
SQLHost                  = localhost
SQLServer                = conquestpacs_s
Username                 = conquest
Password                 = conquest1415
DoubleBackSlashToDB      = 0
UseEscapeStringConstants = 0

# Configure server
ImportExportDragAndDrop  = 1
ZipTime                  = 05:
UIDPrefix                = 1.2.826.0.1.3680043.2.135.736588.40949073
EnableComputedFields     = 1

FileNameSyntax           = 4

# Configuration of compression for incoming images and archival
DroppedFileCompression   = un
IncomingCompression      = un
ArchiveCompression       = as

# For debug information
PACSName                 = NICVDPACS
OperatorConsole          = 127.0.0.1
DebugLevel…
0
I have a clr assembly installed on a SQL Server 2008 R2 instance
It was developed with .Net 2.0

It accesses a web service

Does that web service have to be a .Net 2.0 as well?
0
In my code below...
What happens if a null value is passed in?

On my "far end" api I handle the null value  (api.brkEditProspect)

    <Microsoft.SqlServer.Server.SqlFunction>
    Public Shared Function UpsertProspect(user As String, token As String, PID As String, FirstName As String, middlename As String, LastName As String,
    EmailAddress As String, Address1 As String, Address2 As String, City As String, state As String, country As String,
    zipcode As String, Occupation As String, spousefirstname As String, spouselastname As String, spouseoccupation As String, DateOfBirth As String,
    Gender As String, WorkPhone As String, HomePhone As [String], CellPhone As String, MaritalStatus As String, HouseHoldIncome As String, ReferingOwnerID As String, SalesAgent As String) As Int32
        Dim ret As Int32 = 0
        Try
            Dim api As New wsclientapi.Service
            Dim rett As String = ""
            rett = api.brkEditProspect(user, token, PID, FirstName, middlename, LastName, EmailAddress, Address1, Address2, City, state, country, zipcode, Occupation, spousefirstname, spouselastname, spouseoccupation, DateOfBirth, Gender, WorkPhone, HomePhone, CellPhone, MaritalStatus, HouseHoldIncome, ReferingOwnerID, SalesAgent)
            ret = 1
        Catch ex As Exception
            File.WriteAllText(Convert.ToString("D:\netAssemblies\errorfile.txt"), ex.Message + Environment.NewLine + Environment.NewLine + ex.StackTrace + Environment.NewLine + 

Open in new window

0
Hello,

I'm trying to convert single row records in SQL 2008 that look like this:

PrID      Type      Color       Shape      Qty      Store      OrderDt      
2299      Plate      Blue      Round      2      LA              06/02/16
3342      Vase      Clear      Long      4      NY              08/22/17

Into multiple rows records that look like this:
      
PrID      Attr              Value
2299      Type      Plate
2299      Colro      Blue
2299      Shape      Round
2299      Qty               2
2299      Store      LA
2299      OrderDt      06/02/16
3342      Type      Vase
3342      Color      Red
3342      Shape      Long      
3342      Qty               4
3342      Store      NY
3342      OrderDt      08/22/17

I don't want to use Union All because of efficiency.  

Thanks for your help!
0
How can I use a CASE statement to check if a field contains alpha characters?

Ex
CASE F1 CONTAINS [A], [B] ....
0
MY QUERY IS
SHOW DATABASES;

THEN SHOW  ERROR

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'SHOW'.  
   WHY SHOW THIS ERROR PLZ HELP ME.
0
A table was designed to have some custom fields which are defined as nvarchar(max).  The problem is that the date being entered in that field are either entered as DD/MM/YYYYY or MM/DD/YYYY.

I need to select from the table but need the date in MM/DD/YYYY format.  

I tried using CONVERT(VARCHAR, date, 101) but it is still leaving values as DD/MM/YYYY.

I also tried to convert as date first before converting to the 101 code but receive an error - The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

How do I convert the date?
0
I have a table that gets truncated daily and reloaded with fresh data. Now I need to make an update to show when the record became part of the report by adding a new field Entry_Dt.
I have my main linked server query output as #ACCT_LIST which I then join to another sql server table then it INSERTS INTO tbl_Lien_Illogical.
What I would like to do is see if the acct_id from #ACCT_LIST exists in tbl_Lien_Illogical (before it gets truncated). If acct_id exists then it will use the Entry_Dt column (from tbl_Lien_Illogical) and if doesn't exist then the Entry_Dt column will be getdate().
Then the tbl_Lien_Illogical will be truncated and new list will be inserted with the updated Entry_Dt data.
1
I have three SQL *.bak - backup files in a SQL backup folder, with the following dates, and I only want to delete one of  the oldest specific files which is the oldest.  

How can I configure the "SQL Maintenance Cleanup task : to do just this.

These are the files :

SQLsample1.bak   9/11/17
SQLsample2.bak   8/8/17
SQLsample3.bak   7/13/17 - This is the only file I want to delete because its the oldest.
0
What Is Blockchain Technology?
LVL 4
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Hi,

I wanted to understand what will be the impact of performance on server if i set Max Pool size =500 and Connection Lifetime=60 seconds in connection string specified in web.config

Is it dangerous to set connection max pool size as well as connection lifetime=60s ? I added this configuration as I was getting exception thrown for default connection max pool size i.e 200 so had to increase it to 500 though i am closing my connection properly as explained in other forums as well as Microsoft docs?

Thanks,

Krutik Sheth
0
Hi.  Do you know how I can translate the .vbs to vb.net for SSIS 2008?   There’s a lot of logic in the .vbs files so I am concerned about not translating it right.   Please provide specific examples of a .vbs and the end result SSIS .dtsx package so I can follow, not just guidelines.

I am using:
-  Visual Studio 2008
- SQL SERVER 2008R2
0
I am using MS SQL and need to update a date in one table, with a date from another table with the same ID, and the newer date. For example....   I want my query to update ID 1 in TableONE with '2017-03-07 02:45:58.050' and ID 2 in TableONE with '2017-03-14 22:20:10.823'. Could someone assist? Thank you.

TableONE                                                           TableTWO
-------------------------                               --------------------------------
ID   RefineDate                                         ID        LogDate
1      2017-02-21 08:52:52.930               1          2017-03-02 14:26:20.987
2      2017-03-03 15:12:32.927               1          2017-03-07 02:45:58.050
                                                                   1          2017-03-03 15:28:21.887
                                                                   2          2017-03-09 11:57:28.137
                                                                   2          2017-03-14 22:20:10.823
0
I wanted to ask you a question about something that might give me an idea of how to do it. I have a project in which I have to collect information about database activities, servers, sql instances, etc. store them in a DWH and then set up a control and monitoring dashboard.
I have thought to use Data Collector to collect the information that is needed, the problem is that many servers, many instances and Cluster AlwaysOn High Availability (above all) as the image.

I do not know if the Data Collector works with this type of architecture and if I can separate the information by instances, nodes, IP, NameServer and DBs. I look for information and I do not find that someone has done something similar.
Thanks in advance for your help, I will be attentive to your comments.
0
I have a MS/SQL (2008 R2) Agent Job with several steps that use a local table in the WHERE clause of a select against a linked server.  On the steps that deal with smaller results sets there are no issues but the step(s) with a large (potential) results set are timing out.  I believe that the "FROM <linkedserver>.<database>.<schema>.<table> WHERE CLAIMID IN (<select calimid from local table> where <criteria>) is returning the full results set (ie: all rows in the table) from the linked server and then applying the filter (WHERE clause).  The step is coded to "EXEC <local storedprocedure>" and the local stored procedure contains the query.  I have read where EXEC can push query execution off to the linked server using "EXEC (<userdefinedprocedure>) AT <linkedserver>" (see:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec527f63-d454-4b8a-81e9-23a2ff54c1ca/execute-at-with-dynamic-linked-server-name?forum=transactsql ).

When I execute just the SELECT portion of the INSERT INTO statement from the stored procedure using TSQL I get 54K rows returned in 22 minutes.

I don't know if - or how - to use this when one table is local and the other is on the linked server and would like the Experts suggestions.

Here's the stored procedure:

            INSERT INTO <localtable>
            SELECT WC.*
              FROM <linkedserver>.<remotedatabase>.<schema>.<remotetable> AS WC
              JOIN <localtable> AS UL
                ON UL.CLAIMID = WC.CLAIMID
             WHERE UL.SOURCE = 'D2'
0
Hi Experts,


I have a scandate Table.

On a current Query I have when a user selects a date it comes up with the Monday for that date:
Set @MONDAYDATE = DATEADD(wk, DATEDIFF(wk,0,@STARTDATE), 0)

Open in new window


But I also need when a user selects a StartDate I want the SP to return the first day of the month within the StartDate.

Example:
User selects 09/06/2017  I would like the SP to return 2017-09-01

Please help and thanks
0
How to write a simple grant permission for read, write everything in sql server 2008 to all users? I tried it but I got an error.

Grant Select on dbo.tmlemployee to all
0
I'm using sql server 2008.

on my table i have a column called TestRate which is a numberic(10,4) dataype.

So the records in the column look like this:

10.2345
14.0345
16.4405

So in my select query,  to query this column i'm doing something like this.


SELECT CONVERT(varchar,convert(numeric(10,4),MyTable1)) + ' rate per user'
FROM MyTable1


That query returns this:

10.2345 rate per user
14.0345 rate per user
16.4405 rate per user


How do i revise the query to mask the last 2 decimal digits with an X.

So i would want my result to look like this:

10.23XX rate per user
14.03XX rate per user
16.44XX rate per user
0
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
             DROP TABLE #Test

DECLARE @TextSearch NVARCHAR(1000) = 'India,,'
Select Value into #test  FROM dbo.udf_Split(@TextSearch, ',')

BEGIN
IF @TextSearch IS NOT NULL
    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C     
   
    )A
    INNER JOIN #test t on A.Name  like '%' + t.value + '%' and a.name <> ''

ELSE 

    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C 
   
    )A

END

Open in new window

0
Comprehensive Backup Solutions for Microsoft
LVL 4
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

I am looking for an automated way to do the following.


I need to create a script that could be ran on any version of sql 2008 and above that will do the following.

1)  create a mail profile.  Lets say we call the profile mycompany

2) Allow this mail profile to be used in a stored proc call that will need to execute send mail

The stored proc will live in the stored procs of the master database.
the call to the send mail of msdb database will come inside of this stored proc.


This stored proc will be called from a sql job.


I get into so many issues from one database to the next from permission ..etc.

I am looking for a solid setup of this to eliminate the many varables I run into.

I also am looking to automate this so it can be setup by other people with less experience on the issues that come up.


Thanks,
0
I have the following stored proc...I run it on any version of sql 2008 through 2016..and soon  2017.

This procedure is supposed to run the actual SQL from the Blocking SPID as well as the SPIDS blocked.  It does return the SQL of all the queries that get blocked..but doesnt show the SQL of whats blocking.  

Please let me know if you can refactor this query so the results are proper.

Thanks.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SendMailNotificationWhenBlocking]    Script Date: 8/7/2017 1:14:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET nocount ON; 
GO
SET concat_null_yields_null OFF 
GO


Create procedure [dbo].[SendMailNotificationWhenBlocking]
as
begin



WITH blockers (spid, blocked, level, batch, lastwaittype,hostname,cmd,dbid,loginname,open_tran,login_time,last_batch) 
     AS (SELECT spid, 
                blocked, 
                Cast (Replicate ('0', 4-Len (Cast (spid AS VARCHAR))) 
                      + Cast (spid AS VARCHAR) AS VARCHAR (1000))         AS 
                LEVEL, 
                Replace (Replace (T.text, Char(10), ' '), Char (13), ' ') AS 
                BATCH, 
                R.lastwaittype,
                R.hostname,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time,r.last_batch
         FROM   sys.sysprocesses R WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text(R.sql_handle) T 
         WHERE  ( blocked = 0 
                   OR blocked = spid ) 
                AND EXISTS

Open in new window

0
I ran an execution path.  Per the plan it states that the below statement cost 95%.  Anyway to improve on it?

SELECT * 
	INTO #ItemsResult1
	FROM #Items IR 
	WHERE IR.id_shipment IN (
		SELECT id_shipment FROM Shipment SI 
		WHERE SI.id_shipment = IR.id_shipment
	)
	OR IR.id_shipment = 0

Open in new window

Item Execution Result
0
Having an issue in SSRS 2008 R2 using visual studio 2017.

I have created a report that with grouping that works well, but i wanted to add gauges to it to represent the SUM value of the groups.

Each gauge will show a different group. When i'm configuring the groups it will always show the total for all the groups not just the individual groups i'm trying to tag.

Please see screenshot of what i want it to show,

Many thanks in advance for your help and advice.

James.

SSRS Screenshot
0
Hi
I created an instance in sqlserver 2008 with x name.
I want to stop this service on specific date....
or take one of the databases under this instance off
is that possible?
0
I have mssql query (MS SQL 2012), I can export the result into Excel, csv...etc. But I don't find a way to export it into HTML
Is there a way to do that by DTS or something else?

Any advice?
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.