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

We have a current SQL query which summarizes the hours worked by a particular branches staff.  Rather then running this report every month I would like to create a new report which automatically calculates the totals for each month, over an 18 month period.

EXAMPLE:

Current query has TOTAL HOURS -- EMPLOYEE FIRST NAME -- EMPLOYEE LAST NAME as headers.

I would like to see: EMPLOYEE FIRST - EMPLOYEE LAST - 2015JAN - 2015FEB...2016JAN - 2016FEB...

The current query is listed below:

use DB1
go
select      sum(timesheet_orders.no_of_hours) as TotalHours,
            user_profile.first_name, user_profile.last_name
From orders
            Inner Join timesheet_orders on orders.order_id = timesheet_orders.order_id
            Inner Join timesheet_header on timesheet_orders.timesheet_number = timesheet_header.timesheet_number
            Inner Join client_organization on orders.group_id = client_organization.group_id
            Inner Join user_profile on timesheet_header.contractor_id = user_profile.user_id

where timesheet_header.start_date >= dateadd(mm,-1,getdate())
             and timesheet_orders.status = 'P' and orders.office_id = '2601'

group by user_profile.first_name, user_profile.last_name

order by TotalHours desc


I know another query we created uses the DATEPART command but I am unsure of the proper syntax to use something like this with our current query.

Any assistance would be appreciated.
0
We have an Access App with Linked tables to a SQL Server 2005 Database.

We have a Linked Server set up from SQL Server 2005  to a SQL Server 2008 Database for a call from a view in the SQL Server 2005 DB.

Some (but not all) user are receiving this error When trying to access the ACCESS Database:

Run-time error '3146' ODBC Call Failed

Error we are seeing in the SQL Server log on the  SQL Server 2008 Database:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xx.xx.xx]

All users have the same permissions to Databases in BOTh SQL Servers via AD Group permissions.

Here are the properties of the linked Server:

