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

Please help folks.

How to club this two logics together for a single field. Field name is description.

1.) case when right(rtrim(Description),1) = ',' then substring(rtrim(Description),1,len(rtrim(Description))-1)
2.) replace (replace (Description, char(10), ''), char(13), '') Description,

Please help.

Thanks in advance
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.

I know I can do this in .Net but would like to deploy my solution in MS/SQL 2008 R2 as the processing needs to run every minute (or some such) 24/7/365

I have to read a SOURCE .xml file from disk and create/write to a DESTINATION .xml on disk

SOURCE.xml                 should always be a single <package_creation>.....</package_creation>
DESTINATION.xml       should always be a single <PackagingList>.....</PackagingList>


SOURCE.xml

      <?xml version="1.0" encoding="UTF-8"?>
      <packaging_creation>
            <item_code>G1-000123-2</item_code>
            <packaging_code>0000014263</packaging_code>
            <quantity>2220</quantity>
            <location>Fuori</location>
      </packaging_creation>

DESTINATION.xml

      <?xml version="1.0" encoding="utf-8" ?>
      <PackagingList ImportVersion="1.0.1.1">
            <Packaging IdPackaging="0000014263" ComponentName="G1-000123-2" OriginalQuantity="2220"  />
      </PackagingList>

(indentation added for clarity)

Mapping of data (?nodes?) is

      SOURCE                        DESTINATION

      item_code            =>  ComponentName
      packaging_code  =>  IdPackaging
      quantity               =>  OriginalQuantity

A couple of days searching with "The Microsoft Search Engine" (ie: GOOGLE) has born little I can make sense of...  I have read the SOURCE into SQL using OPENROWSET but have been unable to expose the ?nodes? and their values.

I expected this to be fairly simple (I've been wrong before) but can't seem to get it wired up.

I would appreciate an example solution - at least for …
0
I am trying to print out a date with today's date.  The result i'm looking for is 9/18/2017 23:59:59.  Or it can be 2017-09-20 23:59:59.
DECLARE @sDate AS VARCHAR(25)
SET @sDate = CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59.999'
SELECT @sDate

DECLARE @Date AS DATETIME
SET @Date = CONVERT(DATETIME, @sDate, 102)
--SET @Date = @sDate
SELECT @Date

Open in new window

0
Hello,

I'm trying to convert single row records in SQL 2008 that look like this:

PrID      Type      Color       Shape      Qty      Store      OrderDt      
2299      Plate      Blue      Round      2      LA              06/02/16
3342      Vase      Clear      Long      4      NY              08/22/17

Into multiple rows records that look like this:
      
PrID      Attr              Value
2299      Type      Plate
2299      Colro      Blue
2299      Shape      Round
2299      Qty               2
2299      Store      LA
2299      OrderDt      06/02/16
3342      Type      Vase
3342      Color      Red
3342      Shape      Long      
3342      Qty               4
3342      Store      NY
3342      OrderDt      08/22/17

I don't want to use Union All because of efficiency.  

Thanks for your help!
0
How can I use a CASE statement to check if a field contains alpha characters?

Ex
CASE F1 CONTAINS [A], [B] ....
0
A table was designed to have some custom fields which are defined as nvarchar(max).  The problem is that the date being entered in that field are either entered as DD/MM/YYYYY or MM/DD/YYYY.

I need to select from the table but need the date in MM/DD/YYYY format.  

I tried using CONVERT(VARCHAR, date, 101) but it is still leaving values as DD/MM/YYYY.

I also tried to convert as date first before converting to the 101 code but receive an error - The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

How do I convert the date?
0
I have three SQL *.bak - backup files in a SQL backup folder, with the following dates, and I only want to delete one of  the oldest specific files which is the oldest.  

How can I configure the "SQL Maintenance Cleanup task : to do just this.

These are the files :

SQLsample1.bak   9/11/17
SQLsample2.bak   8/8/17
SQLsample3.bak   7/13/17 - This is the only file I want to delete because its the oldest.
0
I am using MS SQL and need to update a date in one table, with a date from another table with the same ID, and the newer date. For example....   I want my query to update ID 1 in TableONE with '2017-03-07 02:45:58.050' and ID 2 in TableONE with '2017-03-14 22:20:10.823'. Could someone assist? Thank you.

TableONE                                                           TableTWO
-------------------------                               --------------------------------
ID   RefineDate                                         ID        LogDate
1      2017-02-21 08:52:52.930               1          2017-03-02 14:26:20.987
2      2017-03-03 15:12:32.927               1          2017-03-07 02:45:58.050
                                                                   1          2017-03-03 15:28:21.887
                                                                   2          2017-03-09 11:57:28.137
                                                                   2          2017-03-14 22:20:10.823
0
Hi Experts,


I have a scandate Table.

On a current Query I have when a user selects a date it comes up with the Monday for that date:
Set @MONDAYDATE = DATEADD(wk, DATEDIFF(wk,0,@STARTDATE), 0)

Open in new window


But I also need when a user selects a StartDate I want the SP to return the first day of the month within the StartDate.

Example:
User selects 09/06/2017  I would like the SP to return 2017-09-01

Please help and thanks
0
How to write a simple grant permission for read, write everything in sql server 2008 to all users? I tried it but I got an error.

Grant Select on dbo.tmlemployee to all
0
Back Up Your Microsoft Windows Server®
LVL 4
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

I'm using sql server 2008.

on my table i have a column called TestRate which is a numberic(10,4) dataype.

So the records in the column look like this:

10.2345
14.0345
16.4405

So in my select query,  to query this column i'm doing something like this.


SELECT CONVERT(varchar,convert(numeric(10,4),MyTable1)) + ' rate per user'
FROM MyTable1


That query returns this:

10.2345 rate per user
14.0345 rate per user
16.4405 rate per user


How do i revise the query to mask the last 2 decimal digits with an X.

So i would want my result to look like this:

10.23XX rate per user
14.03XX rate per user
16.44XX rate per user
0
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
             DROP TABLE #Test

DECLARE @TextSearch NVARCHAR(1000) = 'India,,'
Select Value into #test  FROM dbo.udf_Split(@TextSearch, ',')

BEGIN
IF @TextSearch IS NOT NULL
    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C     
   
    )A
    INNER JOIN #test t on A.Name  like '%' + t.value + '%' and a.name <> ''

