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 have a column that I need to always be 4 characters

It may at timesbeb 2 or 3

In thise cases I need to pad out to the left some zeroes.
0
Three Reasons Why Backup is Strategic
LVL 4
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Hello,
Can you please help,
I'm using below code, but it  returns multiple rows.
I need to combine the NoteText, seperated by __

Select Distinct FO.Orderno AS [Order #],FO.Orderdate AS [Order Date], NoteText AS [Reason]
From FinalizedOrders FO
INNER JOIN FinalizedOrderNotes ON FO.OrderNo=FinalizedOrderNotes.OrderNo
Where NoteText Like '%QA1%'
AND FO.orderdate >= ................. and ............... and ..................

Example Results:
Order #                       Order Date                                  Reason
7517502                       2017-07-10 08:31:55.000      Q1-Test1
7517502                       2017-07-10 08:31:55.000      Q1-Test2
7517502                       2017-07-10 08:31:55.000      Q1-Test3

Would like to have it
7517502                       2017-07-10 08:31:55.000      Q1-Test1__Q1-Test2__Q1-Test3

Your help is appreciated.
0
I have a table that stors table counts with a running total at a particular time
SELECT tbl, tme, cnt  FROM #RunningCount ORDER BY tbl, cnt
I need to add logic that partitions by tbl value
And gets the difference between the most recent row and the provious row cnt for THAT tbl
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

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
How can I estimate the local area network utilisation involved in remotely executing a simple SQL Server Stored Procedure ?

I have a windows application that uses ADO to execute a stored procedure on a Microsoft SQL Server 2005 database server.
The stored procedure is simple and returns a single result in an  integer output parameter.

I am executing this procedure every 10 seconds and have been asked to estimate the "network utilisation".  There are no input parameters to the stored procedure. I will be answering the question as "virtually no network utilisation" but I anticipate being asked how to justify this statement.
0
Hello guys

I am having problem to do this, maybe the way is not correct and I don't have another idea how to achieve the task

take a look at the code:
with tab1 
as
( select numped, seqped, seq from arifat 
       where arifat.numped = 19315 
)
, tab2 as
(
  
  select * from dbo.Calculo_Tributos_Aux('FAT', 'P', '', tab1.numped, tab1.seqped, tab1.seq)  
)

select * from tab2

Open in new window



is it possible something like this?

I got this error:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'tab1'.

thanks
0
this code not show error

select PERIODO,
            SUM(CONVERT(float, DEBE)) AS DEBE_MENSUAL,
            SUM(CONVERT(float, HABER)) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

PERIODO      DEBE_MENSUAL      HABER_MENSUAL
20150500      1740846979.85011      1740849103.85011

this code show error
select PERIODO,
            SUM(CONVERT(decimal(14,3), DEBE)) AS DEBE_MENSUAL,
            SUM(CONVERT(decimal(14,3), HABER)) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Float is better than decimal?
0
Hi experts
In May I had installed the sql server express with the ssdt. SSDT showed error, see attached file

In June a new installation was made and now I have the sql server 2012 developer
Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
Sep 23 2016 15:49:43
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack

However the error is the same as it showed in May and I can not create packages in the SSIS
www.png
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi; How can i solve repeated value columns issue solve?  I have a query below, it is producing below table. But Batch and  ExpiryDate always repeated . they are the same in all rows. How can i rearrange below query?

SELECT k.*,k1.Batch FROM 
                            (
	                            SELECT 
	                             CONVERT(DATETIME, s.[ExpiryDate]) ExpiryDate
	                            ,s.[CustomerFullName]    
	                            ,s.[CustomerID]
	                            ,s.[MaterialName]
	                            ,s.[MaterialNumber]
	                            ,s.DeliveryDate
	                            ,s.SapContractNumber
	                            ,s.SapDeliveryNumber
	                            ,DATEDIFF(DAY, GETDATE(),s.ExpiryDate) DateDiff,
	                            s.PartType
	                            FROM [Sade].[dbo].[StokView] s 
                            WHERE  ( s.[ExpiryDate] BETWEEN @date1 AND @date2 )   @customer
                            )k
                            OUTER APPLY 
                            ( 
	                            SELECT di.Batch FROM dbo.DeliveryItems di WHERE di.SapContractNumber= k.SapContractNumber 
	                            AND PartType <> 'ReAgent'
                            )k1

Open in new window

image.png
0
This is SQL 2014

I have this sample data

create table #temp
(
   rowId int identity,
   cId int,
   year int,
   rank int,
   cName varchar(500)
)
 
 
 
insert into #temp
select 1, 1712, 2015,3,'test1'
 
insert into #temp
select 2, 1929, 2015,2302,'test2'
 
insert into #temp
select 3, 1712, 2014,1,'test1'
 
insert into #temp
select 3, 1929, 2014,2024,'test2'

Open in new window


I get 4 row. This is how I want it to look like. (I wanted to loop thru the #temp table, compare the "cid" and then maybe concat and dump the result into another table but that can't be the correct way of doing this)

I need the data in "Rank" column to be from earliest year (2014) to latest year (2015)

CId                           Year                                        Rank                              Cname

1712                    2014,2015               1,3                                  test1

1929                     2014, 2015                           2024,2302                              test2
0
hi experts

I have a table with 100,000 records, as I should modify it to support temporary system tables

this create code
CREATE TABLE dbo.Manager
( ManagerId int NOT NULL PRIMARY KEY,
  ManagerName nvarchar(50) NOT NULL,
  ManagerPassword varbinary(200) NOT NULL,
  ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
  ChangedBy sysname NOT NULL CONSTRAINT DF_Employee_ChangedBy DEFAULT  (SUSER_SNAME()),
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ManagerHistory));
GO

But my table already exists and has data, so my question is: how do I modify the table
0
We have an old SQL 2005 server that is a VM machine and has been working fine but on Monday the backups started failing due to medial errors.  I looked at the permissions on the folder that the backups are stored and the local administrative account has full permissions but the backups are failing. Below is the error we are receiving and I would like to know how to troubleshoot and fix this issue. The database seems to be working fine so I don't think it is a corruption issue but can't be sure.

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.5069 Report was generated on "SQLSERVER3".
Maintenance Plan: Daily Full DB Backup Plan
Duration: 01:11:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Full) (SQLSERVER3)
Backup Database on Target server connection
Databases: CommerceCenter,master,msdb
Type: Full
Append existing
Task start: 2017-06-06T18:30:04.
Task end: 2017-06-06T19:39:16.
Failed:(-1073548784) Executing the query "declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'CommerceCenter' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'CommerceCenter' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''CommerceCenter'' not found.', 16, 1) end RESTORE VERIFYONLY FROM  DISK = N'M:\\MDF Files\\CommerceCenter\\CommerceCenter_backup_201706061830.bak' WITH  FILE = @backupSetId,  …
0
CREATE DATABASE RLSDemo
go
USE RLSDemo
GO
CREATE USER Employee1 WITHOUT LOGIN;
GO
CREATE USER Employee2 WITHOUT LOGIN;
GO

