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

I have dynamic sql that builds a select correctly

DECLARE @DIspo VARCHAR(MAX);
SET @DIspo = '
SELECT ''MAX(cte.'' + COLUMN_NAME + '') AS '' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''#table%'' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)';
PRINT @DIspo;

Open in new window


When I copy the Printed @Dispo into a query window I get this

SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

When I do run the select
I get three rows in a table

MAX(cte.DNC) AS DNC
MAX(cte.NI) AS NI
MAX(cte.DEALPUR) AS DEALPUR

What I need to do is in ONE process...
Get back a varchar string I can use elsewhere in a dynamic query process

MAX(cte.DNC) AS DNC, MAX(cte.NI) AS NI, MAX(cte.DEALPUR) AS DEALPUR
0
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

hi experts

can you share scripts for administration about block SQL SERVER 2016
0
SQL Server 2012 -

I have a challenging query... trying to best optimize it.   Would love some help!

I have two tables (called BEFORE and AFTER) with the same structure; two columns, a GROUP_ID and a VALUE.
I need to compare the VALUES for each GROUP_ID and if they are DIFFERENT, add the GROUP_ID to another table.

So given the BEFORE and AFTER tables below.
GROUP ID = 1 is different between the two tables because value 11 and 12 are not in AFTER
GROUP ID = 2 is different between the two tables because value 23 is not in BEFORE
GROUP ID = 3 is the same in both tables

Therefore, the RESULT table should contain  GROUP ID  with 1 and 2

BEFORE
Group ID     Value
    1                 10
    1                 11
    1                 12
    1                 13
    2                 21
    2                 22
    2                 24
    2                 25
    3                 30
    3                 31
    3                 32


AFTER
Group ID     Value
    1                 10
    1                 13
    2                 21
    2                 22
    2                 23
    2                 24
    2                 25
    3                 30
    3                 31
    3                 32

RESULT table
GROUP ID   (unique key, no duplicates)
     1
     2  


SO basically, it would have to be something like this...


INSERT INTO RESULTS (GROUP_ID)
select distinct GROUP_ID  
  from BEFORE
    ( join ) AFTER …
0
I recently received some excellent help from another Expert with a decimal manipulation that you see in this ticket.  Basically, I needed to control the decimal output to 2 digits or 3, and the Expert gave me great advice:   https://www.experts-exchange.com/questions/29114876/VARCHAR-to-DECIMAL-string-manipulation.html#a42663296

But, I am having a problem with the data in one of the subsequent manipulations which is in the code below.  I've input a lot of numbers into a temp table using the FORMAT in the above question, where the columns are VARCHAR(255), and the values in the column  are 1.00, .50, 5.49, .17, etc.  The problem is that I need to reference that character value numerically within the CASE below, and every attempt to do so fails with the error in my subject line.

I tried to correct it myself with this:  CONVERT(INT,CONVERT(DECIMAL(19,2),LPW))
But the end result was changed ---  .01 becomes 0, 2.30 becomes 2, etc.   So my attempt to convert the char value so that I can perform the CASE statement CHANGES the data, and thereby invalidates it.

Can somebody assist?

