[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables
CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterSched bit
)

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window


2. Populate tables

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 88, 'Edward', 1 -- has filter and will get data with the INNER JOIN I have because #FilterScheds table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 14307, 'Camillia', 0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #FilterScheds doesn't get populated)

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window


4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window


My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end 

Open in new window

0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

What is the difference between press and hold shift key and click on SSMS executable and directly clicking on SSMS executable and enter the same credential?
0
Thank you in advance,
I'd like to know if it is possible to upgrade the MS SQL server independent of a Windows Server version.
I have a legacy client with Windows Server 2008 R2. They of course have their SQL embedded as a 2008 R2 version of SQL as well.
This is a difficult client, and just for grins I'd like to know if SQL can be upgraded separately from the OS.
Thank you for your help regarding this.
Sam
0
Dear EE,

I have one App and one DB server and need to disable TLS 1.0 and enable TLS 1.1 and TLS 1.2 on them.

App = windows 2008 r2
Db = windows 2008 r2 --- SQL 2008 R2

Please suggest,

Thanks
0
Hi There,
We use Macola 7.8.200 progression WorkFlow and Sql 2008 server (on Win 2012 r2)

We have auditors, and they want me to get a report of all the users who have access to macola and the modules they have access to and the access level( maintain, view etc).
I'm not sure if there is a table or from macola we can do a report to get this.
I checked the Screens database and looked through the table.

I see Syscomp_Sql table has the user and menu name, and menu level but not sure what the menu level indicates.I see 0 through 3 there.
I'm not sure which will be a good table or if we can run something from macola to get list of all users and their access level.
Otherwise we may have to go to Visual Menu builder and look at each user and do screen shot of each users access. That's a lot of work we have lot of users.
I will appreciate if anybody have a easier method
0
What do i need to do to download a free version of SSIS?
0
Why are my SQL Backups failing in my maintenance plan? SQL 2008 - The Job was invoked by Schedule 13

I received the following email alert about my Maintenance Plan backups:

JOB RUN:            '4DCVR_BackupPlan.4DCVR_Subplan_Tran' was run on 27/11/2018 at 14:00:00

DURATION:        0 hours, 1 minutes, 46 seconds

STATUS:               Failed

MESSAGES:        The job failed.  The Job was invoked by Schedule 13 (4DCVR_BackupPlan.Subplan_2).  The last step to run was step 1 (4DCVR_Subplan_Tran).



I attempt to manually run a Full Backup on the database which is set to Full Recovery mode, which appears to be backing up as the file appears in the backup folder, but still the  backup fails. The transaction logs are apparently failing according to the email, but they are still visible in the backup folder (although may not be complete).

I have maintnenance plan backups setup exactly the same way for other databases on different instances that are successfully working,

Can anyone tell me what may be going on and how to rectify it?

The backups from the maintenance plan have stopped working for way over a couple of weeks.
0
I'm having an issue with a DAX formula in SSAS/Tabular.

I have a calculated measure (rtn_qty_adj) which provides the values as expected, however when I reference that formula in another calculated measure (rtn_qty_avg_adj), it gives incorrect values.
It appears that "rtn_qty_avg_adj" is ignoring or filtering-out the logic of the calculated measure "rtn_qty_adj".

Basically the dataset consists of returns quantities over a (monthly) timeframe.
Where there are big peaks/excursions in the data (ie; return qty. in a month is greater than uper control limit) , then in the measure  "rtn_qty_adj" I reset these peaks to the Average return qty.
So then I want to perform another averaging on the adjusted returns qtys. where the peaks are "smoothed-out" (ie; equated to the average)
Most of the calculations work just fine, except this one which is causing me a headache.

Appreciate all input/help!!

Formulae
Here's the formulae for the adjusted return qty and adjusted return qty. average
Rtn_qty_adj:=
VAR rtn_avg =
CALCULATE(
		(AVERAGEX(
			SUMMARIZE( drct_excg, dates[incr_base_date], "rtn_avg",  [Rtn_qty] )
		,[rtn_avg] ) )
, ALL(dates))
RETURN
IF( [Rtn_qty_ucl_1sd_otlr] = 1, 
	IF(COUNTROWS(VALUES(dates[incr_base_date])) >1,
		SUMX(VALUES(dates[incr_base_date]),
			IF( [Rtn_qty_ucl_1sd_otlr] = 1, rtn_avg , [Rtn_qty] )  )
	,
	rtn_avg
),
[Rtn_qty]
)