As you may be able to create database users without login in a DB that is not contained
0
hi experts
i do not understand
Temporal tables limitations:
User tracking requires adding a column to the table to hold SUSER_SNAME

 https://msdn.microsoft.com/es-es/library/dn935015.aspx

can you give me example in T-SQL
0
Hi;

Below query is extremely too slow and getting alotof rows is not related my customer. I guess something is wrong. How can I make it faster?
 SELECT
                                        DISTINCT
                                         s.Id
                                        ,s.[Batch]
                                        ,s.[BillTo]
                                        ,CONVERT(datetime, s.[ExpiryDate]) ExpiryDate
                                        ,s.[MaterialNumber]
                                        ,CONVERT(INT, s.[Quantity]) Quantity
                                        ,s.[PartType]
                                        ,s.[TestAmn]
                                        ,s.[SapDeliveryNumber]
                                        ,d.[SapContractNumber]
                                         ,sc.MaterialName
                                        ,s.Stock Total
                                        ,(SELECT CustomerFullName FROM Customers c WHERE c.CustomerID = @customerId) CustomerFullName
                                        FROM DeliveryItems d
                                        left JOIN Stok s on  s.SapContractNo= d.SapContractNumber
                                        left JOIN dbo.StockCards sc ON s.MaterialNumber = sc.MaterialNumber
                                         where d.SapContractNumber=@contractNumber and  s.BillTo=@customerId  and s.Stock>0 ORDER BY s.SapDeliveryNumber

Open in new window

0
i have this query

SELECT col_1
            ,SUM(CAST([COL_13] AS decimal(28,2))) as DEBE
            ,SUM(CAST([COL_14] AS decimal(28,2))) as HABER
FROM [dbo].[CuentaCorriente_2015]
GROUP BY col_1