I can cast it as float, but then I lose my decimals -- 1.00 becomes 1, 0.50 becomes 0.5... etc.  I need a result that retains the data as I have stored it in the temp table.

    SELECT tmpTable.MATNR,
           tmpTable.itemNumber,
           CAST(tmpTable.WPL AS VARCHAR(20)) + ' oz./ft.',
           CASE
               WHEN tmpTable.LPW >= 2 THEN
                   CAST(tmpTable.LPW

Open in new window

0
This is concerning index rebuild's impact on transaction replication.
During an index rebuilds with full recovery mode, transaction log entries will be created (normal).  
If I switch to bulk logged recovery, will:
- less data be replicated ?
- less mining of the transaction log occur ?

What I'm seeing after an index maintenance where some large table indexes are rebuild, replication will report "Approximately 5000000 records have been scanned in pass #4, 0 of which were marked for replication".

This concerns me in that "real" data changes are being slowed down in getting to the subscriber.

Thank You
Bruno
0
Hi,
Any suggestion on how to make this  query look better.
Thanks in advance


ALTER PROCEDURE  [dbo].[sp123]

(
    
	 @DocIDType int = 0
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	declare @SQL varchar(500) = ''
	declare @GroupBy varchar(500) = ''
	
		 SET @SQL = 'SELECT DISTINCT ApprovalDocDisplay ' 

	IF @DocIdtype = 1 
		BEGIN


			
			SET @SQL = @SQL + '   FROM  [vvApp]    ' 
		    SET @SQL = @SQL + ' WHERE  Email = '+ '''' + ''  +''''	
		END


	IF @DocIdtype = 0
		BEGIN
			SET @SQL = @SQL + '   FROM  [vvApp]    ' 
		    SET @SQL = @SQL + ' WHERE  Email <> '+ '''' + ''  +''''	
		END
		  
	SET @SQL = @SQL  + ISNULL(@GroupBy,'')
      
	EXEC(@SQL)


END

Open in new window

0
Hello,

Need to have a new column depending on the text in an column.
Please find the attached example.

Thanks in advance.
example4.xlsx
0
Hello guys

I made this function to get the percentual from two value, but sometimes it works fine and sometimes it doesn't.

could you help me to ajust it?

Thanks a lot

alter Function  fn_PercentualDesconto (@Fullvalue float, @EndValue float)
  Returns Float
Begin 
	declare @v01 float;
	declare @v02 float;
	declare @v03 float;

        Set @v01=@Fullvalue 
	Set @v02=@EndValue 
	Set @v03=(@v01-@v02)
	Return 100- round((@v03 / @v01 ) * 100 , 2) 
End

Open in new window

0
In the sql below it is reading about 2000 records.
Is there any way to read those columns from left to right
Basically I want to choose column 1 if its > 0
If not then column 2
and so on

Just seems like I have a mess here



SELECT RollaPoll_IndividualID ,
            CASE WHEN [1] > 0 THEN [1]
                 WHEN [2] > 0 THEN [2]
                 WHEN [3] > 0 THEN [3]
                 WHEN [4] > 0 THEN [4]
                 WHEN [5] > 0 THEN [5]
                 WHEN [6] > 0 THEN [6]
                 WHEN [7] > 0 THEN [7] ELSE [8] END IndividualID
     FROM   #Results r

Open in new window

0
Hi,
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using  the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
0
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I need some help with an incredibly ugly DECIMAL string manipulation.  The datatype for all values used in these calculations is varchar.  (I know.  There's nothing I can do about it.)  I have two different groups of calculations -- I need one to always come back with 3 decimals, and I need the other to always come back with 2 decimals.  By itself, that is not too bad... I thought I could use something like this --   CONVERT(varchar(20), CAST(SUM(value) as MONEY),1)    ---  where CONVERT( varchar, m, 1 ) gives me the value into a string with commas, but it's just not coming together for me.

Again, the two groups of calculations below -- I need the top one to always return 3 decimals with ROUND, and I need the bottom one to always return 2 decimals --- whether the values are whole or not.

Is anyone able to help?



Always display 3 decimal points, whether it is a whole number or not -- ie., 200.000, .015 -- ROUNDing up for anything 4 and greater.  .0539 becomes .054
1.        CAST(ROUND((mmmm / oooo), 2) AS FLOAT)
2.        CAST(LWength AS VARCHAR(20)) + ' oz" x 1" piece'
3.        CAST(WPLength AS VARCHAR(20)) + ' oz/' + MinWidth   + '" x 1" piece'

For these, I just need to always display 2 decimals, whether the value is a whole number or not -- ie., 1.00, .50
1.        MinWidth + ' x ' + CAST(LPWWeight AS VARCHAR(20))  + '"/oz'
2.        CAST(LPWeight AS VARCHAR(20))+ ' sq in/dwt'
3.        MinWidth + ' x ' + CAST(LPWeight AS VARCHAR(20)) + '"/oz'
4.  …
0
Hi,
How do I change the column number in a serial way
example:
1,2,3
excluding which have a ref number :  0

example
Refno
1
2
3
0
5
0
6

The expected result:


Refno
1
2
3
0
4
0
5

Thanks in advance
0
What is the syntax to restore a SQL Server .mdf file from the Command Prompt?
0
Hi,

I am populating 4 dropdownlists with distinct values from columns from my DB table. I would like to ask is it more efficient to have for datsources for each dropdownlist or somehow create 1 datasource that contains the unique values from each column?

I have several other datasources so would like to limit how many are used on the page.

Manager	PMList	ClientList	ProductList
Name1	PM2	ClientA	ProductA
Name2	PM1	ClientA	ProductC
Name3	PM2	ClientA	ProductD
Name4	PM1	ClientA	ProductF
Name5	PM1	ClientA	ProductF
Name6	PM1	ClientA	ProductC
Name7	PM1	ClientB	ProductC
Name8	PM2	ClientB	ProductA
Name9	PM2	ClientB	ProductF
Name10	PM1	ClientB	ProductF
Name11	PM1	ClientB	ProductC

Open in new window


Thanks for any help.
0
I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ
0
what are the cautions we need take in SQL Server 2012 std( windows cluster 2012) and  Node1, Node2 are in cluster

as sql server 2012 sp4 patch upgrade we unable to install because of some installer files are missing, we tired to fix, but it is not working and  we are decided to go for SQL server Repair option on Node1

Pls let me know, what steps do i need to do..with a caution..on Node1

Pls note, once if it successful patched on sp4 on node2, after repair, then we will plan on node2.

Pls suggest. Thanks
0
HELLO...

We have a number of Tables, that need to be "migrated" from one database to another, in MSSQL-2012.  There are FK Dependencies on a good number of these tables.  For example:

PS_TKT_HIST_LIN                   (Table-# 1)
PS_TKT_HIST_DISC_COD       (Table-# 2)
PS_TKT_HIST                           (Table-# 3)
PS_TKT_HIST_LIN_CELL         (Table-# 4)
PS_TKT_HIST_LIN_CELL_EXT (Table-# 5)

...and so on.  In the example above, Table-# MUST exist FIRST...then...Table-# 2 MUST exist SECOND, then Table-#1, Table-#4, and Table-# 5.  I know we can use the built-in MSSQL EXPORT/IMPORT function...and that's great...but, because these need to be Exported/Imported IN SEQUENCE (due to the FK Dependencies), we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".

Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.  If we can get an example to provide a roadmap, that would be great.  And before you ask...the COMMAND LINE utilities are not an option...this MUST be done, within Management Studio :-)

Thank you in advance...Mark
0
I have a huge backup file the decomposed into 3 files test.bak1,Bak2 and bak3. How can I restore this DB in Standby mode and apply additional Tlog files?
0
An external provider has setup a load of SQL agent backup jobs on a 2008 database. Since this the MDF file seems to have grown from around 50GB to 120GB.

When i run a script to check the table usage, the total table usage is only 37GB. The bakups created by the agent were manually deleted from the drive as it had consumed 100% disk space.

Will anything have been left in the mdf file to cause it to grow so much and is there a procedure I can run to reduce its size.

Code I ran to check table size is as follows.

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

Open in new window

0
10 Tips to Protect Your Business from Ransomware
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
0
This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
0
Hello,
I am trying to create a testing. Database1 instance of a database with SQL Authentication which was previously set on Windows Authentication and  dbo.
I have a Audit trial set on the StoredProcedures .
 The Audit Trial table (Table_Debug) has credential susername.

Query used in the Stored Procedure is
Insert into Table_Debug(sql)
After execution of the above query the entry in the table is like:

Table_Debug

SQL                                                Username  
"Select * from table1"                 "Network\Username"

Now after setting as the new database instance as  testing. Database1

The above query gives result as

SQL                                                Username  
"Select * from table1"                 "testing.Database1"


Any suggestion on how to get the user name who is connected to the Database.
Any change I can do on the credentials of the table.

Cheers
0
Hi there. We currently have SQL Server 2008 R2 running on a single server/DC (small office setting), and we just purchased a new server box, and want to move everything over to it (including SQL Server). Unfortunately, the installation media with the licence on it is missing - is there a way to find the licence that the old server with SQL 2008 R2 is using? To clarify, SQL was installed separately, so should have its own licence. I looked through the registry, but the "Productcode" registry key didn't have the correct format (wasen't aaaa-bbbbb-ccccc-ddddd-eeee).
Thanks.
0
I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
0
I have select statement the produces 9 columns

What I need returned is for columns 2 through 9
The results of the first column where the integer value > 0

example... the columns would be
IndividualID       [1]         [2]         [3]  ... etc
95487512         32546      0         33365
95487513             0         33358  33365

The desired final select would be
IndividualID        OPCol
95487512            32546
95487513            33348
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.