Microsoft SQL Server 2005

71K

Solutions

26

Articles & Videos

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

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
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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
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
What is SQL Server and how does it work?
LVL 1
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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
Hi Experts,

I'm in middle of a process of securing data in our database.

Would like to move all employees sensitive data into a separate table and have the columns there set as encrypted columns.

Now I am basically looking for an introduction on how to successfully use that within my Access FE application.

Was trying to follow what it says on following link
https://blogs.msdn.microsoft.com/lcris/2005/06/09/simple-demo-for-how-to-encrypt-and-decrypt-a-table-column-in-sql-server-2005/
However when I ran the following view, the encrypted column was showing as null..
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

Open in new window


Thanks in advance.
0
Hi People,

I've got about 8x SQL Servers VM that is used 24x7 and very big, with some of them is around 12.5 TB in single VM (multiple large VMDK):

16x vCPU
128 GB vRAM
C:\ - OS - 300 GB
D:\ - Database .MDF - 6.5 TB
E:\ - Backup .BAK & TRN -23 TB
L:\ Logs 3.1 TB

So my question is, can I just backup the C: drive and the E: drive only so that I can back it up more frequently like every 6 hours (using Veeam Backup - VM snapshot, no SQL transaction logs commit)?

Does backing up the OS and the E: drive containing the SQL maintenance backup file dump is enough to restore it functionally?

I cannot backup the whole VM due to the below concerns and stumble block:
1. Disk space for backup repository
2. Since the SQL server is very busy, sometimes when the  Veeam ABckup failed after 55 hrs of the backup job, snapshot removal process kills it for 3 hours bringing down the entire business with it.
3. Backing up the whole VM is not fast enough with VM snapshot mode.

So any help would be greatly appreciated.

Thanks.
0
Experts

I'm dealing with the following issue:
I need to know how to use a VARIABLE as a linking-field in a SQL QUERY.

declare @variable1 varchar(30) = select top 1 fieldname1 from table_3

Note: Result of @variable1 is a string "Col1" and represents the name of a column in a table. This will be somewhat dynamic in that it can be different columns. (ie. Col1 or Col2 or Col5 etc..etc..)

I need to create a SQL script that will LINK on this @variable1

Like this:

select t1.col1, t1.col2, t2.col3, t2.col4
from table_1 t1
inner join table_2 t2 on t2.col1 = @variable1

The problem I'm having is that @variable1 is using the literal TEXT  "Col1" but I need it to actually be linking on the "DATA" within "Col1"...

Please advise on how to handle this issue?
0
I'm working on a project and we have a geometry column. I'm querying the database to get the data , send it to Mapbox and display it on the US map.

Now, the query is slow and someone at work says there's some GIS method of geometry data type that I can add to the query that simplifies the returned shape and it has some accuracy compression parameter that controls the amount of detail that is lost.

I've been Googling and we don't know what it is.

We're using SQL 2014. C# MVC VS2015.
0
Enterprise Mobility and BYOD For Dummies
LVL 4
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Experts,

I have a table that looks like this:


Attribute_1_Type,Attribute_1_DBFieldName,Attribute_2_Type,Attribute_2_DBFieldName,Attribute_3_Type,Attribute_3_DBFieldName,Attribute_4_Type,Attribute_4_DBFieldName,Attribute_5_Type,Attribute_5_DBFieldName,Attribute_6_Type,Attribute_6_DBFieldName,Attribute_7_Type,Attribute_7_DBFieldName,Attribute_8_Type,Attribute_8_DBFieldName,Attribute_9_Type,Attribute_9_DBFieldName,Attribute_10_Type,Attribute_10_DBFieldName
HPBP,Column_2,CLIENT_NAME,Column_5,OUTBOUND_SUPPRESSION_FLAG,Column_10,COHORT,Column_23,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

I need to create a dynamic SQL script that will result in this:

