Still celebrating 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 am looking for idea. I need to grab locations , some of the lcoations have the same name but different locationID.

I need to create a location drop down with a unique locations. And then do the search by these locations. The question  is if I do make a drop down of unique locations thne in my search I will be searching by name instead of locationID
0
Back Up Your Microsoft Windows Server®
LVL 4
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

I have a question related to OLE Automation (reference: Why not enable 'OLE Automation Procedures' )

I need to use OLE Automation to interact with the Windows File System.  I do not want to leave OLE Automation "turned on" because of Security Risks so......

Is there a way to turn OLE Automation On at the beginning of a Stored Procedure and then turn it back off at the end of the procedure?   The Procedure will run on a timed cycle from a SQL Agent job.

Thanks in advance!
0
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
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
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
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
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
Prepare for your VMware VCP6-DCV exam.
LVL 1
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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
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
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
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
What statement would you use to remove all rows from a table
0
I was trying to install MS SQl server 2008R2 on windows Server 2012R2.
But the installation was successfull with following error message.

What problem could i face in future ?

Error Message-----------------------
Note : PkgMgr.exe has been deprecated.Please update your scripts to use DISM.exe to install,COnfigure, and update features and packages
and packages for windows.

See http://go.microsoft.com/fwlink/?LinkID=195329 for more information.

Operation failed with 0x8007000B
---------------------------
0
Hi,
I have created user and given permssion to  db_owner for Test database.
But user complaining user can't create stored procedure or View.
I have given grant using following command but still user can't create store procedure.
What iam missing

use [App_Test]
GRANT EXECUTE ON SCHEMA::[dbo] TO [user1]
GRANT EXECUTE ON DATABASE:: App_Test  TO [user1]

USE  [App_Test]
GRANT CREATE PROCEDURE TO [user1]    
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [user1]
0
Hi there,
   We have log shipping for 4 databases which are going well but however only Restore Jobs for two databases gets failed in DR environment occasionally. The error message says like this
Executed as user: xxxxxxxxx. The process could not be created for step 1 of job 0x867837754D077D4A9BFF608D000072BD (reason: 5).  The step failed.

I have configured the Job->Step->Advanced->output file to capture the error message but this error was not written in the output file as well. In fact this error doesn't seem to be happened at all as I can see the output file has only log which is written before the last run which was failed.

thanks
Deepak
0
Hi,
I have create user "qry" and create table Sales.
I need to give access this user only sales year 2017, i don't want to show 2016<= sales.
How to do that?
Best regards
0
Ransomware-A Revenue Bonanza for Service Providers
LVL 4
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

I have a query that pulls records from the previous day. I would like the where clause to use the previous day's information. So in the example below, if I ran the query on 5/1/2017, I would want it to use " April of 2017" for the "MONTH" and if it was January 1st, 2018, I would want it to use the previous year for the GETDATE(). I only need this to happen on the first of the month since every other day it pulls the current month's information. Can someone assist?

SELECT blah FROM myTable
WHERE  YEAR(myDate) = YEAR(GETDATE()) 
	    AND MONTH(myDate) = MONTH(GETDATE()) 

Open in new window

0
I've inherited a linked server which drives our website. I recently discovered that not all of our replications to that linked server are complete. I'm missing sometimes thousands of records in some tables. I think what is happening is that some of the local publications never had subscriber jobs created so the snapshots are being created, but not moved to the linked server. When I try to create a new subscription job I get an error  that the "subscription to publication <xxxx> has expired or does not exist."

How do I even begin to resolve this? Google searches are only telling me how to create the subscription but doesn't really help resolve this specific issue.

I'll provide whatever additional information you need. The full error is here:
Date            6/28/2017 9:26:59 AM
Log            Job History (SQL12-ItemMaster-repl1_Cat_to_Opt-WIN-3EB9B225E5P-1069)

Step ID            2
Server            SQL12
Job Name            SQL12-ItemMaster-repl1_Cat_to_Opt-WIN-3EB9B225E5P-1069
Step Name            Run agent.
Duration            00:00:01
Sql Severity      0
Sql Message ID      0
Operator Emailed      
Operator Net sent      
Operator Paged      
Retries Attempted      0

Message
2017-06-28 14:26:59.919 Copyright (c) 2008 Microsoft Corporation
2017-06-28 14:26:59.919 Microsoft SQL Server Replication Agent: replmerg
2017-06-28 14:26:59.919
2017-06-28 14:26:59.919 The timestamps prepended to the output lines are expressed in terms of UTC time.
2017-06-28 14:26:59.919 User-specified agent parameter values:
                  -Publisher SQL12
                  -PublisherDB …
0
The Server (win 2008) name for one of our test Server was changed , however, the server hosted an SQL Database. with the change, it has resulted in us unable to connect to the SQL Database (sql 2008). the previous username does not work anymore and i even tried connecting with windows authentication and still nothing. is there anyway to resolve this?

Regards,
Grand
0
Hello Experts,
I am trying to debug a Stored Procedure and I get the following error as soon as I Start Debugging.  Any idea what is going on?

Error Message:  Unable to start T-SQL debugging. Could not attach to sql server process on XXX.  The object exporter specified was not found.

Thank you in advance!
0
I have many columns in my report and I want to dynamically sort the columns which the user needed to sort in ascending order. How could this be done?
0
I am using MS SQL Server 2008 as my Database and I created a Stored Procedure that looks like this.

    USE [DB_Question]
    GO
   
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER Procedure [dbo].[Check_ExamID]
    @ExamID NVARCHAR(MAX)
    as
    SELECT * FROM TBL_ExamTimer WHERE ExamID = @ExamID

and here is my PHP code that suppost to be the target is to display data in my PHP site by using some criteria also.

    <html>
    <head>
   
    </head>
    <body>
    <?php
   
          include('config.php');
          include('adodb/adodb.inc.php');
          $db = ADONewConnection($dbdriver);
          $db->Connect($dsn, $username, $password);
          
          $procedure = $db->prepareSp('Check_ExamID');
          if (!$procedure)
        die ('Invalid or inaccessible stored procedure name');
          
          $parameter1Name = '@ExamID';
          $ok = $db->inParameter($procedure,$parameter1Name,'1234');
          $result = $db->execute($procedure);
     
   
    ?>
    </body>
    </html>

I am trying to display data where `ExamID =@ExamID` or `1234` for param of `@ExamID`

My Target here is to use my stored procedure to select data in my table and return it back in my php file but nothings happening. TYSM
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.