Rtn_qty_avg_adj:=
CALCULATE(
	AVERAGEX(
		SUMMARIZE( drct_excg, dates[incr_base_date] ,"rtn_avg", 

Open in new window

0
when we use exists in a query

what is meaning of select x means

select count(order_nr) from xyz where ord_cd="10"
exists(

select 'x' from ABC a where ef=ge
and
a.some_cd='zz'
and
((datediff(...)or (datediff.....))


i checked below link
https://www.geeksforgeeks.org/sql-exists/

how to validate my results are correct or not

how to break above query to see if difference of time is actually correct?
please advise
0
Msg 1013, Level 16, State 1, Line 1
The objects "XYZ" and "ABC" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

wondering what it means by above error

please advise
0
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

we have hundreds of flows like registration, sales, cancellation from front end java applicatio to SQL database.

Microsoft SQL database has hundreds of stored procedures, tables, columns, joins, primary keys, foreign keys etc.

it is getting hard to check database stored procedures, tables if issue come on say sales flow to check quickly as no proper documention on database side.

what kind of documentation is good to have on database side.

i think like one excel spreadsheet  with columns like stored procedures, then next column relevant tables then next column table 1 columns, table 2 columns and then highight primary key as yellow color and then next column joins and other related tables

please advise any other better ways and best practices and links resources on it
0
My SQL server is 2016 SQL. The SQL server has replication service running. I installed below two KBs
SQLServer2016-KB4458871-x64
SQLServer2016-KB4464106-x64

Both KBs seem not installed successful because it doesn't show "successful" But, instead showing the image attached. But, after applying the KBs, the SQL service is not auto started. Even I manually started it, it failed with the image attached.

So, I have to use the following command to start it.

NET START MSSQL$INSTANCENAME /T902

I don't want to start SQL service every time I have to run this command, but instead using SQL service

Do you know why the SQL service is not started auto. How to fix this issue? I know uninstalled is very complicated even not possible because they have installed into the registry
CaptureSQLPatchingProvisioningdbissu.PNG
CaptureSQLPatchingProvisioningdbissu.PNG
0
I'm looking for reasons why a SELECT query does not return all rows that satisfy the selection criteria.

 I have created such a query and incorporated it in a stored procedure that runs periodically at 15 minute intervals.  Most of the time it returns all of the expected rows.  At other times a row is missing even though the data in the tables being queried remains unchanged as does the selection criteria for three successive executions of the query.  

The routine processes 20 to 30 transactions a day correctly, but every couple of days it misses a transaction.

I suspect this might be due to locking, but it seems improvable that records could remain locked for a 30 minute period.  

Am I overlooking any other possible reason or reasons for this behavior.  

Thanks in advance for your time and consideration.
0
in excel how to represent below date and time stamp as it is like in database cell

2018-11-13 06:13:15.891


when i copy past it shows as 13 or something

how to import sql server results value including heading to excel without loosing date formatting etc
any tips or tutorial videoes around this?

Please advise
0
I have a view that calls a function which takes a long time to complete.
Can anyone suggest how can I convert this cursor to a set based approach or CTE for better performance?

the view without the call to the function runs in seconds,  adding the call to the function kills the performance.

ALTER FUNCTION [dbo].[CT_OpsBetween] (@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3), @SPLIT nvarchar(3), @SUB nvarchar(3), @eSeq smallint)
RETURNS nvarchar(255) 
AS
BEGIN

DECLARE @OpsBetween nvarchar(255);

DECLARE @MyCursor CURSOR;
DECLARE @SEQUENCE_NO smallint;
DECLARE @RESOURCE_ID nvarchar(15);
DECLARE @OPERATION_TYPE nvarchar(15);
DECLARE @SERVICE_ID nvarchar(15);

SET @OpsBetween = ''
SET @MyCursor = CURSOR FOR
     SELECT SEQUENCE_NO, RESOURCE_ID, OPERATION_TYPE, SERVICE_ID
     from dbo.OPERATION
     where TYPE = @TYPE AND BASE_ID = @BASE AND LOT_ID = @LOT and SPLIT_ID = @SPLIT AND SUB_ID = @SUB AND SEQUENCE_NO  < @eSeq
     ORDER BY SEQUENCE_NO
 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
 
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @OpsBetween <> ''
         BEGIN
             set @OpsBetween = @OpsBetween + ', '
         END
      SET @OpsBetween = @OpsBetween + Cast(@SEQUENCE_NO as varchar(10)) + '-' + CASE WHEN @OPERATION_TYPE IS NOT NULL THEN @OPERATION_TYPE ELSE CASE WHEN @SERVICE_ID IS NOT NULL THEN @SERVICE_ID ELSE @RESOURCE_ID END END;
      
      FETCH NEXT FROM @MyCursor INTO 

Open in new window

0
sql server 2012 when i login as below it shows some values which are unclear to me under server name

how to make sure to put server names some meaningful names to understand

instead of say
CXXXXX1234.xy.LMP.com,99999
i like to give alias name like
Production Database
or
System Test
etc

so that i do not need to refer every time when i login to check documentation to see where i am making changes etc

also how to connect multiple environments same time paralleled like production and System Test, integration etc
do i need to open microsoft sql mgmt tool multiple times or can i open separate tabs in one instance one underneath other?
Where can i refer for good usage tips and learning? any good vidoes or resources around this

what is difference between
windows authentication
and
sql server authentication
when to use which one

please advise
SQLServer2012_NAmeEnv.png
0
Has anybody used Azure Site Recovery to protect a SQL Server 2008 standard edition cluster?

I am just trying to wrap my head around how the data gets sync'ed. Typically a hot data synchronization is an enterprise feature however this article states that ASR can be used to protect a SQL Server 2008 SE fail over cluster.

I don't see any details on how the data sync works though.
- https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sql

Can anybody share their experience with Azure Site Recovery specific to SQL Server 2008 standard edition.

Thanks experts!
0
You receive an error message when you try to create a differential database backup in SQL Server 2008

Cannot perform a differential backup for database namedb, because a
current database backup does not exist. Perform a full database backup by
reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
BACKUP DATABASE is terminating abnormally.

We use TSM,  TDP for SQL to do the backups.
0
Guys,
I have a situation where  I need to migrate / copy existing  logins from windows authentication to - sql authentication. Every windows  Logins  has specific server role, database role and attach to  schema. So  in order to copy this login and again create them as sql logins, do you guys able to recommend a method of doing this ? currently this sql server has around 2000 actives windows login that I must convert them to sql authentication.
0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SQL Server 2008 R2 Developer Edition

Hello Sir,
My DBCC OPENTRAN Displays

Oldest active transaction:
    SPID (server process ID): 67s
    UID (user ID) : -1
    Name          : DELETE
    LSN           : (286474:1220:6)
    Start time    : Oct  2 2018  1:10:17:007AM
    SID           : 0x0105000000000005150000005080301bd0cce941ef8d2526df040000

How can i kill it
0
Hi,

I have a DTS job that uploads some CSV data into a SQL table.

I wanted to schedual the DTS using an SQL job.

I have setup the job in SQL but when it is executed through the SQL job it fails (see error message below).

If I run the DTS directly it works ok.

I think it is something to do with the user permissions but I don't know why it fails as SQL user I have setup (web_admin) has SA rights.

Any ideas?

DTS fail
0
Hello,

I am getting error;
Date cannot be converted to string

Please find the query below:
INSERT INTO TABLE1

SELECT IET.[In1]
   
         , ''
         , getdate()
         , ''
         , IET.[Name of Trt:]
       ,NULLIF(RTRIM(IET.[FROM]), '')      
        ,NULLIF(RTRIM(IET.[TO]), '') 
         , IET.[Trion] 
 FROM TABLE2 IET


WHERE [InvemGUID] NOT IN 
    (SELECT [InvemGUID] FROM TABLE1 )
 

Open in new window


The error is caused by
  ,NULLIF(RTRIM(IET.[FROM]), '')      
        ,NULLIF(RTRIM(IET.[TO]), '') 

Open in new window


Any suggestions?
Thanks
0
Hi,
 Please find attached Image.
My query
I have Created Data warehouse. For example
Dimension Table : DimSite, DimCustomer,DimSalesperson
FactTable : FactinvoiceDetails

DimSite is Include

SiteID    Sitename

1            United Kigdom
2           USA
3           France

DimCustomer
SiteID       CustomerID CustomerName
1                R001             Ryan
2                R001             Bryan
3                R001            Stephen

DimSalesperson
SiteID       SalespersonID      SalespersonName
1                S001                       Mark
2                S001                       Stacy
3                S001                       Franck


My question is

I have to connect DimCustomer and DimSalesPerson to DimSite Table also
FactTables.png
0
I have a winforms ERP software built using C# dotnet. Database is sql server. My issue is with installation at client side. It is taking huge time to install. Major time consuming is the installation of MSSQL server and its Management Studio. Is it necessary that I should install sql server at client side? Is there any option where I can copy the mdf and ldf files of the sql database to the system and connect with my application (like we do with access database)
1
Environment: Windows 2012 R2 Standard on VMWare VSphere
SQL Server 2016 Standard

We are getting this error
Msg 0, Level 11, State 0, Line 1
A severe error occurred on the current command.  The results, if any, should be discarded.

Open in new window

Everything works fine in SQL Server 2008 but when we moved it to SQL Server 2016 the complex queries are failing. Sample below is the query that works on 2008 but is failing in 2016.
SELECT        dbo.tblRevenueSubAccounts.Year, dbo.tblRevenueSubAccounts.ServiceTerritory, '$' + CONVERT(varchar, CAST(dbo.tblRevenueSubAccounts.Forecast AS money), 1) AS Forecast, '$' + CONVERT(varchar, 
                         CAST(dbo.tblRevenueSubAccounts.CurrentPlan AS money), 1) AS CurrentPlan, '$' + CONVERT(varchar, CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) 
                         + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0), 1) AS [Shipped+Backlog], CONVERT(varchar(50), (CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) 
                         + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0)) / CAST(dbo.tblRevenueSubAccounts.Forecast AS money) * 100) + '%' AS [%Forecast], CONVERT(varchar(50), 
                         (CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0)) / CAST(dbo.tblRevenueSubAccounts.CurrentPlan AS money) * 100) 
                         + '%' AS [%CurrentPlan]
FROM            dbo.tblRevenueSubAccounts LEFT OUTER 

Open in new window

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.