We help IT Professionals succeed at work.

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.

Can Entity Framework be used along with the SQL Server CLR?

I will use the CLR to have a Database triggered call to my C#.NET source code. But, I lately only use Entity Framework in C# to query and update the SQL DB.

Does this work? It's seems like it could be very problematic. But, on the other hand, if my C# code is sure to close all DB connection before returning a value, I SUPPOSE this could be a logical design paradigm.

Please feel free to comment.

Thanks
0
Hi,

I wanted to fudge or mask user data in Mssql

My table name is appusers

field names to fudge the data:

EmailAddress
Mobile
Phone
FirstName
LastName

Please advise the best query.
0
We have around 6 billion rows in a single table
We are running some scripts to clear out old data
However the size of the database does not seem to be reducing by much (i know the MDF file will stay same size and i've looked at space available inside this file)
Do we have to somehow defrag the table?
I've looked into index rebuilds are they are highly fragmented, and wondering if this is what we have to do to release the space

Any feedback appreciated
(Sql server 2017 enterprise)
0
Hi Experts,

We have a report based on Nurses table.

Then we have a table named Skilled_nursing_Visit_Notes (Nurse_Table.Nurse_UserName = Skilled_Nursing_Visit_note.Nurse_User_ID_num_SNV) where nurses enter visits and notes for patients.

Under each visit note there is a column named Browser which auto saves the browser version used for that entry.
We also have a calculated field (formula) in the Skilled_nursing_Visit_Notes  table named OutdatedBrowser set as follows.
case when ([@field:Browser] like 'Chrome%' and [@field:Browser]>='Chrome 78.0') or ([@field:Browser] like 'Safari%' and CAST((Right([@field:Browser],(Len([@field:Browser])-Charindex(' ', [@field:Browser], 0)))) as FLOAT) >= 13) or ([@field:Browser] like 'Firefox%' and [@field:Browser]>='Firefox 71.0') or ([@field:Browser] like 'InternetExplorer%' and [@field:Browser]>='InternetExplorer 11.0') then 'No' Else 'Yes' end

Open in new window


Now we would like to add to the nurses report the following.

1- Ability to filter for nurses with Outdated browser (According to latest entry in Skilled_Nursing_Visit_Notes table determined by Date_Signed field which is a timestamp field).
2- View on the report the browser field.

Thanks
0
Hi Experts,

this Q is related the following.

I'm trying to execute the suggested script (using SQL 2008) and getting the error below
Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'IF'.

Thanks
0
I have a run longing script (30 days) and I want to see the progress/size of one of the temp tables

however this doesn't work
select count(*) from [tempdb].[dbo].[#DataToDeleteTable]

Open in new window


its says
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 8
Invalid object name '#DataToDeleteTable'.

Open in new window


I've also tried
SELECT COUNT(*) 
FROM #DataToDeleteTable

Open in new window


but get
Msg 208, Level 16, State 0, Line 5
Invalid object name '#DataToDeleteTable'.

Open in new window


if i run this
select * from tempdb.sys.objects

Open in new window


I see the name
#DataToDeleteTable__________________________________________________________________________________________________000000005144

Open in new window


How might I get the count of this temp table?
0
Hi Experts,

I'm trying to restore a database from a backup of MDF file, but not looking to replace the existing database but instead I want this as copy of the DB, as the purpose is to lookup something back dated.

Tried simple restore method and got the attached error after selecting that file.

How can that be done?

Thanks
Untitled.png
0
I am new to AWS RDS. We have SQL server in Azure, not SQL Azure. I think AWS RDS, like SQL Azure is Platform as a Service (PaaS), not  infrastructure as a service;. I want to know if this is correct statement.

I have MS 2016  Always-on cluster. What is the benefits if we want to do AWS RDS.

What is pros and cons for AWS RDS vs not just AWS?
0
Hi Experts,

I'm looking for a simple trigger (in T-SQL) that will check if a particular field was changed, should insert a row into log table.

Table name = Skilled_Nursing_Visit_Notes
FieldName = Visit_Date
LogTable = SNVN_Log
Primary Key = SNV_ID

Thanks
0
Been Googling for this and haven't found a solution (I'll research more and post back if I find my answer)

1. If I have a column like this data:

STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:4;GMT: 55;HH:22;MM:5566

2. I want to update this column but only replace LOC:4; with a new value. I want to replace the value after LOC: up to ; with a new value. Leave the rest as is.

3. For example, the new value is 4,10004,10005 I want the new string be:

STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:4,10004,10005GMT: 55;HH:22;MM:5566
0
I've tried the 3 statements below but still can't get it working. . This is what I want

Original string: STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:23,19,25,10003,20,21,22,10000,10002,10005,10004,10001,24,4;GMT:444

Anything after LOC: and before ;

We will always have LOC: and the semicolon.

This is what I have. Close but still not getting what I need:

DECLARE @string VARCHAR(MAX) ='STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:23,19,25,10003,20,21,22,10000,10002,10005,10004,10001,24,4;GMT:444'

Select Substring(@String,0,CharIndex('LOC',@String))

SELECT RIGHT(@string,LEN(@string)-CHARINDEX('LOC:',@string))

SELECT
    SUBSTRING(@string,CHARINDEX('LOC:',@string)+1,LEN(@string))

Open in new window

0
Hi Experts,

I want to create a calculated field (or formula) that will show which of the field/s on the table is null for that record.

So for example if FieldA, FieldC and FieldE are null, the value of that calculated field should display "FieldA, FieldC, FieldE".

Need this in SQL 2008 compatible syntax.

Thanks
0
Hi Experts,

Getting attached error when trying to perform an update query.
The underlying table is updatable in Access.
Table is SQL based.
Untitled.png
0
hello expert
I have a person table.
I don't want duplicate inserts,
ı want to use SQL Server Trigger to prevent duplicate inserts.
for example
CREATE TABLE person(
    tc_umarasi varchar(11),
     adi VARCHAR (50) ,
    soyadi VARCHAR (50) 
);

Open in new window

I tried to write a trigger
trigger is as follows
create TRIGGER [dbo].[dene_ali]
   ON [dbo].[person] FOR INSERT
AS
IF EXISTS(SELECT I.adi
		From Inserted I, person F
		Where  I.tc_umarasi=F.tc_umarasi AND I.adi = F.adi  
		Group By I.tc_umarasi,I.adi
		Having COUNT(*) >1 )
BEGIN
   
  RAISERROR('>>>DUPLICATES PREVENTED<<< ',10,1)
    ROLLBACK TRAN
	
END 

Open in new window

trying to add a few data
collectively when I insert
insert into person(tc_umarasi,adi,soyadi) values('11','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('88','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');

Open in new window

then I run the following query
SELECT * FROM person

Open in new window


tc_umarasi          adi                soyadi        id
11                         ali sahin      bayoglu      NULL
125                         ali sahin      bayoglu      NULL
88                         ali sahin      bayoglu      NULL

insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');-want this value to be printed in the database.
I want this value to be printed in the database.
but the database stoped the insert operation before it reaches this value.
When trigger encounters duplicate, I don't want to write value to the table, but I want it to continue with other insert operations.
how can I resolve this?
thanks a lot
1
Greetings,

We have a software called IQware that is installed in classroom of our school. It uses a local database configured in Sql management studio 2008.

All the machines of that classroom uses an image created by Acronis and machines are also by default in frozen state using deepfreeze.

That being said, the issue we have is in one of the computer, IQware was not launching at all so I restarted the machine hoping deepfreeze would fix the problem but no luck.

The support team of IQware couldn't help because they don't support SQL so I decided to reimage the machine and once done IQware worked.

After updating the machine and a couple of reboot, IQware was still working. So I renamed the machine and restarted again to apply the change.

After the reboot, I get a message Your database version number UKnown.UKnown.UKnown.UKnown is older than you application version number appears and IQware doesn't work anymore.

So I renamed the machine with the name it had after reimaging it and IQware works!!

I am not an SQL expert so I am wondering why changing a name of a machine can give this issue?

I am attaching 3 files:

2 showing that the database is accessible and one with the error message.

Cheers,

Richard
0
I wonder if this is doable...I tried a pivot example and couldn't get it working. If not doable in SQL, I'll look into doing it in C#.

1. This is sample data. I have "12345" in the usage column as example.

CREATE TABLE #temptable ( [activationdate] nvarchar(20), [reportdate] nvarchar(20), [usage] money )
INSERT INTO #temptable
VALUES
( N'2018.1', N'2019.5', 12345.0000 ), 
( N'2018.2', N'2019.5', 12345.0000 ), 
( N'2018.3', N'2019.5', 12345.0000 ), 
( N'2018.4', N'2019.5', 12345.0000 ), 
( N'2018.5', N'2019.5', 12345.0000 ), 
( N'2018.6', N'2019.5', 12345.0000 ), 
( N'2018.7', N'2019.5', 12345.0000 ), 
( N'2018.8', N'2019.5', 12345.0000 ), 
( N'2018.9', N'2019.5', 12345.0000 ), 
( N'2018.10', N'2019.5', 12345.0000 ), 
( N'2018.11', N'2019.5', 12345.0000 ), 
( N'2018.12', N'2019.5', 12345.0000 ), 
( N'2019.1', N'2019.5', 12345.0000 ), 
( N'2019.2', N'2019.5', 12345.0000 ), 
( N'2019.3', N'2019.5', 12345.0000 ), 
( N'2019.4', N'2019.5', 12345.0000 ), 
( N'2019.5', N'2019.5', 12345.0000 ), 
( N'2019.6', N'2019.5', 12345.0000 ), 
( N'2019.7', N'2019.5', 12345.0000 ), 
( N'2019.8', N'2019.5', 12345.0000 ), 
( N'2019.9', N'2019.5', 12345.0000 ), 
( N'2019.10', N'2019.5', 12345.0000 ), 
( N'2018.1', N'2019.6', 12345.0000 ), 
( N'2018.2', N'2019.6', 12345.0000 ), 
( N'2018.3', N'2019.6', 12345.0000 ), 
( N'2018.4', N'2019.6', 12345.0000 ), 
( N'2018.5', N'2019.6', 12345.0000 ), 
( N'2018.6', N'2019.6', 12345.0000 ), 
( N'2018.7', N'2019.6', 12345.0000 ), 
( N'2018.8', 

Open in new window

0
I want to sum the 2 select statements in the inner cursor loop.   I get this error: Invalid column name 'TotalDebits'.

I looked at example online and this looks correct. I also tried using the + operator. I removed the second "select" as a test and ran it but still the same error.

I've marked it where I get the error:

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
              -- INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits)  ---- ******* I get the error here **************
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							......
							 
                            
						UNION ***** I tried the + operator here 
						SELECT SUM([CashBalance]) TotalDebits
						...
                       )   -- union statement
							
					Fetch Next from 

Open in new window

0
We need to get a script done for our client (their developers, DBAs in the dept quit within a few weeks and no one bothered to ask them to turn over their code and scripts!)

My manager came up with this cursor loop but I get an error (I know cursors are not recommended but we have to get this done for now). Not sure where he got the script but I think the placement of closing the cursors is off somewhere..

error: A cursor with the name 'activationCursor' already exists.

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
                                      INSERT INTO #usage (reportdate,activationdate,usage)
				         SELECT
				                .....
					Fetch Next from activationCursor into @aYear, @aMonth
				End
			Close activationCursor
		 --Insert into Usage table
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor

Open in new window

0
Hi,
I have built a dtsx package using VS 2008 on Windows Server 2008 R2 Standard SP1 and it works fine when I use Preview in one of the OLE DB Source components (see below)
okHowever, I have installed the package in a different environment, VS Pro 2017 on Windows 10 Desktop and I am getting an error when I click Preview, see below.
I have checked the Connection Manager  and it Tests fine, also the same Connection Manager is used in multiple components in the package and it works fine.
Any ideas? If you need any more info please  ask.
Thanks.
error
0
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I have this error when building a CTE that has aggregate function and GROUP BY STATMENT

      
            WITH ProductSales      (      PoductID                  
                                                ,ProductCount
                                                ,TotalSales
                                          )
            AS
      --We Define the CTE query
      (
            SELECT                  ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
            
      )
      ,CTE_ProductSales
            AS
      --Define the Outer query referencing CTE_RatingInfo
      (
            SELECT                  ProductID, COUNT(*) ProductCount, SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
                                    GROUP BY      ProductID
                                    Order by      ProductID
            
      )

            SELECT *      FROM CTE_ProductSales

How can I implement the TOP here to remove the error ?
0
I need to find the required doc that need to uploaded by the employee when the expiration date is 90 days before or after.

The query that I am dealing with
SELECT *
from instructors I
inner join userInstructorLicense UIL on UIL.userKey = I.instructorKey
left  Join instructorsDoc ID on I.instructorKey = ID.instructorKey
left outer join lkup_insDoc lID on LID.docKey = ID.docKey
WHERE UIL.userKey = 5
and DATEDIFF(year,UIL.expirationdt,getDATE()) < 0
and LID.status = 1

Open in new window

0
I have a high throughput c# console app which is saving around 250-350 records a sec to a log table (sql server 2017 enterprise)

We are looking at ways to increase performance and sometimes a report is run on the table and things grind to a halt
We are also struggling to keep up at busy times

We currently do not use partitions, and are investigating this
We also insert one record at a time, and attach the insertedID to the c# object of log data for further operations

I'm considering partitions, however i'm not sure what to partition on hour, day, week month
We generally store 6 months of historical data (could also be 1 month or 24 months for smaller number of IOT devices.)
data is usually queried by day, last 7 days, week, month, and sometimes custom date ranges.
I would welcome ideas and comments about what is the best option here, we write more than we query, but queries will span multiple days/weeks

I'm also considering a different approach on inserting, instead of one at a time, do a batch insert.
However i'm unsure how I can then associate the database insertedID with C# object.
Currently its one at a time, so i just add the insertedID to the object, and send that off to messages queues for further processing,

I was looking at the SQL OUTPUT and inserted.ID but not sure when batching 500 records how to marry the inserted ID with the c# object

So in summary I would welcome ideas on
1) partition setup
2) batching inserts and marrying the …
0
Hello Experts,
I have the Following SQL Table1:

