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 need to downgrade ms sql server from 2014 back to 2008 and wanted EE advice if we are doint  it correctly.  We plan to first uninstall sql 2014, then reinstall sql 2008, finally restore the sql2008 databases (note when we installed sql2014, we didn't uninstall sql2008).  Please advice if we need to consider any steps omitted.  Thank you in advance.
0
I need help modifying this query

DECLARE @END DATE = '12/01/2017'
DECLARE @Start DATE =DATEADD(month, -11, '12/01/2017')


;WITH _DateList([DATE]) AS(
SELECT @START
UNION ALL
SELECT DATES = DATEADD(MONTH,1,[DATE])   
 FROM _DateList 
 WHERE DATEADD(DAY,1,[DATE])<@END
)
SELECT DISTINCT 
        Yr                  =      YEAR(DATE)
       ,ID     =			RTRIM(e.ID)
       ,NumMonths    =      COUNT(*)
INTO #MemMonths
FROM  dbo.enrollkeys      e 
JOIN   program         p
       ON p.programid = e.programid
JOIN _DateList
       ON DATEADD(DAY,5,DATE) BETWEEN e.effdate AND e.termdate

GROUP BY RTRIM(e.ID), YEAR(DATE) 
OPTION (MAXRECURSION 2000)


SELECT * FROM  #MemMonths

Open in new window


I want is not year to show month/year and group by that. Also, I want to show only 12 months from the start date to the end date.
0
I want to return the last 12 months as header

so if  today's date is 12/01/17 it would do this

01/17 02/17 03/17 04/17 05/17 06/17 07/17 08/17 09/17 10/17 11/17 12/17 TOTAL

under each date I need the count of records from table where the purchasemonth = the month of the heading

How?
0
for a key in my table I am appending RACT to a number..

So the key would be like this:

RACT100000000000000000000

I would like the next id to be:

RACT100000000000000000001
etc


Is there something in the table create that I could make that happen so that they keep adding one each time? Or is there a better way to do in in my t-sql code where I am inserting?
0
i am trying to replace a paragraph of rtf text of a string, i have the following sql
UPDATE [CDISERV2].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = REPLACE(Notepad1, '\par WHEN 
**************** (all sorts of info including ''s /etc)***************
\par 
\par }', '\par WHEN 
**************** (all sorts new info including ''s /etc)***************
\par 
\par }') 
WHERE Notepad1 LIKE '%\par WHEN 
**************** (all sorts of info including ''s /etc)***************
\par 
\par }%';

Open in new window


Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

is there a different way for me to find and replace a string
0
i have a restricted growth setting for 2 transcations logs(.ldf) for one sql server db(Simple recovery).  

where I can the see the % of logs filled
and what can i do clean up? shrink log or db?

and db for mdf and ndf is in unrestricted growth

will the transactions logs commit  to .mdf or ndf or clean up the .ldf, once sql server is restarted?

customer is doing lot of inserts, updates,etc I guess logs will fill up immediately

Thanks
0
I'm using the Orders table from the Northwind sql server database.

Here it the script to create the table.

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [nchar](5) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[RequiredDate] [datetime] NULL,
	[ShippedDate] [datetime] NULL,
	[ShipVia] [int] NULL,
	[Freight] [money] NULL,
	[ShipName] [nvarchar](40) NULL,
	[ShipAddress] [nvarchar](60) NULL,
	[ShipCity] [nvarchar](15) NULL,
	[ShipRegion] [nvarchar](15) NULL,
	[ShipPostalCode] [nvarchar](10) NULL,
	[ShipCountry] [nvarchar](15) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] 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 IDENTITY_INSERT [dbo].[Orders] ON 

INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10248, N'VINET', 6, CAST(N'1996-07-04T00:00:00.000' AS DateTime), CAST(N'1996-08-01T00:00:00.000' AS DateTime), CAST(N'1996-07-16T00:00:00.000' AS DateTime), 3, 32.3800, N'Vins et alcools Chevalier', N'59 rue de l''Abbaye', N'Reims', NULL, N'51100', N'France')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate],

Open in new window

0
I need to make a database that returns a value type char or varchar being the first to enter a decimal by means of a function.
CREATE FUNCTION Descripcion (@precio decimal(8,2))
RETURNS decimal
AS BEGIN DECLARE @descripcion char(25)
SET @precio =
CASE
WHEN @precio <= 50 then 'Barato'
WHEN @precio > 50 and @precio <= 100 then 'Regular'
WHEN @precio > 100 and @precio <= 200 then 'Caro'
ELSE 'Muy Caro'
END
RETURN @descripcion
END