SELLECT 'HPBP',Column_2,'CLIENT_NAME',Column_5,'OUTBOUND_SUPPRESSION_FLAG',Column_10,'COHORT',Column_23,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL
FROM #MyTempTable
WHERE data_source_id = @datasourceid

So I need to turn a table of data into a SQL Script that can be ran in my stored procedure to produce data.

Can someone help me with the syntax, especially in regard to my example above. I need to single quotes around the specified fields as my example sql script shows.

Thanks
0
Hello,
I have to columns in a table
Date : Data type is Datetime
Time: Data type is Datetime.

need to sort it by Date and time in asc manner

Cheers
0
I have a client that has a program is getting the following error when he tries to start the application:

-- Must Call gAfz.SetName()!! --
SQL Express password is not valid. Invalid password is:

I have attached a snapshot of the error message.

They have not used a password in the past and the software manufacture has said that they do not use a password on SQL Express.

I have checked for viruses and malware and have found none. Unfortunately, there are no restore points prior to whatever event changed the system.

Any ideas of what could be going on with the application?

Thanks for any help you can send me.
20170503_194458.jpg
0
Experts:

This script:

select [name] + ', ' as NameTest
from tempdb.sys.columns where object_id = object_id('tempdb..##attribute2')
for xml path('')


Results in this:
<NameTest>Column_1_Name, </NameTest><NameTest>Column_2_Name, </NameTest><NameTest>Column_3_Name, </NameTest><NameTest>Column_4_Name, </NameTest>

How do I modify the script to produce THIS:

Column_1_Name, Column_2_Name, Column_3_Name, Column_4_Name


Any assistance is greatly appreciated!
0
I've migrated a legacy (XP) SQL Server 2005 application to new hardware which it supposedly much faster than the previous implementation. Indeed, GUI response is clearly faster. However, SQL Server responses continually timeout.

The system was, and is running on XP with SQL Server 2005. The application is Access/VBA with SQL Server 2005 backend. The Access/VBA apps (.ade) can run locally on the XP, but more commonly they run from remote workstations and connect via network to the SQL Server host. Remote workstations are generally Windows 7.

As this is a legacy application, pretty much all of the tables accessed really reside on an SQL Server 2014 host where the new app replacing this legacy one resides. The tables are accessed via views on the 2005 server. For example:

CREATE view [dbo].[tblPaLaborClass] as
select memberType as laborClass, description, null as ts from [dbserver\MEMBERSLINK].hprs.dbo.memberTypes

The new platform was created by using Acronis Restore to copy an exact image from the old system to the new platform, so the XP version, SQL Server version, etc are supposedly identical. Even though the new system is apparently faster, yet queries via the Access/VBA app always timeout, whether local of remote. A remote query with Java takes 50 seconds to return -- which is too long for the Access/VBA app which simply gives a "Timeout expired" message after 30 seconds.

How can I figure out where the bottleneck is? I've been working on this for days.
0
I need the script to do this to an existing table.

Here is the table and I do not want to re-create it.

This is the script to create the table without the column...

USE [Tellus]
GO

/****** Object:  Table [extension].[ExtAPTransferToSF]    Script Date: 5/3/2017 2:07:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [extension].[ExtAPTransferToSF](
	[ExtAPTransferToSFId] [int] IDENTITY(1,1) NOT NULL,
	[MachineName] [nvarchar](100) NOT NULL,
	[JobType] [nvarchar](10) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[ExtensionSource] [nvarchar](25) NOT NULL,
	[CallingFunction] [nvarchar](255) NOT NULL,
	[Status] [nvarchar](255) NOT NULL,
	[Error] [nvarchar](1500) NULL,
 CONSTRAINT [PK_ExtAPTransferToSFID] PRIMARY KEY CLUSTERED 
(
	[ExtAPTransferToSFId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


I have SSMS 2014
0

Microsoft SQL Server 2005

71K

Solutions

26

Articles & Videos

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.