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

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
Hello,


I  am trying to  insert rows from table1 to table2 which are not in table2. The query i have is inserting '0'  in the columns which are of datatype integer , marked as ".Instead  how can I  insert null instead of 0 in the columns (integer)

INSERT INTO [Pt] 

SELECT IET.[Iber]
      ,IET.[Invte]
      ,IET.[Sule]
      ,IET.[SeNo]
      ,IET.[Cy]
      ,IET.[Pde]
      ,IET.[Dion]
      ,IET.[Pion]
      ,IET.[Crge]
      ,IET.[Tpe]
      ,IET.[Aass]
      ,IET.[Eode]
      ,IET.[Clode]
      ,IET.[Quantity]
      ,IET.[TaxRate]
      ,IET.[GrossValue]
      ,IET.[NetValue]
      ,IET.[TaxValue]
      ,IET.[GrossValueHome1]
      ,IET.[NetValueHome1]
      ,IET.[TaxValueHome1]
      ,IET.[ExchangeRateHome1]
         ,IET.[InvoiceGUID]
         ,IET.Originator
         , ''
         , IET.DocumentType
         , ''
         , ''
         , ''
         , ''
         , ''
         , ' '
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         ,''
FROM table2


WHERE [InvoiceGUID] NOT IN 
    (SELECT [Pt] )

Open in new window

0
Hi

What should be a disk storage size for SQL db in day 1, day 2 and day 3.?

Where my database size is 1.2 TB
Provide Storage capacity for 3 years

Day1: starting point. (1-6 months) +30% for disk extension

Day2: addition storage after ( 6-12 months) +30% for disk extension

Day3: addition storage after ( 12 up to 36 months from starting point) +30% for disk extension

Thank you, M
0
Sample data-

CREATE TABLE #emp_records
(
 Id INT,
 Name VARCHAR(80),
 Salary NUMERIC(8, 2)
)
GO
 
INSERT INTO #emp_records
(Id, Name, Salary)
VALUES(1, 'A', 7000),
(2, 'B', 30000),
(3, 'C', 6000),
(4, 'D', 10000),
(5, 'E', 800),
(6, 'F', 500)

Desired Output :-

Id      Name      Salary
2      B      30000.00
6      F      500.00
4      D      10000.00
5      E      800.00
1      A      7000.00
3      C      6000.00
0
Hi

I am wondering how much extra disk storage I need to backup (sql database)

Is there any formula?

For example
My SQL data base size is 1.3tb

How much extra disk space I need to backup 1.3tb of data?

For example
0
I have a server that is running sql express 2008.   the sql server agent is stopped and i am also reading that sql express doesn't have xp agent anyway (so why is it listed in services).  I am receiving a bunch of log file errors where it is advising me to turn on agent xps with an sp configure command.
any thoughts?  currently the app is throwing a bunch of errors and my event log is littered with the agent errors.


the exact language of the error is
sql server blocked access to procedure 'sys.zp_sqlagent_enum_jobs' of component 'agent xps' because this component is turned off as part of the security configuration for this server.  A system administrator can enable the use of 'agent xps' by using sp_configure
0
Receiving syntax error Msg 102, Level 15, State 1, Procedure KLLsp_InventoryRoll_Variance, Line 23
Incorrect syntax near 'v2'.

