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

hello Guys,

I have a Query that results me 130.000 rows, I need to save all the rows into a txt file.
Today I use my system to do that, row by row and it takes me almost 3 horas processing.
Is there any possibility to have all the rows into a text field, thus I could save all the information
into a file in seconds.

Regards
Alexandre
0
I demoted a 2008r2 domain controller.  Now I cannot log into SQL server.  SQL was installed on this DC because at the time because this was there only server.  Made 2 Manage software runs on this server and the database for this software is installed on SQL.  I didn't realize that this would break SQL.  Anyone know any fix for this?  I won't be back to work until Tuesday to address this, but I an trying to research this now.  Hopefully I will have a fix for this by Tuesday to get our M2M software backup and running.
0
I'm trying to export a View as a Flat text File. The view has 6 months of transactions and executes in 5 seconds.
I created an export data DTS package to export this data to text and it is taking an age, the data file is stored on the same server.

Anyone know why this is taking so long?
0
Here is some sample data for a sql server 2014 table


CREATE TABLE #License(
       [customer_id] [int] NULL,
       [license_key1] [nchar](10) NULL,
       [license_state1] [nchar](10) NULL,
       [license_key2] [nchar](10) NULL,
       [license_state2] [nchar](10) NULL,
       [license_key3] [nchar](10) NULL,
       [license_state3] [nchar](10) NULL
 ) ON [PRIMARY]

 INSERT INTO #License
            ([customer_id]
            ,[license_key1]
            ,[license_state1]
            ,[license_key2]
            ,[license_state2]
            ,[license_key3]
            ,[license_state3])
      VALUES
            (11111111
            ,12345    
            ,'IL'
            ,34567    
            ,'IN'
          ,12345    
            ,'IL')

 INSERT INTO #License
            ([customer_id]
            ,[license_key1]
            ,[license_state1]
            ,[license_key2]
            ,[license_state2]
            ,[license_key3]
            ,[license_state3])
      VALUES
            (22222222
            ,66666    
            ,'MI'
            ,999999    
            ,'NY'
          ,87654    
            ,'CO')

 INSERT INTO #License
            ([customer_id]
            ,[license_key1]
            ,[license_state1]
            ,[license_key2]
            ,[license_state2]
            ,[license_key3]
            ,[license_state3])
      VALUES
            (33333333
            ,908763    
            …
0
Hi
What connection string would I use to access my local sql database on my computer. I remember using LocalDB etc
Thanks
0
I have the following stored proc....I am passing @FileData into the from clause....its of type xml ...but the dynamic sql is nvarchar.

how do I format this so that it will use the variable @FileData.

The sql runs fine in a test..only when called as stored proc  I get
Must declare the scalar variable "@fileData".

Here is the stored proc so far.

USE [SharePoint2007Archive]
GO
/****** Object:  StoredProcedure [dbo].[uspCreateAttachments]    Script Date: 5/9/2019 8:49:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspCreateAttachments]
@ReqID nvarchar(50),
@TableName varchar(MAX),
@xQuery VARCHAR(100),
@fileData XML
AS


BEGIN
Declare @Insert nvarchar(max);

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;

set @Insert = 'INSERT INTO sharePoint2007Archive.dbo.Attachments (RequestID,Form, Document)
Select '   + QUOTENAME(@ReqID,'''') + ' as RequestID, ' + + QUOTENAME(@TableName,'''') + ' as Form, 
 xData.Col.value(''*:Document[1]'',''nvarchar(max)'') Document
from @fileData.nodes(''' + @xQuery + ''') AS xData(Col)
where DATALENGTH(xData.Col.value(''*:Document[1]'',''nvarChar(MAX)'')) > 0;'
print @Insert;

execute(@Insert);

print 'Inserted New Request ' + @ReqID + 'for Form ' +  @Tablename


END -- uspCreateAttachments

Open in new window

0
This is SQL 2012. I'll go step by step and show what I need done. I'm thinking I need a loop but also somehow to see the next row

1. Sample data
  Create Table #test
(
 Device VARCHAR(250),
 CashCredited Money,
 PlayType VarChar(50),
 DateAndTime DateTime,
 [Transaction ID] BIGINT,
 accunulated money)

--inserts

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX',-2.50,'Debit','2019-02-05 11:12:21.920',1211793,0 --last column needs to be -2.50


INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', 0,'Credit','2019-02-05 11:12:45.000',1211794,0

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', -1,'Debit','2019-02-05 11:30:26.383',1211795,0 -- last column needs to be -3.50

  INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'FSS',0 ,'Posting','2019-02-05 11:30:27.383',8888,0 --second column needs to be 3.50

   INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'MX',0,'credit','2019-02-05 11:31:01.000',1211796,0

    INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 

Open in new window

0
We plan to move from MS SQL 2008 SP4 Enterprise edition to
MS SQL 2016 standard edition :

a) besides exporting out the data/tables & import into the Standard
    Edition, what are the things to watch out for to verify the viability
    to migrate

b) what's the differences that Enterprise offers that Standard doesnt?

c) any other migration checklist items, compatibility & points to note?
0
Dreamweaver 2019 CC
Win 10, 64 Bit, all patches current
Microsoft SQL Server 2008 r2 Express
SSMS installed using window for credentials
Apache installed

Installed is a software I sell and support for the Point of Sale industry.
Use localhost via Chrome to log into a "Back Office" usually used for reports for the end user / customer. I / we also use it for adding items ect.
1. Fully functioning installation.
2. Successfully login via Chrome "localhost" using default user name and password
##
Goal:
Use Dreamweaver to edit webpages display - padding fonts and adjust some of the tabular displays.

Problem:
No matter the page I chose - in Live Mode (NOT LIVE PREVIEW) displays the login page.

Live preview did ask for login - but then displays the page. I'd really prefer to not work in this manner - but if this is the only solution.

Kindly help me out. Thank you.
R/
Vincent.

See my settings below.

Dreamweaver-settings.png
LocalTesting.png
BasicConnection.png
AdvancedConnection.png
ServerChoices.png
0
I am upgrading my SQL server from 2008 r2 to 2012 then to 2016. We have hundreds of Legacy DTS packages that I need to convert to SSIS. In the mean time I was wondering if anyone knows of software or a tool to run Legacy DTS packages in server 2012 or later edition

Thank you
0
Hi,

I am using the Coalesce function to combine records into a comma variable (I'm using SQL 2012) which is then used in another query for the IN query, this is the stored proc.

	@BldID INT
AS

BEGIN

	SET NOCOUNT ON;

DECLARE @listStr VARCHAR(MAX)

-- List all current approvers into a comma value for the IN command
SELECT @listStr = COALESCE(@listStr+',' ,'') + LoginID
FROM tbl_Approvers
WHERE BldID = @BldID

-- Set a dummy value if the returned list is empty
IF (@listStr IS NULL)
   SET @listStr = '-1' 

-- Get available users 
SELECT * FROM [tbl_login] WHERE ([BldID] = @BldID AND UserRole = 'Admin') AND NOT (Id IN (@listStr))

Open in new window


However, this works when only one row in the DB but fails if there are several rows with the following message.

Msg 245, Level 16, State 1, Procedure spTPListApproversAvailable, Line 23
Conversion failed when converting the varchar value '1016,' to data type int.

Open in new window


Any help is appreciated.
0
I want to declare a list:

DECLARE @myList     nvarchar(max)  = '1,2,3~4,5,6';


Do cross appply (or similar) to get:

col 1, col 2, col 3
1         2        3
4         5        6

How would you do that?

/BK
0
Hi all,
I'm trying to build a graphs in ssrs report , when I test to receives this report I'm curious why  I can view all  namesgraphs_ssrs_email.PNG which only ssrs present me 3 names  ( managersemail ( those marked as dark ) where the actual  values I should be able to have 100 of them here.

This is how the report looks like in ssrs report builder graphs_ssrs.PNG
0
SQL Server Maintenance

I have a SQL Server that houses a small db for about 20 users. I am by no means a DBA. I am currently using Datto to do image level backups every hour. The server has 2 Drives that are mirror running server 2008 and SQL 2008 and 8 GB of Ram. I would like to know what other tasks i should be doing to make sure the DB is healthy. Also i would like to know if there is a way to monitor its performance to make sure its running at its best.
0
IN SSRS, we have a AD new security group that has users with browser permissions. However when members in this group try to run reports we get this error..

The permissions granted to user 'DOMAIN\testit' are insufficient for performing this operation. (rsAccessDenied)

If we add these same members of the AD group individually with browser permissions, it works fine.  What are we missing on this AD security group?

SSRS is running on SQL2008
0
I'm using sql server 2008.

I have a table that looks like this:

TestTable1.JPG
This is the script to create the table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestOrders1](
	[OrderID] [int] NOT NULL,
	[LastName1] [varchar](50) NULL,
	[LastName2] [varchar](50) NULL,
	[LastName3] [varchar](50) NULL,
	[LastName4] [varchar](50) NULL,
	[LastName5] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (1, N'Smith', N'', N'Johnson', N'', N'')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (2, N'Rogers', N'', N'', N'Taylor', N'Williams')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (3, N'Smithers', N'Phillips', N'', N'', N'Lee')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (4, N'', N'Jackson', N'', N'', N'Chen')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (5, N'Rainier', N'', N'Grisham', N'Jacobs', N'Bly')

Open in new window


I would like to create a column called TestLastnames which is is all the lastname columns seperated by a comma.

My desired result would look like like this column colored in blue:

TestTable1Desired.JPG
What's the best syntax to create this column?
0
Good morning team,

 I have an error (The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.) when trying to sum hours and minutes (duration column) from a table where the column data type is nvarchar(50). I need to aggregate the hours+minutes so I can join on another table to get total work hours for installers out in the field. The select query below will work for specific CustomerName however, I receive the error attached when running for all customers. I've tried at least 10 different ways to resolve but the error is always the same.

As always, thank you all for your time and expertise in helping me get this resolved!

SELECT     TOP (100) PERCENT CustomerName, CONVERT(varchar(10), SUM(DATEDIFF(minute, 0, Duration)) / 60) + '.' + CONVERT(varchar(10), SUM(DATEDIFF(minute, 0,
                      Duration)) % 60) AS install_hours_worked
FROM         dbo.vWIP_Install_TimeSheets AS T
GROUP BY CustomerName


Here is the source query for the view above. This is simply to refine to a list of time sheet entries that meet certain service item criteria (in this case, we're only looking for a WIP report on installation jobs).

SELECT     TOP (100) PERCENT CustomerName, CustomerId, Duration
FROM         dbo.TimeTracking
WHERE     (CustomerName IN
                          (SELECT     Job
                            FROM          dbo.vWIP_Install)) AND (ServiceItemName IN
                          (SELECT     …
0
SELECT        COUNT(Receipt_Detail.Receipt_Number) AS totalcountreceipt, SUM(Receipt_Detail.Amount_Applied) AS receiptamount, Invoice_Header.Invoice_Number,?
FROM            Invoice_Detail INNER JOIN
                         Invoice_Header ON Invoice_Detail.Invoice_Number = Invoice_Header.Invoice_Number INNER JOIN
                         Member_Association ON Invoice_Header.Member_Number = Member_Association.Member_Number INNER JOIN
                         Member ON Invoice_Header.Member_Number = Member.Member_Number INNER JOIN
                         Receipt_Detail ON Invoice_Detail.Invoice_Number = Receipt_Detail.Invoice_Number INNER JOIN
                         Receipt_Header ON Member_Association.Member_Number = Receipt_Header.Member_Number AND Receipt_Detail.Receipt_Number = Receipt_Header.Receipt_Number
WHERE        (Invoice_Detail.Charge_Code = 'D' OR
                         Invoice_Detail.Charge_Code = 'T' OR
                         Invoice_Detail.Charge_Code = 'N')  (Invoice_Header.Invoice_Date BETWEEN '20181201' AND '20190131') AND (Receipt_Header.Deposit_Date BETWEEN 
                         '20181201' AND '20190131')  and (Invoice_Header.Invoice_Amount > 0)
GROUP BY Invoice_Header.Invoice_Number
ORDER BY totalcountreceipt DESC

Open in new window

Inside of "?" if I want to add like below, is it possible? Please show me how.

select top 1 (Receipt_Detail.Amount_Applied) where Receipt_Header.Deposit_Date BETWEEN '20181201' AND '20190131' order by Receipt_Header.Deposit_Date asc
0
rrrplease take a look of the attached file. It is a simple invoice to receipt (1 to m) relationship. and my question is.

1. assume I give you receipt received to date range 01/01/2019 to 01/14/2019. how can get the following in SQL query.

Invoice ID Total Receipt Count Receipt Amount
1                 25.00              1
2                 0                      0
3                 0                      0
0
Hello,

I have a SQL Replication problem. I added some tables in an existing replication and I don't know what tables they are. Is there any way to see the history of tables I have added? (SQL 2008 R2)

I hope u can help me!
JN
0
Hello,
In current Production environment hosted on Microsoft SQL Server 2008, we have expensive sql query that is killing the database. We wish to add additional index to existing table.
SQL Query. This database is currently monitored by Solar wind and it has recommended to add index to the existing table. This table has currently 20 million records.

SELECT PVT_CHG_LOG_SEQ,  
   "Table_Name",  
   PK_Column_Name,  
   PK_Value,  
   ADP_SYNC_REQ_FLAG,  
   SYNC_STATUS,  
   SIBL_SYNC_STATUS,  
   ADP_SYNC_STATUS,  
   PRIORITY,  
   RETRY_COUNT,  
   SIBL_RESPONSEDESC,  
   ADP_RESPONSEDESC,  
   SIBL_COMPL_TIME,  
   ADP_COMPL_TIME,  
   Created_Date,  
   9,1  
FROM dbo.INT_PVT_DATA_CHANGE_LOG  
WITH  
   (
      UPDLOCK,  
      NOWAIT
   )  
WHERE (SYNC_STATUS = @P0)  
ORDER BY 9,1 ASC  

Recommendation from Solar wind to add inded
Clustered Index Scan (OBJECT:([CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[PK__INT_PVT___61283AF276F68FE1]), WHERE:(CONVERT_IMPLICIT(nchar(1),[CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[SYNC_STATUS],0)=[@P0]))

Question: a) Do we need to create new clustered or non clustered index for column SYNC_STATUS  to the table INT_PVT_DATA_CHANGE_LOG or modify existing index PK__INT_PVT___61283AF276F68FE1 to incorpate the column SYNC_STATUS

b) Any other guidelines to be followed while adding index

Regards
Tom
0
In my SSIS for each loop...
It is grabbing each file in the folder and running the processes
Then moving the file just completed into an archive folder
My problem is that it is running the processes nN times depending on number of files remaining

So... if I start with 8 files...
It inserts the first file 8 times and runs the processes that many times
Moves that file
And then does the next file 7 times
And so in

Any ideas?

SSIS
For Each Loop Properties
0
I’m using sql server.

I have a table that has a lastname,firstname and middleinitial columns.
They are these datatypes:

[LastName] VARCHAR(30)
[FirstName] VARCHAR(20)
[MidInit] VARCHAR(1)

So when I run this query

SELECT [LastName],[FirstName],[MidInit]
FROM [Emplyoees]

I get this:

LastName FirstName MidInit
Simpson    Homer       J
Simpson    Lisa            M
Bunny        Bugs
Bonney      William      H

I would like to create a column called FullName that displays the data in this format LastName, FirstName MidInit

So then the new column would like like this:

FullName
Simpson, Homer J
Simpson, Lisa M
Bunny, Bugs
Bonney, William H

What’s the beat syntax to create this column?

Notice how bugs bunny has no MidInit.
When employee has no MidInit don’t place any character in the string for the middleinit.
1
creating Indexes to boost query performance

Please provide me some examples where indexes and clustered indexes would help to boost performance of a database query.

Thanks
0
I could really use some of your expertize with this query.

I need to turn this Select query to an Update.

Need to  Update table Itemsite and field CycleId for every Invtid returned by this select statement:

declare @today char(10)
declare @month char(2)
declare @day char(2)
declare @year char(4)

set @month=DATEPART(month, GETDATE())
set @day=DATEPART(day, GETDATE())
set @year=DATEPART(year, GETDATE())
set @today=rtrim(@month)+'/'+rtrim(@day)+'/'+rtrim(@year)



SELECT top (5)percent
b.INVTID,
a.cycleid,
a.LastCountDate,
COUNT(b.INVTID) AS TICKETS

from itemsite a,soshipline b,soshipheader c
where c.CpnyID = b.CpnyID and c.ShipperID = b.ShipperID
and b.invtid=a.invtid and c.SiteID=a.siteid and
            c.CpnyID ='0001' AND
            c.CustID LIKE '%' AND
            c.CustID <> 'AAA1000' AND
            c.Status ='C' AND
            c.SiteID LIKE 'WEST' AND
            DATEDIFF(day, c.OrdDate, @today)<=280 and
            c.sotypeid<>'tr' AND
            c.cancelled<>'1' and
            left(b.InvtID,3) in ('g/h','WHI','FRI')

      GROUP BY b.INVTID,a.lastcost,a.LastCountDate,a.CycleID
      ORDER BY a.LastCountDate asc,TICKETS desc,b.INVTID
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.