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,
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
I have an emergency need to compare object definition across a few servers -- procedures, tables and views.   I've got all object definition loaded into a bunch of different tables, but I have one problem.
 
 The production objects are from a 'PROD' server, and there are explicit references to 'PROD'.
 The uat objects are from a 'UAT' server, and there are explicit references to 'UAT'.

When I compare he object definition, I want to exclude the server name references, such that I am only comparing the rest of the procedure definition.  I've written the piece below, thinking my REPLACE would do it --- but it does not appear to be.  I say that because I looked at one of the variances it returned, and I do not see any differences aside from the server names.

A third party tool is not an option.  I need to compare the objects across two servers ASAP, and identify which ones are different.  It is SQL Server v2014.


SELECT a.server_name,a.schema_name +'.'+ a.name [procedure],a.definition [prod],b.definition [uat]
FROM dbo.Procedures a JOIN dbo.Procedures b
  ON a.schema_name +'.'+ a.name = b.schema_name +'.'+ b.name  
  AND a.type = b.type
WHERE a.type = 'P'
AND REPLACE(a.definition,'PROD','') <> REPLACE(b.definition,'UAT','')
AND a.server_name = 'PROD'
AND b.server_name = 'UAT'
ORDER BY server_name,[procedure];

Open in new window

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

I’m trying to filter a drop down list based on another dropdown list using SQL/vbscript/hta. I have it working sort of thanks to Experts Exchange where I can populate the dropdowns from the database.

Now, I need to do two things:

1. Filter the dropdown lists
2. Send the values to a text file

The query in the code below shows the data like this:

ARA_PERSON_ID               ARA_TYPE_DESC                     INVOICE_TERM_DESC
0000000                                Student - Credit                       2004 Fall Reporting Term
0000000                                Student - Credit                       2004 Spring Reporting Term
0000000                                Student - Credit                       2005 Intersession Reporting
0000000                                Student - Credit                       2009 Fall Reporting
0000000                                Student - Credit                       2012 Fall Reporting
0000000                                Student - Credit                       2013 Fall Reporting
0000000                                Student - Credit                       2013 Spring Reporting
0000000                                Student - Credit                       2016 Spring Reporting
0000000                                Student - Credit Free               2003 Fall Reporting Term
0000000                                Student - Credit Free               2004 Spring Reporting Term
0000000                                Student - Credit Free               2006 Fall Reporting Term


 But I need it to show like this:
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
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
I have a sql script that writes the selection into xml format. I am trying to write a batch file that will output this data into an xml file. Using :XML ON I am receiving the following error in the output file:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
HResult 0x80004005, Level 16, State 1
No description provided

my .bat file code:
sqlcmd -i "ProductHierarchy-Dept-Cat-Subcat.sql" -S "SC-L-WINSQL-001" -d "HQ_L" -o "output.xml" -y0

If I turn :XML OFF it prints out the entire data selection into one line.

SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

--drop tables if exist
If(OBJECT_ID('tempdb..#importType') Is Not Null)
Begin
    Drop Table #importType
End

--select data
If(OBJECT_ID('tempdb..#AMSprocuctHierarchy') Is Not Null)
Begin
    Drop Table #AMSprocuctHierarchy
End

select 
	distinct DIM.pos_dept as Department, 
	SD.DESCRIPTION as DeptDesc,
	--left(DIM.category,4) as Category, 
	--right(rtrim(DIM.category),4) as Subcategory,  
	DIM.upc as UPC, DIM.description as ItemDesc, 
	substring(DIM.CATEGORY, 1, 4) as Category, 
	substring(DIM.CATEGORY, 5, 8) as SubCategory, 
	CAT.DESCRIPTION as CategoryDesc,
	subcat.DESCRIPTION as SubcatDesc
		into #AMSprocuctHierarchy
			from DHAT_ITEM_MASTER DIM
			join store_department SD on DIM.pos_dept = SD.STORE_POS_DEPARTMENT
			join CATEGORY subcat
			   on subcat.CATEGORY = DIM.CATEGORY
			join CATEGORY cat
			   on 

Open in new window

0
Upgrading an old e-commerce website from SQL 2000 to SQL 2008 (a necessary intermediary step) and finally to SQL 2016 a query on a search page has significantly slowed down.  There is one search criteria text object for the users to enter their search data.  Multiple tables/columns are searched based on the search criteria.  If I eliminate the final OR statement in the where clause the query takes 1-2 seconds.  Leaving the final OR statement adds 20 - 30 seconds.  Any suggestions on how to speed this up.  I have checked the SQL 2000 tables and there are no indexes, so I'm not sure why this worked faster in SQL 2000.  

Final OR statement mentioned:
OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq    
      where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions    
      and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
      and chq.WebOrderColumnName like 'ShowName') LIKE '%12281%')


SELECT  top 15 o.KeyWebOrder AS id,
convert(varchar(256),KeyWebOrder) AS KeyWebOrder,
OrderNumber AS OrderNumber,
DateCreated AS DateCreated,
convert(varchar(256),keyWebStatus) AS keyWebStatus,
LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
      and chq.WebOrderColumnName like 'BillingFirstName')) )        …
0
VMware vCenter 5.5 web login  coming Empty inventory Error reflecting is : "Could not connect to one or more vCenter Server systems: https://xxx.xxx.xxx.xxx:443/sdk"

VC 5.5 is running on windows 2008 and database is Sql2008  running on other server [both are vm running on esxi same host]
Telnet is Fine
DB Test is Fine
Services are fine
But Noting visible under vc inventory.
Did Server reboot and issue got fixed but repeated again after 4 days.

Pls help to locate if issue is with Network/DB/SSL or VC itself


Not able to locate much detail to apply appropriate fix
-2618423BBF69F68A.jpg
-351E50B996600DC9.jpg
-218B3348C6B2659B.jpg
-7057366E756B0F4D.jpg
0
I have a text field in a table and I want to read them. I did this but I still see a value like this
4sIAAAAAAAEAE2PT0sDMRDFv0qZ8x52W//U3LZKRcQt2OJFPAz

 r1.png
0
Hi Guys,

select hashbytes('SHA2_256','123'+'456'+'1') -- Hashbytes will always give UNIQUE value for n number of rows.

Just wanted to know, if we convert the HASHBYTES function to BIGINT like:-

select convert(bigint, hashbytes('SHA2_256','123'+'456'+'1')) will this conversion will always give UNIQUE values if their are million or billions of rows?

Please help.
0
Good morning,

I am having a problem running SSIS package in SQL 2016. I can create and run it in separate Import/Export Data utilities, but when I execute it in SQL itself I am getting an error message "Parser NULL". I also can not run the Import/Export wizard withing the SQL database engine. I am getting an error: "The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered."

Please advise. THanks
0
Hello ,

I am being part of a project based on Citrix Migration 6.5 to 7.15 LTSR.
We have planned to migrate SQL 2008 r2 to 2012 sql.
we have been working on 2016 dev environment.

please provide me the scripts for the above two cases.

Thanks,
Shirish
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.