USE [dataKLL];
GO
/****** Object:  StoredProcedure [dbo].[KLLsp_InventoryRoll_Variance]    Script Date: 11/16/2016 12:36:20 PM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		Michael Burk
-- Create date: 11/16/2016
-- Description:	Retrieve records for Inventory Roll Variance.
-- =============================================
CREATE PROCEDURE [dbo].[KLLsp_InventoryRoll_Variance]
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
select v1.Value1 - v2.Value2 from  

(SELECT  SUM(CalInvPc + AdjPc + RecPc + RetPc - ShipInvPc) AS Value1
FROM    KLL_Cust.dbo.KLLInventoryRoll
WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) ) )as v1   
CROSS JOIN
(  (SELECT   SUM(RepInvEndPc) as Value2 
FROM    KLL_Cust.dbo.KLLInventoryRoll
WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) )  ) )AS v2

Open in new window

0
Hi


How to generate Total data intek per day for individual DB?

Is there any SQL queries I can run against DB to generate this info?

Regards, m
0
Hi

How I can pull info about  total number of records in DBase?

Thx,M
0
Hi

Can you tell me what am I missing to calculate  iops?

I have read  and write trans per second. What else do I need to calculate iops?


Can provide clear example?

Thx, m
0
I keep getting '0' in line 10,  31 and line 125.  These are identified as column '6' or '22' in my code.
The date shows properly in the middle query as it should.  

How can I get the  columns to just show blank, and not zero?

See attached file of the results from the query.

use CLARITY

 (
 SELECT
'B' AS '1', --B,
CONCAT('PR',(CONVERT(CHAR, GETDATE()-1, 112)))  AS '2', --APBATCH_NO,--'PR'+YEAR(YYYY)+MONTH(MM)+DAY(DD)
CAST(COUNT(tx.tx_id) as char) AS '3', --NO_OF_VOUCHER, --COUNT OF UNIQUE ID'S FOR THIS FILE, Actual number of invoices
CAST(SUM(tx.TX_AMOUNT) as CHAR) AS '4', --INV_BATCH_AMT,     --SUM OF H RECORDS, Control amount for invoice amounts (Sum of Distribution Amount?)
cast('' as char) AS '5', --INV_BATCH_STATUS,  --hardcode
cast('' as char) AS '6', --INV_BATCH_TYPE  --hardcode
 --ha.HSP_ACCOUNT_ID AS '7',
--ha.hsp_account_name AS '8',
--tx.tx_id AS '9',
--tx.tx_amount AS '10',
--tx.SERVICE_DATE AS '11',
'' AS '7',
'' AS '8',
'' AS '9',
'' AS '10',
'' AS '11',
'' AS '12',
'' AS '13',
'' AS '14',
'' AS '15',
'' AS '16',
'' AS '17',
'' AS '18',
'' AS '19',
'' AS '20',
'' AS '21',
'' AS '22',
'' AS '23',
'' AS '24',
'' AS '25'
 FROM clarity.dbo.HSP_TRANSACTIONS				tx
 INNER JOIN clarity.dbo.HSP_ACCOUNT				ha ON tx.HSP_ACCOUNT_ID=ha.HSP_ACCOUNT_ID
 WHERE  tx.IS_REFUND_ADJ_YN='Y')


 UNION 
  
 SELECT * FROM
 (
SELECT 
'H' AS '1', --H, --starts HEADER SECTION
CONCAT('PR',(CONVERT(CHAR, GETDATE()-1, 112)))  AS '2', 

Open in new window

0
Hello

I'm using SQL Server 2008 R2.  I have a table with two columns with similar information.  In some rows (not all), the information in Column 1 was erroneously switched with the information in Column 2 and vice versa (the data were received this way).  

I need to count the distinct values that exist in both Column 1 and Column 2.  

e.g., given this:
--------------------------------
Column 1 | Column 2
-------------------------------
    A121    |  A121
    B111    |  C111
    D111    |  A121

The distinct count would be 4
0
If I want to in place upgrade SQL Server 2008 Express to SQL Server 2012 Standard, is it just stop vCenter services and insert SQL 2012 Standard install and perform the upgrade of the vCenter SQL Express database instance, or should I upgrade 2012 Express and then again perform a Edition upgrade to SQL 2012 Standard.
0
How do I remove the decimal point in a string using a SQL statement?

Also, the result must result in a Char field with 7 spaces.

So 123.8 would be 1238xxx with x's being blank spaces in a fixed width file.

or D02.3 as D023xxx with x's being blank spaces always to the right of the string.

I am using SQL 2008.
0
Hello EE,

I have a select Query that, when a user search for a range of numbers lets say :

FROM ID 1 to ID 15

right now in my query, if ID 9 and 10 does not exist at all, in my result I will show only the IDs are obviously exists like this

1
2
3
4
5
6
7
8
11
12

what I would like, is that, I would like to show 9 and 10 with a note "DELETED"  so that the user knows why its not there anymore.


my query starts with SELECT * from myMainIDTable m and some other inner join / left join for other informaiton
and in my WHERE clause I do  WHERE m.ID >= txtFrom.Value and m.ID <= txtTO.Value

so if the ID is not in M, it will not show, but I would like to show with a note.

how can I do that ?
0
We recently began having an issue with our SQL servers sending out email. This is an issue that has recently begun happening as of 10/31/16.  When I look up the error log in SQL, some of the email will send successfully, but randomly we are getting the follow error:
 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1) Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time<c/> or established connection failed because connected host has failed to respond 173.194.200.108:587.  The IP address that is listed here is not the same each time and with each error.  We have tested out the sending account alone and it is sending each time so not sure why when sending out via SQL that there are these random mail failures.

Does anyone have any ideas on what could be causing this?
0
Hi

i am trying to create stored procedure but getting syntax error and dont know what am i doing wrong,

CREATE PROCEDURE Get_Save_ComSon 

@IDTNo float (10),            --Input parameter
@BkgDate as date,
@BasicFRT AS INT 
AS
BEGIN

	SET NOCOUNT ON;


	INSERT INTO [dbo].[CmsonDtl] SELECT [IDTNo]
      ,[ConName]
      ,[Address]
      ,SUM(CASE WHEN [CmsonVasuli] > 0 THEN (AmtTTL) + (ExpTTL) * (CmsonVasuli) / 100 ELSE [CmsonArt] > 0 THEN (CmsonArt) * (Article) ELSE [CmsonWeight] > 0 THEN ([Weight]) * [CmsonWeight] ELSE '0' END) as CMson
      ,@BkgDate
      ,[Description]
      ,SUM(Article)
      ,Sum([Weight])
      ,CASE WHEN [CmsonVasuli] > 0 THEN 'Vasuli' ELSE [CmsonArt] > 0 THEN 'Article' ELSE [CmsonWeight] > 0 THEN 'Weight' ELSE '0' END as CmsonMode
      ,@BasicFRT
      ,@GroupNameCOM
      FROM [ComsonOTHRView]
      WHERE IDTNo = @IDTNo
END

Open in new window

please assist me
0
How can I select distinct values from an column from two different tables.
Table1      Table2
Code         ClientCode
A                     a
B                     D
C                    f

Query result : A,B,C,D,f
0
I have a table with this structure on a SQL Server 2008 DB:

Create Table Test (
	Id int,
	Attributes XML
)

Open in new window


and with this data:

1, '<Attributes><ProductAttribute ID="1"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>2</Value></ProductAttributeValue></ProductAttribute></Attributes>'
2, '<Attributes><ProductAttribute ID="1"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>3</Value></ProductAttributeValue></ProductAttribute></Attributes>'

Open in new window


For readability, here's what the first XML column data looks like:
<Attributes>
	<ProductAttribute ID="1">
		<ProductAttributeValue>
			<Value>1</Value>
		</ProductAttributeValue>
	</ProductAttribute>
	<ProductAttribute ID="2">
		<ProductAttributeValue>
			<Value>2</Value>
		</ProductAttributeValue>
	</ProductAttribute>
</Attributes>

Open in new window


I'm trying to extract the data form the table:

Id, Attribute

1, 1
1, 2
2, 1
2, 3      


I've tried following this post  to flatten the xml and extract the data I want but cannot get my head around it.  Is this the right path?
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.