i test from
SUM(CAST([COL_13] AS decimal(10,2))
.
.
.
.
SUM(CAST([COL_13] AS decimal(28,2))

but always error is
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
cuenta_corrient.txt
0
As I can determine the first parameter of the decimal data type, sometimes I had to put 10 and sometimes I put the value of 11, I did a lot of tests. There is some function that allows me to determine the value of the first parameter x of the decimal (x, y)

select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
            SUM(CAST(HABER AS decimal(10,2))) AS HABER_MENSUAL
FROM [dbo].[Octubre]
WHERE ISNUMERIC(DEBE)=1 AND ISNUMERIC(HABER) = 1
GROUP BY PERIODO


UNION ALL
select PERIODO, SUM(CAST(DEBE AS decimal(11,2))) AS DEBE_MENSUAL
            , SUM(CAST(HABER AS decimal(11,2))) AS HABER_MENSUAL
FROM [dbo].[Diciembre]
WHERE ISNUMERIC(DEBE)=1 AND ISNUMERIC(HABER) = 1
GROUP BY PERIODO
0
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

This sql statement works

SQL = "SELECT top 1000000 convert(varchar, TimeStamp, 101) + right(convert(varchar, TimeStamp, 120),9), LogicName, PointValue FROM Trends"

Open in new window


But if I add "Order By" I get a query timeout error within 15 seconds

SQL = "SELECT top 1000000 convert(varchar, TimeStamp, 101) + right(convert(varchar, TimeStamp, 120),9), LogicName, PointValue FROM Trends order by LogicName ASC"

Open in new window

0
this query
select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
            SUM(CAST(HABER AS decimal(10,2))) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

has errror
Error converting data type varchar to numeric.
0
I work for a law firm where a client case file is called a Matter.  Each Matter can have multiple 'Matter Originators', 'Responsible Attorneys' and 'Paralegals'.  Originally I only took the TOP 1 of each category and UPDATED Server2.DB2.  The below query is what I used.  

MERGE Server2.DB2.[dbo].HBM_Matter AS Target
	USING 
	(		
		SELECT DISTINCT mp.Matters, 
		(
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE mp1.AssignedType = 'Matter Originator' AND Matters = mp.Matters
			ORDER BY mp1.AssignedPercent DESC					
		)  AS MORG,		
		(		
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE mp1.AssignedType = 'Originating' AND Matters = mp.Matters
			ORDER BY mp1.AssignedPercent DESC							
		) AS ORIG,
		(
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE Matters = mp.Matters AND mp1.AssignedType = 'Responsible' 
			ORDER BY mp1.AssignedPercent DESC
		) AS RESP
		

Open in new window

0
hi,

right now we have a task to relocate SQL 2000 (long time ago, I can't remember how to do it...) from a Windows 2000 server to Windows 2003 server so that we can apply security patch on Windows 2003 for any new virus attack.

anyone still remember how to move system DBs and user DBs from one SQL 2000 to another SQL 2000 ?
0
Hi.  I wrote a stored procedure (SQL Server 2008 R2) that moves files between folders on our network.  I am using an Access frontend.  The form has a button that users click to call the stored procedure.  

The file move works for me but does not work for my users.  My login has system admin privileges.  I am a domain admin.  I have a Windows Test account that is not a domain admin and doesn't have any special privileges on the server anywhere, just like the users.    I am logged in as this test user on another computer so I can test. The error message is "User does not have permission to perform this action."

I looked at links on the web and tested many different ways.  However, each way I try works for me but not the users.  When I push the button in my test program, the logic should rename the test file on the server under c:\Temp even though the users do not have permissions to this folder through Active Directory.

Here's the link I used as a basis for my code:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

I am testing with a basic stored procedure to rename a file on the server.  I applied the logic from the link above.  It works for me but not for my users.  I also tried "EXECUTE AS" my login but that doesn't work either.

USE [ExperimentTracker]
GO
/****** Object:  StoredProcedure [dbo].[spTestWindows]   

Open in new window

0
I am looking into a migration of an environment which was recently aqcquired from a previous IT, havent had the best of handovers. The business has 2003 R2 server running SQl 2005 Express , the business has now got a new Windows Server 2012, I have installed SQL Server 2016 SP1 express edition.

There is one database on the 2005 which was built inhouse and has a IIS6 front end. I am trying to find the best way forward to get the database from 2005 onto the new 2016. I believe i need to backup and import the DB and also do the same for the IIS front end.

-Please assist in providing a migration plan, how to preform this, high level/low level
-Any issues in migrating from 2005 to 2016, should i opt for SQL 2014
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.