Date            Lot_Num      Lot_Weight      Lot_Checked
9/22/2019      1901            22.5            Yes      
9/22/2019      1902            30.6            Yes      
9/22/2019      1903            22.8            Yes
9/22/2019      1904            19.3            No
9/23/2019      1905            18.6            Yes
9/23/2019      1906            35.7            No

I would like to find the % Lot_Checked (Yes and No) based on Lot_Weight by each Day.

For Example, on 9/22/2019 The Percent Lot_Weight Checked would be:

Yes (22.5 + 30.6 + 22.8) / (22.5 + 30.6 + 22.8 + 19.3) = .7972
No (19.3) / (22.5 + 30.6 + 22.8 + 19.3) = .2028

I would like the output to look like this using TSQL:

Date            Checked_A%      Checked_B%       
9/22/2019      .7972            .2028                  
9/22/2019      .3425            .6575


Does anyone know how to do this with as few steps as possible?

Thanks!
0
I have a large table with ID identity seed int (primary key)

Its currently at 1.8 billion rows and growing rapidly
When this hits 2.1 billion rows it will reach the max int size.
Therefore I need to change it to bigint with minimal down time

I've read a LOT of articles online about doing this
I have 3 indexes on this table which references the ID, so I know these will need to be dropped and recreated

My current theory is this
1. Create new column NewId Bigint
2. copy the primary key/int values to this new column so it matches
3. drop all indexes, and unset primary key
4. Drop old ID column
5. Rename "NewId" to "ID"
6. make the new ID column primary key, and identity seed (even though they already contain Id's ?)
7. re-create indexes

Is this solution plausible? what are the issues with the above?
Sql server 2017 enterprise.
0
I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
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.