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,

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
Hi, we have an SQL 2008 server (version 10.0.1600.22) running on Windows 2008 R2 Server.

I'd like to upgrade to version 10.50.4000 (SQL 2008 R2 Server).

Is there a way for me to still do that? what about my SQL license? (Standard), will it be kept?
0
MS-SQL 2008 express, currently does not have SSMS installed.
Is is possible to add/install SSMS SQL Server Management Studio. I can't seem to find a satisfactory answer on line.

Secondary:
I'd like to script a re-index (attachment) of the SQL DB as the software using the SQL DB slows down and re-indexing resolves this.
RebuildTblIndexScript.txt
0
Hello Guys,

I need help from you

Please take a look at my example assembled

create table #tmp (
  numlanc int,
  numdoc  varchar(10),
  tipdoc varchar(1),
  valmov float,
  datmov datetime)


insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (1, 'a415', '4', 150, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (2, 'a415', '4', 150, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'a500', '4', 160, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'a501', '4', 174, '20190910')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'b604', '4', 908, '20190501')


-- First search 
select * from #tmp where 1=1 and (numdoc='a500' and valmov=160 and convert(varchar(10), datmov, 112)='20190810')

-- second search
select * from #tmp where 1=1 and (numdoc='a501' and valmov=174)

-- third search  ( here is the problem )
select * from #tmp where 1=1 and (valmov=150 and convert(varchar(10), datmov, 112)='20190810' and tipdoc<>0)

-- with all filters above I need to add more one filter where my query can only returns the register with only one row, 
-- with my query it returns me two rows, as I really have two register with the same values for my filter, I need to put 
-- the result aside. In other words, I only want register that has only one cast in my table with valmov and datmov

Open in new window


Regards
Alex
0
Hi,

I'm migrating a Database Server that has a Transactional Replication configured. In some tables the replication is bi-directional. From the remote site the transactions come to the main site that works as a central repository and is distributed to the other sites.

I have verified that in order to be able to establish replication I need to have the Databases of Origin and Destination without information.

Is it possible to establish replication without having to empty the tables?

SQL Versions: SQL 2014 Publisher, SQL 2014 Distributor, SQL 2008 Subscriber - Main office to remote office.
SQL Versions: SQL 2008 Publisher, SQL 2008 Distributor, SQL 2014 Subscriber - Remote office to main office.
Primer-Diagrama.jpg
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.