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

I'm using sql server 2008.

I have a table with a column called Phones1 that holds phone numbers.
Some phone numbers are 7 digits and some are 10 digits.

I have a select query that gets all the columns from my table

if a phone number is 7 digits I want to format it like this:  123-4567
if a phone number is 10 digits I want to format it like this:  (204)123-4567

What's the best way to format this column like this  in my query?
0
Salesforce Made Easy to Use
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Hi Experts,

I would like to begin installation of TFS.  At this point I am only interested in the Shelving/Unshelving part of TFS.  I have seen this working in another company I was working for and would like to do the same...

Don't think it is installed...
AS one can see seems I don't have it installed.

I have SQL Server 2014 and Visual Studio 2012 currently installed.  And would require that TFS supports both when user goes through TFS.

This is not local and will be on the share...

So I would get this information from you and pass this to my share team to have it installed.  I am sure they already know how to install.  I just need from you a starting point.

What do I need to do first?

Please help and thanks...
0
I'm looking at examples and I need to insert the result of this CTE into another table.

I created a temp table to insert the results into but I don't know where the insert statement goes.

I tried it right after the CTE query , before it but it didn't work.

How can I do this? This is SQL 2016

;with cte as
(
SELECT  n.c.query('.') entirequery,
       n.c.value('(//num/node())[1]','varchar(max)') ChapterNumber,
	   isnull(n.c.value('(//heading/node())[1]','varchar(max)'),'') AS ChapterName,
       n.c.value('num[1]','varchar(max)') AS 'Subchapter',
	 	stuff (' '+n.c.query('(heading)').value('.', 'varchar(max)'),1, 1, '') subchapterTitle,  

		--section num
	
		stuff (' '+n.c.query('(section[1]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum1,  
		stuff (' '+n.c.query('(section[2]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum2,
		stuff (' '+n.c.query('(section[3]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum3,
		stuff (' '+n.c.query('(section[4]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum4,
		stuff (' '+n.c.query('(section[5]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum5,
		stuff (' '+n.c.query('(section[6]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum6,
		stuff (' '+n.c.query('(section[7]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum7,
		stuff (' '+n.c.query('(section[8]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum8,
		stuff (' '+n.c.query('(section[9]/num)').value('.', 'varchar(max)'),1,

Open in new window

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
I have a date column that is unfortunately varchar(10) and need to use it in a between statement. I've tried every conversion I could find but always get an error.
select p.person_id, p.last_name, p.first_name,hm.encounterdate,
hm.encountertype, convert(VARCHAR(10), hm.date_px, 120) as date_px, hm.hospitaladmitdate from Histories_Master_ hm
inner join person p on hm.person_id = p.person_id
and convert(VARCHAR(10), hm.date_px, 120) between '2017-06-01'
    AND '2017-06-30'
and hm.encountertype in ('hospitalization', 'emergency room')

Open in new window

1
When I execute a stored procedure
Is there any way to log or track what table triggers execute?
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
Is it possible to view an MS-SQL Server last 10 queries on a specific database or table?  and how?  (I'm using MS-SQL Server Express 2016).

I've seen this code on SO but it doesn't show every calls, and not for a long time.  If i run the same query again, some rows of the previous result doesn't show anymore:
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('MyDB')
ORDER BY deqs.last_execution_time DESC

Open in new window


Do i have to set a flag to keep a trace somewhere?

Thanks you
0
We have a BOM (Bill of Materials) table named BM_BillDetails. Within it are 2 fields named BillNo and ComponentItemCode.

We are trying to create a PHP program function to extract any top-level BOM and all of its sub-BOMs recursively and place the results into either an array or a temporary table.

In other words:

A top-level BOM contains several component items.  Each of these contain sub-BOMs of their own in addition to just plain items.  So we want to build a complete BOM array or temporary table until there are no more levels to find.

The database itself is a Microsoft SQL database running living on a Microsoft 2014 SQL Server.  We access the database for many different reasons using simply PHP.

Thank you for your help.
0
Hi,
I have insatlled my local machine 2008R2, if i have installed sql server 2014 ,it's going be a issue?
0
Windows Server 2016: All you need to know
LVL 1
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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
Hello,
I a have table with three fields

CREATE TABLE [test_DEBUG](
      [SQL] [nvarchar](2000) NULL,
      [EntryDateTime] [datetime] NULL DEFAULT (getdate()),
      [Tracking_user] [varchar](255) NOT NULL DEFAULT (suser_sname())
) ON [PRIMARY]

How can I know how many insert statements are on each day.

Thanks
0
Hi there,
  I am in process of configuring logon trigger.
My need for this trigger is to restrict few SQL logins to only login from particular IP address:

I  got the below code from google, but when I setup the trigger, first time the user can able to login but next time when we disconnect and try again then the trigger is not letting to login the sql server even though we tried from the same machine(same IP address)

CREATE TRIGGER [TR_check_ip_address]
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'Test' AND @ip_addr <> 'xxx.xx.xxx.xx'
        ROLLBACK;

END

thanks
Deepak
0
Hi Experts,

I'm looking to create a new user for particular database that will have only limited rights, for example to view only a few tables, and some of those tables only to certain columns.

Also would like to put restriction in some tables that should be able to add/edit only but not delete, and to some only to add, and other tables only read permission.

How would I go about all that?

Is there a script I can have that would allow me to list all those object and particular rights, or perhaps someone can can post some screenshots how do I accomplish that with the SSMS user interface?

Thanks in advance.
0
I have the same problem, after one year of fine work i have the error. Can help me; how i can disabled the HP program that tell is the problem
0
Hi there,
  I am trying to configure the logon trigger which should allow the connection only from specific IP addresses. Below is the code which I got by googled few blogs, when I tried with that I am unable to get the IPaddress from the Eventdata().

USE master

GO

-- Create table to hold valid IP values

CREATE TABLE ValidIPAddress (IP NVARCHAR(15)

CONSTRAINT PK_ValidAddress PRIMARY KEY)

-- Declare local machine as valid one

INSERT INTO ValidIPAddress

SELECT '<my Local Machine IP>'
-- Create Logon Trigger to stop logins from invalid IPs

CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

    FOR LOGON

AS

    BEGIN

        DECLARE @IPAddress NVARCHAR(50) ;

        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                      'NVARCHAR(50)') ;
                   
        IF NOT EXISTS ( SELECT  IP

                        FROM    master..ValidIPAddress

                        WHERE   IP = @IPAddress )

            BEGIN

            -- If login is not a valid one, then undo login process

                SELECT  @IPAddress

                ROLLBACK --Undo login process

            END

------------------

SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                      'NVARCHAR(128)') ;
                    I am getting Null Value as output for the variable @IPAddress. can somebody help me with this?
Please Note, I am …
0
Hi Experts,


Currently I have created a temp table.  But the problem is when there are large amounts of data.  After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'


Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)


This is in the select statement when inserting into the temp table:

Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
         WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
         WHEN vra.Actionable = 0 THEN 'Not Actionable'
         WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],

From TableName as cce

so this can be done more faster I would like to include:

Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table

But because this is a case statement it does not work this way.

What do I have to do in the Where statement to get this to work.

Please help and thanks
0
Hi,

I have MSSQL 2008 R2 Server that I need to reset the master key on one of the DB.  However, I cannot find the password for the current master key.  In my research to find a solution, I have found that I should be able to use;

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Not real password'

But I need to use the FORCE option.  Which I am not for sure how to use with the above query statement.

If this is correct, how do I use the FORCE option?

Thanks
0
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
SharePoint Admin?
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Hi

In what scenario should I use this command?
0
I need to change the password of a user at SQL Server level (main login of systems).

But my doubt is that other things I should check before making this change, that other factors can be affected by doing this since this user used it almost for everything within this DB.

Obvious that at the application level will make the respective changes with the new password (it is a production pass-through server, nobody has access to this), but my doubt comes from things in the database itself, for example, as the Replicas that I have to other servers, sending mails, or where else should I look where maybe that password is left and I have to update it for the new one perhaps manually.

Here are several servers, I have to check all and see which are connected to my main server, since changing the password will cause several (load processes, dblinks, etc) to fall.

What is the correct way to make this change to decrease the margin of error
0
hi,

we see application log of a SQL server show this error:


1) sqlservr (1336) An attempt to open the file "C:\Windows\system32\LogFiles\Sum\Api.log" for read only access failed with system error 5 (0x00000005): "Access is denied. ".  The open file operation will fail with error -1032 (0xfffffbf8).

and

2)
The Open Procedure for service "BITS" in DLL "C:\Windows\System32\bitsperf.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

the first one. I google it and it shows :

https://stackoverflow.com/questions/25830912/error-occurred-while-opening-logfile-c-windows-system32-logfiles-sum-api-log

https://support.microsoft.com/zh-tw/help/2811566/error-1032-messages-in-the-application-log-in-windows-server-2012?wa=wsignin1.0

but I am wondering why SQL server need to write to this folder and why we want to change read/write permission for the SQL server ! we are not the first day to run that box but now, funny error like this happened.

please share experience on this!
0
I have SQL 2008 Standard that I need to upgrade to SQL 2008 R2.
All my tests pass fine to do the upgrade.

The upgrade due date comes and thru the procedure I get this instance 'DBXBNDV1' that does not exist on the particular server that I am doing the upgrade on.  Where does this instance come from?

Infact all my SQL intances don't show any name relevant as that as an Instance name. Due to this issue, my upgrade to SQL 2008R2 standard has halted. Any advise would be greatly appreciated.
0
hi experts

i am reading about Upgrading Data Quality Services:
DQS schema must be upgraded as a separate step

what's the mean DQS schema
0
Hi Experts,

I am trying to pass a multi value parameter.


User has a downdown:
Severity dropdown
I am creating a manual and it looks like currently it is not correct.
see attachment.

I am also dealing with a drillin report which at this point is not passing correctly.  I have tried many different ways using what is mentioned in the Manual.

Ex:
On Main report I first do the Dataset like:
Main Dataset Dataset Properties
I click on fx to enter expression:
Express within the dataset
On the drillin from the main report under SEVERITY:
Click fx to go into the expression for SEVERITY
Within the expression:
Expression for the drillin from main report
On Drillin Report DataSet:
Drillin Dataset Expression
And I have followed all on the manual for the all SP's involved.

Please help and thanks.
SeverityCheckBoxParameter.docx
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.