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

When I execute a stored procedure
Is there any way to log or track what table triggers execute?
0
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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
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
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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
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
Optimize your web performance
LVL 1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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
Hello,
Need to write a query on table to mask values return.


Table1

Name    EndDate
A             23-11-90
B             31-12-9999
C             12-10-2013


Diplay results:
Name    EndDate
A             23-11-90
B            
C             12-10-2013


Mask the max date

Cheers
0
I need to formulate update statements for each record in the attached spreadsheet. The SQL statement I need for each field and value is attached. Is it possible to do this?
warehouse-level---lines-to-modify.xlsx
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
Hello guys,

Please, take a look at this query:

select A.QtdPRO, B.QTDEMBALAGEM,
       convert(float, CEILING( ( A.QtdPRO / convert(float, (A.QtdPRO / B.QTDEMBALAGEM))))) as qtde1,
       convert(float, CEILING( ( 88 / convert(float, (88 / 60))))) as qtde2,
	   CEILING( ( 88 / convert(float, (88 / 60)))) as qtde3,
	   ( 88 / convert(float, (88 / 60))) as qtde4,
       ( 88 / (88 / 60)) as qtde5,
	   ( 88 / 60) as qtde6
  from arifat a 
inner join arest b on (a.codpro=b.codpro)
where a.numped=6758

Open in new window


This is the result:

QtdPRO      QTDEMBALAGEM      qtde1      qtde2      qtde3      qtde4      qtde5        qtde6
  88                         60                        61                 88             88                 88               88                  1



I use a store proc where I need to make a calculation for labels:

QtdEmbalagem = Total of product per box
QtdPro = Quantity of product sold

First weird qtde1 <> qtde2,  Why?

The result of Qtde1 should be 60 and not 61

When I have other values, I got the correct result, for example:

If I have qtdPro = 80 I will get the Qtde1 = 60
If I have qtdPro = 90 I will get the Qtde1 = 60 and so on

Why?
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

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.