ELSE 

    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C 
   
    )A

END

Open in new window

0
I am looking for an automated way to do the following.


I need to create a script that could be ran on any version of sql 2008 and above that will do the following.

1)  create a mail profile.  Lets say we call the profile mycompany

2) Allow this mail profile to be used in a stored proc call that will need to execute send mail

The stored proc will live in the stored procs of the master database.
the call to the send mail of msdb database will come inside of this stored proc.


This stored proc will be called from a sql job.


I get into so many issues from one database to the next from permission ..etc.

I am looking for a solid setup of this to eliminate the many varables I run into.

I also am looking to automate this so it can be setup by other people with less experience on the issues that come up.


Thanks,
0
I ran an execution path.  Per the plan it states that the below statement cost 95%.  Anyway to improve on it?

SELECT * 
	INTO #ItemsResult1
	FROM #Items IR 
	WHERE IR.id_shipment IN (
		SELECT id_shipment FROM Shipment SI 
		WHERE SI.id_shipment = IR.id_shipment
	)
	OR IR.id_shipment = 0

Open in new window

Item Execution Result
0
Hi
I created an instance in sqlserver 2008 with x name.
I want to stop this service on specific date....
or take one of the databases under this instance off
is that possible?
0
I attempted to duplicate the issue I am experiencing.  I have 3 tables (Ship, Items, ItemsShip).  I am trying to resolve a logic issue and write a SQL that will get me the data that I'm looking for.

Item table relates to Ship table by "ShipID".  
ItemShip table relates to Item table by "ItemID" and Ship table by "ShipID".

The logic was written with .NET with regards to how the data is inserted.  I don't understand why ItemShip was created considering that Items table contains a "ShipID" field.

Regardless, I am getting data between Ship and Items.  However, I need to check to see if there is data in ItemsShipped which should be joined.

Should I just do something like

FROM Ship S
INNER JOIN Item I on I.ShipID = S.ShipID
INNER JOIN ItemShip IS ON IS.Item = I.Item
                                           AND IS.Ship = S.SHip
CREATE TABLE #SHIP(
		ShipID	INT,
		Name	VARCHAR(20)
)

CREATE TABLE #ITEM(
		ItemsID	INT,
		ShipID	INT,
		Name	VARCHAR(20)
)

CREATE TABLE #ITEMSSHIP(
		ItemsShipID	INT,
		ItemsID	INT,
		ShipID	INT
)

INSERT INTO #SHIP
(ShipID, Name)
VALUES
(1, 'Ship #1'),
(2, 'Ship #2'),
(3, 'Ship #3'),
(4, 'Ship #4'),
(5, 'Ship #5')

INSERT INTO #ITEM
(ItemsID, ShipID, Name)
VALUES
(1, 1, 'Item #1'),
(2, 4, 'Item #4'),
(3, 5, 'Item #5')