SELECT IdProducto, ProdNombre, PrecioVenta, dbo.Descripcion(PrecioVenta) FROM Productos

But I get the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
0
ms sql management studio how to query select stored proc by passign inputs graphically without writing query.

one of my colleague passed inputs in one cool graphical option in ms sql management studio
not exactly sure how he was able to do it
please advise
0
We are looking for the proper way to upgrade a sql2008 server instance to have the same version number sp number as another.  How can we go about this?
0
Hi,

I need to get last 1000 customers  (customerid) from invoices table. But if a Customer is repetead more than 1 time, i only need 1 row.

Invoices Table
--------------------
Date
CustomerId
Date
Value

best regards
0
Hi experts,

This is related to the queries in this ticket.
https://www.experts-exchange.com/questions/29096348/using-t-sql-date-function-to-get-records-older-than-10-days.html?anchor=a42544980¬ificationFollowed=206809480#a42544980

I'm using sql server 2008

I have a table that looks like this called Orders2:

Notice that there are 2 records for 4/16/2018.

Table2.PNG
This is the script for this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders2](
	[OrderID] [int] NULL,
	[LastName] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[OrderDate] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (1, N'Smith', N'Sam', CAST(N'2018-04-16T20:10:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (2, N'Johnson', N'Tony', CAST(N'2018-04-16T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (3, N'Williams', N'Anna', CAST(N'2018-03-27T21:23:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (4, N'Westbrook', N'Kyle', CAST(N'2018-03-27T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (5, N'George', N'Norman', CAST(N'2018-03-26T23:21:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], 

Open in new window

1
i'm using sql server 2008

I have a table that looks like this:

p1.PNG
This is the script to create this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders1](
	[OrderID] [int] NULL,
	[CustomerID] [nvarchar](255) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[Freight] [decimal](20, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11074, N'SIMOB', 7, CAST(N'2018-04-24T09:34:00.000' AS DateTime), CAST(18.44 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11075, N'RICSU', 8, CAST(N'2018-04-23T08:30:00.000' AS DateTime), CAST(6.19 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11076, N'BONAP', 4, CAST(N'2018-04-23T07:10:00.000' AS DateTime), CAST(38.28 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11077, N'RATTC', 1, CAST(N'2018-04-20T08:20:00.000' AS DateTime), CAST(8.53 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11070, N'LEHMS', 2, CAST(N'2018-04-15T10:23:00.000' AS DateTime), CAST(136.00 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11071, N'LILAS', 

Open in new window

0
Hello guys

I have a table with some rows, I need to make a query where only the row that is diferent from must be return in my query

something like this the x.codprod='A1258'


select * from table01 a
inner join table01 b on (b.xname<>a.xname and b.xcoddep<>a.xcoddep and b.xcodprod<>a.xcodprod)

it should return me

create table table01 (
xname varchar(20),
xcoddep varchar(20),
xcodprod varchar(20)
)

insert into table01 (xname, xcoddep, xcodprod) values ('alexandre', '01452', 'A1257')
insert into table01 (xname, xcoddep, xcodprod) values ('alexandre', '01452', 'A1257')
insert into table01 (xname, xcoddep, xcodprod) values ('alexandre', '01452', 'A1257')
insert into table01 (xname, xcoddep, xcodprod) values ('alexandre', '01451', 'A1258')

select * from table01

Open in new window



How to do that?
thanks
0
I have a SQL Server 2008 R2 instance with a database which is currently set to use the Simple recovery model. The database files are distributed between the primary file group and several additional file groups. I'd like to change this database over to use the Full recovery model, taking a Full database backup every 6 hours and transaction log backups every 15 minutes. Do I need to do anything additional to handle backups for those additional file groups or will the Full database backup file include those files? My understanding of the recovery process under the Full recover model would just be to restore the Full database backup and then sequentially restore any transaction log backups that occurred after that Full backup was taken. However, I just want to be certain I'm not missing anything with those additional database files & file groups.
0
I'm taking Backup of the LOG every 15 minutes, but which LOG has created up to 90GB.

While it is true every time you get a BK Full or LOG truncates the LOG, but this has grown a lot, as you can calculate how much is needed for transactions LOG and not grow as much, as in this case.

This is for several BD that you have.
0
Hi

what are the permissions that my user must have in order to see the jobs (successful, failed)

1. If I can see the msdb database but I do not see the jobs
2. You do not work with the integration services catalogs, they inform me that they work the jobs through the agent and see them visually with jobis
3. My server roler to the instance is public
4. My role membership in the database is role_main and public

I need a permission that allows me to see the jobs, where they show error what are the errors, if they stay hung, execution time etc etc. The limitation is that DBA can not give me administrator permission on the instance.
0
claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3215276                      SCH091                               RMR                              1851298                      1669307
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307

need sql query that removes only the duplicate claimremark values where accession_no <> medinfoapptno.

Results:
claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307
0
Greetings,

This is regarding an issue I have with an SQL 2012 server.  I had obtained assistance from Microsoft on a performance issue several months ago, and they placed the server into some sort of debug or trace mode as part of their diagnostics.  I thought they turned off the trace mode after diagnostics were completed, but as it turns out they did not.  I found this out after noting that available disk space on the SQL server's C: drive was slowly being reduced over time.  There is a debug or trace mode active that is generating daily folders containing log files for apparently every single transaction being executed on the SQL server.  Folders are named by date, so today's log files folder is under a folder called LOGS, and is named 2018-04-18.  Here is a listing of the files in that folder:

- c. 25 files named all.XX.log where XX goes from 1 thru 22.  Files vary in size from c. 15K to c. 2.5MB
- a file called all.csv which is 31K in size
- 6 files called fatal.X.log where X goes fro 0 to 6.  Files are up to 10K in size
- 15 files named Info.XX.log where XX goes from 0 thru 14.  FIles are 1K to 6K in size.
- 22 files called Trace.XX.log, where XX goes from 1 thru 22.  Files are 22K to 2.2 MB in size.
- 8 files called Warn.XX.log, where XX goes fro 0 thru 7.  Files are 1K in size.

On a typical work day, from 25 to 40 MB worth of files are generated in each daily folder.  Currently I have to manually clean these out once a month to keep the accumulation …
0
The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.
0
Hello guys

I have a table with 200 rows and this table doesn't have a identity field. I need to create a field name seq as a int and after that
populate it with number and then create a primary key.

to create the field, I know how to.
to create the primary key I know how to

to populate it with a incrimental number, I don't know.

how to do that?

thanks
0
I have a set of data that I need to clone from three cascading tables
tblSurvey has SurveyID, SurveyName   (UID is SurveyID)
tblQuestions has QuestionID, QuestionText, SurveyID   (UID is QuestionID)
tblAnswers has  AnswerID, AnswerText,QuestionID  (UID is AnswerID)

I insert into tblSurvey (Select SurveyName from tblSurvey where SurveyID = @OldSurveyID)
I insert into tblQuestions (Select QuestionText,@NewSurveyID from tblQuestions where SurveyID = @OldSurveyID)

My problem is matching inserting the AnswerText and matching to the new set of QuestionID's
0
PLEASE

view attachment

The files . mdmp are being saved in the Log folder of Disk C where SQL Server C: \ Program Files \ Microsoft SQL Server \ MSSQL13.POSEIDON \ MSSQL \ Log is installed, the .mdmp files are equal to the dump file I think, when you I right click on a dump file and see its properties, its extension is .mdmp

On the C disk the mdf and ldf files of the databases are not being saved, on the C disk there are only the installation folders and the 223 GB weight of the log referred to the Log of the installation folder C: \ Program Files \ Microsoft SQL Server \ MSSQL13.POSEIDON \ MSSQL \ Log and it's just so heavy because every day a lot of Dump files are being generated

 On the other hand, we do not have visible problems with the bd server, everything works fine, it does not fall or anything, that's why I wanted to know why so many dump files are being generated daily or how could I identify what is happening to solve it? that eliminates the dump files and frees a little space, in a few days it is full again.
0
I need to do a double pvit with counts

Select Origin, IndividualID, Cycle, StateCode from Individuals
There are 6 cycles and 56 "states"

The count will be on IndividualID with a PIVOT on the Cycle and the state codes

An example output would be...

Origin      Cycle1     Cycle2     Cycle3     Cycle3     Cycle4     Cycle5     Cycle6     AL     AZ     etc...
MyOrigin    3              30            19             21             3               5              6         22       54    etc....
0
Good afternoon, I have a question, is it correct to configure the SQL Server Databasemail to send mail, this violates the security of our server ?, if it were the case that another solution is had.

This commented since I want to do it so that I get emails when there is an error in several JOB, either maintenance or sending reports or internal processes of the company.

Thanks for the help.
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.