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

x

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

I upgraded my computer to a clean install of windows 10 and cannot install sql server 2005. I keep getting the message that  sql will not start...

I am using Visual studio 2005.

Thanks for your help!
0
Protecting & Securing Your Critical Data
LVL 1
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Hi All,

I have below code:
	 --IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL 
	 if OBJECT_ID('tempdb..#TEMP') is not null
	 BEGIN
DROP TABLE  #TEMP
END

 

	if 'YASMINE 1017 1548' = ''
	BEGIN
	 
	   SELECT  
	  DISTINCT
	  BarangCode
	  INTO #TEMP
	  FROM TMSTOKBARANGDETIL2018 A
	  WHERE NoTransaksi = 'BS 20180101' 

	END
	ELSE
	BEGIN

	 

	   SELECT  
	  'YASMINE 1017 1548' AS BarangCode
	  INTO #TEMP
	   
	   		 DROP TABLE #TEMP
  
		

Open in new window


I get :

Msg 2714, Level 16, State 1, Line 31
There is already an object named '#TEMP' in the database.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '#TEMP'.

What is the problem ?

Thank you.
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
I wanted to use a substitution variable to list only three departments. I am using the IN clause to list the departments.  I want to only display 3 departments. Meaning select 3 departments from the list to display on the report.

select d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name,
sum(e.SALARY) Total
from employees e left outer join departments d
on(e.department_id = d.department_id)
where e.department_id IN(10, 20, 30, 40, 50, 60, 70, 80, 90, 10, 110, 220, 330, 440, 450, 455, 456)
group by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
order by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
0
hi experts

i have two query's
--query UNO
SELECT [ERDAT     ]
      ,A.[TRDAT     ]
	  ,[Tipo usuario contractual     ]
      ,[Texto       ]
FROM [SMI].[dbo].[01102018_01_USR02] A 
		JOIN [SMI].[dbo].[01102018_USMM] B	ON A.[BNAME       ] = B.[Usuarios    ]
		JOIN [dbo].[02102018_USR05] C ON B.[Usuarios    ] = C.[BNAME       ]
WHERE  [UFLAG] NOT IN (192,64 ) AND
		[PARID                 ] like '%BUK%'
		and [Texto       ] = 'Diálogo     '
		AND try_convert(datetime, [ERDAT     ], 103) > try_convert(datetime, '31.10.2017', 103)
order by try_convert(datetime, [ERDAT     ], 103) 

--QUERY DOS
SELECT BNAME, [BUK], [BZPNR]
FROM (
		SELECT * FROM [dbo].[02102018_USR05]
	 ) AS Data
PIVOT
(
	MAX(PARVA)
	FOR PARID IN ([BUK], [BZPNR])
) AS PVT_Data

Open in new window


How can I join the queries to have a single result?
0
hi experts

i have this query
SELECT
    [BNAME       ]
,      [PARID                 ]
      ,[PARVA                                   ]
  FROM [SMI].[dbo].[02102018_USR05]
  WHERE [BNAME       ] LIKE '%RLARA  %'

this is the results
BNAME             PARID                       PARVA                                  
RLARA             BUK                         1000                                    
RLARA             BZPNR                       13271                                  
RLARA             FIT_ALV_AR                  /CLIENTE EXT                            
RLARA             LE_SHP_DEL_MON_LISTT        HC                                      
RLARA             MOL                         PE                                      
RLARA             UGR                         PE                                      

but i need this
BNAME             BUK                         BZPNR                
RLARA             1000                      13271
0
Im using sql server 5 and I want a query to loop through a table adding a value in each row to a counter and break when i reach a max value - eg TotalQty

eg table
'records'
name qty
lou 1
paul 2
alan 1
jess 3
.. loop through this table and stop when adding qty in each row = TotalQty

eg TotalQty = 4

should return counter = 3
 as it would have looped through rows 1 to 3
if eg TotalQty = 2 it should return counter = 2

pseudocode eg
select * from records
counter  = 0
recordcounter = 0

while counter <  TotalQty
 counter = counter + table.qty
recordcounter = recordcounter  + 1
wend

any ideas welcome
0
hi experts

can you share scripts for administration about block SQL SERVER 2016
0
Hi experts
1. When should I use IFI - instant file initialization?
2. Some URL that I can recommend for the implementation
3. Should I have it configured because it helps in the autogrowth?
0
Restore Stored Procedure in SQL Server
In this article, we will discuss how to deal with a situation wherein you face an issue with a mandatory stored procedure in your critical database.
0
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

The following command string exports a PDF to be saved or opened.

start explorer "<http://ServerName/ReportServer$NamedSQLInstance?%2fPurchasing%2fPurchaseOrder&PoNumber=103290&rs:Format=PDF"

Need to NAME and SAVE the file Automatically, from a Delphi desktop application.
0
Hi experts,

I get a requirement to monitor deleted records in SQL server 2005. As I know, neither change tracking or change data capture is available in this version of SQL server. Any idea would be appreciated!

Best Regards,
Shelwin Wei
0
Hi,

I need a query to return the average per day for each customer and total average of all customers per day. How can i do that?

I can calculate the average for each date/client but how can calculate (and add a row) with the average of the day .

select Avg(Value) as Med , Customername as customer, Datea as dateinvoice from AGH group by datea,Customer

Attached, image with example what i need.