INSERT INTO #ITEMSSHIP
(ItemsShipID, ItemsID, ShipID)
VALUES
(1, 1, 1),
(2, 2, 4),
(3, 3, 5)

SELECT * FROM #SHIP
SELECT * FROM #ITEM
SELECT * FROM #ITEMSSHIP

DROP TABLE #ITEM
DROP TABLE #ITEMSSHIP
DROP TABLE #SHIP

Open in new window

0
I want to migrate SQL Server 2000 databases to SQL 2012.  I think I have to migrate to SQL 2008 first, so I manually migrated the database from SQL 2000 to 2008 and then to 2012, which is quite a tedious manual process.

If I migrate from SQL 2000 to 2008 then to 2012 now, and I have newer SQL 2000 database files (MDF/LDF and backup) later, is there a SQL script that can automatically migrate the newer databases then?  

How would I see if SQL 2000 stored procedures and functions are ok before / after upgrading?
0
I have a SQL statement.  The below script is a sample of what I'm experiencing.  I want @Temp to equal Zero if no records are returned.

CREATE TABLE #Test (
	CheckValue	INT
)

INSERT INTO #Test
(CheckValue)
VALUES
(123)

SELECT * FROM #Test

DECLARE @Temp as INT = (
	SELECT CheckValue
	FROM #Test
	WHERE CheckValue = 234
)

SELECT @Temp

IF @Temp = NULL 
	SET @Temp = 0

SELECT @Temp

DROP Table #Test

Open in new window

0
Hi all,

I have a ssis project with 3 ssis packages, one is a parent package which calls the other 2 packages based on some condition. In the parent package I have a foreach loop container which will read multiple .csv files from a location and based on the file name one of the two child packages will be executed and the data is uploaded into the tables present in MS SQL Server 2008. Since multiple files are read, if any of the file generates an error in the the child packages, I have to log the details of error (like the filename, error message, row number etc) in a custom database table, delete all the records that got uploaded in the table and read the next file and the package should not stop for the files which are valid and doesn't generate any error when they are read.

Say if a file has 100 rows and there is a problem at row number 50, then we need to log the error details in a table, delete rows 1 to 49 which got uploaded in the database table and the package to start executing the next file.

How can I achieve this in SSIS?

Thank you
Nagesh Kumar
0
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Thanks
Alexandre
0
hi,

I am having a few issues getting my SQL statement to work and I am unsure why as it looks ok to me?!?!

Here is the statement:

-- keep the usrprintlog table up to date

INSERT INTO usrUsersPrintLog (UserID)
Values (SELECT UserID FROM ZSD_RFUSERS where NOT EXISTS (Select ZSD_RFUSERS.UserID = usrUsersPrintLog.UserID))

So basically it should insert into 'usrUsersPrintLog' (just the UserID). any entries that are in 'ZSD_RFUSERS' that dont already exist in 'usrUsersPrintLog'.

I'm sure there is a clearer way for me to explain that!! haha

Here is the error:

error
0
Step : 1  - I am doing bulk  insert    -- This works

step 2 : I need to retrieve BillId


CREATE TABLE #T (IntCol int, XmlCol xml); 

INSERT INTO #T(XmlCol)  
SELECT * FROM OPENROWSET(  
   BULK 'c:\Testing\Test.xml',  
   SINGLE_BLOB) AS x;  
   
      select * from #t   

<filename>
<Transaction>
<Billid>100</BillId>
</Transaction>
<Transaction>
<Billid>200</BillId>
</Transaction>
</filename>

Open in new window

0
Is there a way to do this any more effeciantly?

--Setup Load Balancing for Mail servers
        DECLARE @FromName AS VARCHAR(300)
        DECLARE @SenderEmail AS VARCHAR(300)
        DECLARE @Replyto AS VARCHAR(300)
        DECLARE @ReplyName AS VARCHAR(300)
 
---- Create the variables for the random number generation

        SET @FromName = (SELECT FromName FROM dbo.MailServer WHERE MailServer = 'sendgrid')
        SET @SenderEmail = (SELECT SenderEmail FROM dbo.MailServer WHERE MailServer = 'sendgrid')
        SET @Replyto = (SELECT ReplyTo FROM dbo.MailServer WHERE MailServer = 'sendgrid')
        SET @ReplyName = (SELECT ReplyName FROM dbo.MailServer WHERE MailServer = 'sendgrid')
0
how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
I have date format 2017-08-17 10:04:44.697 in my sql server table. I want to select rows for particular date irrespective of time. What date formate i need to use in my where condition for performance point of view?
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.