/****** Object:  LinkedServer [MySQLServer]    Script Date: 11/23/2016 11:52:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MySQLServer', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MySQLServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'dist', @optvalue=N'false'
GO

Open in new window

0
hi experts

i am reading about Auditing in Azure SQL Database in
https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-auditing-get-started

what's the mean?:
Plain SQL ?
Parameterized SQL ?
Transaction Management?

this complete text:
Only database events may be audited (success and/or failure):
.Plain SQL
.Parameterized SQL
.Stored Procedure
.Login
.Transaction Management
0
Hi Friends,

Is it possible to direct/In-place update/Upgrade of Citrix XenApp 6.5 Datastore database from MS SQL 2008 SP3 to SP4 or MS SQL 2008 R2 without any issue for Citrix XenApp Farm ?

Kindly Advice
0
I'm using sql server 2008

I have  table that looks like this:
table
Here is the script to create my table

USE [Northwind]
GO
/****** Object:  Table [dbo].[EmployeesTest]    Script Date: 11/23/2016 3:01:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew W Fuller Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael J Suyama Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert T King III')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO

Open in new window


If you notice, the FullName column it contains a string. The string is made up of FirstName MiddleInitial LastName Suffix.

How do I split the FullName column into the four parts and place each in it's own column.

SELECT [EmployeeID]
             ,[FullName]
             -- get FirstName part from the FullName column and place in column called FirstName

             -- get MiddleInitial part from the FullName column and place in column called MiddleInitial

             -- get LastName part from the FullName column and place in column called LastName

             -- get Suffix part from the FullName column and place in column called Suffix

FROM [Northwind].[dbo].[EmployeesTest]


Anyone know the best way to split fullname column?
0
i'm using the northwind sql server database

when I use a query like this I am able to concatenate two columns to create the FullName column

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
        ,[FirstName] + ' ' + [LastName] AS FullName
FROM [Northwind].[dbo].[Employees]

It looks like this:

query 1
So now I'm trying to do the reverse.

I have a table that looks like this. The FullName column is a varchar column

EmployeesTest table
This is the script for this table

USE [Northwind]
GO
/****** Object:  Table [dbo].[EmployeesTest]    Script Date: 11/23/2016 1:29:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew Fuller')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael Suyama')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert King')
GO
INSERT [dbo].[EmployeesTest]

Open in new window

0
hi experts

i am reading about Row-Level Security in
https://msdn.microsoft.com/en-us/library/dn765131.aspx
https://www.datavail.com/blog/row-level-security-never-got-this-easy-with-sql-server-2016/

I could provide some good articles to go deeper into the subject
0
hi experts

i am reading about Auditing with Temporal Tables in https://msdn.microsoft.com/en-gb/library/dn935015.aspx

I could provide some good articles to go deeper into the subject

why, Temporal tables limitations?
User tracking requires adding a column to the table to hold SUSER_SNAME
0
Hi

We currently have 3 files that are uploaded through SFTP to us on a daily bases.  Filename examples:

 - Invoices_20161122.csv
 - Agencies_20161122.csv
-  Sales_20161122.csv

The file names will change the date stamp every day when they are uploaded to the SFTP site

The files need to be uploaded in their associated tables

- Invoices
- Agencies
- Sales

The tables would need to be dropped and recreated when the files are imported.  The first row in the files contains the column names.

How can I create an SSIS package to:

- Drop the tables
- Recreate the tables when importing the CSV file and name them appropriately
- Move the old files an archive folder


Thank you for all of your help
0
hi experts

i am reading about: GRANT, REVOKE, DENY
but i do not understand: In general, DENY permissions should be avoided and only used in exceptional circumstances.
0
I have a table of equipment, say just ID column and description.  Then a related table of work orders  - ID column, description, date of work, and FK to equipment.   One to many.     I need a query that pulls the 5 most recent work orders by date for each equipment record.
0
EXISTS (T-SQL)
https://msdn.microsoft.com/en-us/library/ms188336.aspx

I have a table called Employees1 which looks like this. I have another table called Employees2 which is basically a copy of Employees1
Employees1
I just created these two tables for this example.

Here is the script to create Employees1
USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees1](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees1] ON
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(0x000045D100000000 AS DateTime), CAST(0x000083BB00000000 AS DateTime), N'507 - 20th Ave. E.Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], 

Open in new window

0
Please experts

any example about: Partially Contained Databases - Cross Database Queries
0
a) in below statements, if for any reason, if Statement 1 or Statement 2 fails, how can it be combined in a WHILE to keep executing it till the error goes away.. (the transactions is real time and the statements will work eventually if they should fail)

b) in relation to above (a), is it possible a time dimension.. that the BEGIN END WILL BE KEPT Running upon failure upto __ minutes.. (example 5 or 10 minutes)..

IF  EXISTS
(SELECT column FROM table WHERE conditions)
BEGIN
        Statement 1
        Statement 2
END
0
we are working with encrpted sp and wanted to know EE advice when needing a store procedure to have a "where" clause in the querey.  Is it treated as regular SP?
0
hi,

if I open a SSMS console session it will have a SPID itself, is there any other way to kill any other spid on that SQL server box? I want to automated that too if it is possible.
0
Any advice if we crypt some store procedures in a db that already has uncryped SP.  Will there be any conflict? Please advice.
0
Hi experts

i am Reading about: Partially Contained Databases - Connection Strings must be explicit
but i do not understand, can you give me an example.
0
hi experts

i am Reading about Contained Database Collations, but i do not understand
Contained Database Collations
and
Collations of Tempdb
0
hi experts

i am Reading about: Benefits of Database Containment:
Initial Database Development
and
Database Administration. But i do not understand.
Can you give me an example.

Initial Database Development
Because a developer may not know where a new database will be deployed, limiting the deployed environmental impacts on the database lessens the work and concern for the developer. In the non-contained model, the developer must consider possible environmental impacts on the new database and program accordingly. However, by using partially contained databases, developers can detect instance-level impacts on the database and instance-level concerns for the developer.

Database Administration
Maintaining database settings in the database, instead of in the master database, lets each database owner have more control over their database, without giving the database owner sysadmin permission.
0
hi how can we send an encrpted query so where we work on we don't leeave our "know-how"?  is there a way?  Thank you very much experts.
0
Hi Experts,

Does any one have an idea what this error for and how to resolve it :

Log Name:      Application
Source:        SharePoint 2010 Products Configuration Wizard
Date:          11/19/2016 4:36:00 PM
Event ID:      104
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      ZEBRA.ACL.COM
Description:
Failed to create the configuration database.
An exception of type System.Configuration.ConfigurationErrorsException was thrown.  Additional exception information: A configuration file cannot be created for the requested Configuration object.
System.Configuration.ConfigurationErrorsException: A configuration file cannot be created for the requested Configuration object.
   at System.Configuration.MgmtConfigurationRecord.SaveAs(String filename, ConfigurationSaveMode saveMode, Boolean forceUpdateAll)
   at Microsoft.SharePoint.Administration.SPIisWebServiceSettings.ProvisionWebSiteSettings()
   at Microsoft.SharePoint.Administration.SPIisWebServiceInstance.Provision()
   at Microsoft.SharePoint.Administration.SPFarm.Join()
   at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.CreateOrConnectConfigDb()
   at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.Run()
   at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

Open in new window


Waiting for your urgent help.

Regards,
D Patel
0
Hi

Using SQL 2008

I am trying to calculate the time in hours between two datetime fields.

However the query must take into account business hours

These are Mon - Fri 07:00 to 00:00 (midnight)
Saturday and Sunday and Public Holidays 08:00 to 18:00

Example

Open Date                                  Close Date
2016-11-17 23:00:00.000            2016-11-18 08:00:00.000

The result would be 2

Any help would be appreciated
0
I need to concatenate comments based on the customer ID, activity date, activity code, activity time.

I wrote code to get the duplicate number which tells you how many rows one comment should consist of.

 I need assistance how to concatenate the rows into one where the dup number is greater than 1.

I have attached a picture and highlighted what I am looking for.
0
hello,

how to group by col1 and order by col2 in query

Select * from table groupby col1 and order by col2

Cheers
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.