best regards
Img14.png
0
Hello.

I want to create a trigger on a table on INSERT. So when an INSERT attempts to happen on the table, a SELECT is first run and if the SELECT returns rows, the INSERT is cancelled but the transaction ls logged to another table.

The code that has the SELECT in, is as follows

BEGIN TRAN
IF (EXISTS (SELECT *
FROM DBO.TABLE1 AS A, DBO.TABLE2 AS B 
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND PREVBALANCE = 4 AND BALANCE = -2
AND ISSUEDLOCALDATE> '3-JUL-2018'))
BEGIN 
	SELECT @@TRANCOUNT,'UNABLE TO UPDATE TABLE 1 WITH THESE DETAILS - CHECK TABLE_ISSUE' 
	ROLLBACK
END

Open in new window



The above works as expected.

I'm now trying to develop a trigger on the table in question:


USE [DB]
GO
/****** Object:  Trigger [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]    Script Date: 08/16/2018 09:41:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]   
ON [dbo].[WIDGET_COUPON]   
AFTER INSERT 
AS 

	DECLARE @inserted TABLE ([couponID] [bigint] NOT NULL,[organizationID] [bigint] NOT NULL, [orgLevelID] [bigint] NULL, [couponTypeID] [bigint] NULL, [accountID] [bigint] NULL,	[promotionID] [bigint] NULL,[customerID] [bigint] NULL,[hashCode] [int] NULL,[couponCode] [nvarchar](32) NULL DEFAULT ((0)),[serialNumber] [nvarchar](50) NOT NULL,[createdLocalDateTime] [datetime] NOT NULL,[createdHostDateTime] [datetime] NOT NULL,[createdLocationID] [int] NULL,[issuedLocalDateTime] [datetime] 

Open in new window

0
HELLO...

We have a number of Tables, that need to be "migrated" from one database to another, in MSSQL-2012.  There are FK Dependencies on a good number of these tables.  For example:

PS_TKT_HIST_LIN                   (Table-# 1)
PS_TKT_HIST_DISC_COD       (Table-# 2)
PS_TKT_HIST                           (Table-# 3)
PS_TKT_HIST_LIN_CELL         (Table-# 4)
PS_TKT_HIST_LIN_CELL_EXT (Table-# 5)

...and so on.  In the example above, Table-# MUST exist FIRST...then...Table-# 2 MUST exist SECOND, then Table-#1, Table-#4, and Table-# 5.  I know we can use the built-in MSSQL EXPORT/IMPORT function...and that's great...but, because these need to be Exported/Imported IN SEQUENCE (due to the FK Dependencies), we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".

Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.  If we can get an example to provide a roadmap, that would be great.  And before you ask...the COMMAND LINE utilities are not an option...this MUST be done, within Management Studio :-)

Thank you in advance...Mark
0
I have a huge backup file the decomposed into 3 files test.bak1,Bak2 and bak3. How can I restore this DB in Standby mode and apply additional Tlog files?
0
I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
0
This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
0
Hi there. We currently have SQL Server 2008 R2 running on a single server/DC (small office setting), and we just purchased a new server box, and want to move everything over to it (including SQL Server). Unfortunately, the installation media with the licence on it is missing - is there a way to find the licence that the old server with SQL 2008 R2 is using? To clarify, SQL was installed separately, so should have its own licence. I looked through the registry, but the "Productcode" registry key didn't have the correct format (wasen't aaaa-bbbbb-ccccc-ddddd-eeee).
Thanks.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection
LVL 1
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
0
I have a text field in a table and I want to read them. I did this but I still see a value like this
4sIAAAAAAAEAE2PT0sDMRDFv0qZ8x52W//U3LZKRcQt2OJFPAz

 r1.png
0
Hi Guys,

select hashbytes('SHA2_256','123'+'456'+'1') -- Hashbytes will always give UNIQUE value for n number of rows.

Just wanted to know, if we convert the HASHBYTES function to BIGINT like:-

select convert(bigint, hashbytes('SHA2_256','123'+'456'+'1')) will this conversion will always give UNIQUE values if their are million or billions of rows?

Please help.
0
Good morning,

I am having a problem running SSIS package in SQL 2016. I can create and run it in separate Import/Export Data utilities, but when I execute it in SQL itself I am getting an error message "Parser NULL". I also can not run the Import/Export wizard withing the SQL database engine. I am getting an error: "The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered."

Please advise. THanks
0
Hi All,

I have below code.

 UPDATE A
		SET A.NilaiAlokasi = ISNULL(B.TotalNilaiAlokasi, 0)
		FROM TDMONEYPAYDIST A
		INNER JOIN
		(
		SELECT
		KodePembayaran
		, SUM(NilaiAlokasi+NilaiLebih) AS TotalNilaiAlokasi
		FROM TDALCAP A
		WHERE EXISTS
		(
		SELECT
		NULL
		FROM TDALCAP B
		WHERE A.NoAlokasi = B.NoAlokasi
		AND B.NoAlokasi = @NoTransaksi
		)
		GROUP BY
		KodePembayaran
		) B

		ON A.ID = B.KodePembayaran

Open in new window


It is using INNER JOIN, but I think it is not correct, but if I change to LEFT JOIN, then it should not have correct data update.

What should I do ?

Thank you.
0
I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

s1.png
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.