SQL Server Performance

Installation of sql 2005 enterprise client components

sandeepsingh005 — Thu, 29 Jul 2010 11:16:45 GMT

HI,

I am upgrading sql 2000 sp3 enterprise to sql 2005. sql 2000 is default instance and I am installing sql 2005 as named instance. I am planning to move databases, logins and jobs later on to sql 2005. I need only databases engine upgrade only and workstation tools (management studio etc)

the sql 2005 installation is going through fine for the DB services but as soon as it starts installing the workstation tools it breaks and says "an unexpected error as occured. check the logs". the DB services are installed correctly. the setup logs dont suggest anything. I have also tried installed these 2 components separately but same error. restarted machine tried again but didnt help.

can anybody please help me on this.

How to get the table type definition

AJITH123 — Thu, 29 Jul 2010 09:20:06 GMT

Hi,

Can any body give the script to get the table type definition (sql script). What are the sys objects needs to joing to get the definition?

Thanks

Ajith

T-SQL - Need Help

sqlderby — Sat, 10 Jul 2010 14:25:27 GMT

Hi All

I have following data and require result set but facing problem...as my query showing 28 rows...

Table 1:

DT	FirstName	LastName	Extension	CADID
07/07/2010 11:48:57	Ason Asai	201216	31364	452395
07/07/2010 11:48:59	Ason Asai	201216	31364	452395
07/07/2010 13:57:54	Ason Asai	201216	31364	452395
07/07/2010 18:30:03	Ason Asai	201216	31364	452395
07/07/2010 14:37:22	Ason Asai	201216	31364	452395
07/07/2010 09:27:11	Ason Asai	201216	31364	452395
07/07/2010 10:20:46	Ason Asai	201216	31364	452395

Table 2:

CadID	Extension	AgentState	TimeStamp_PST
452395	31364	Ready to Talking	07/07/2010 11:48:32
452395	31364	Ready to Talking	07/07/2010 11:48:34
452395	31364	Ready to Talking	07/07/2010 13:57:29
452395	31364	Ready to Talking	07/07/2010 14:36:56

Result set should be....

DT	FirstName	LastName	Extension	CADID	AgentState	TimeStamp_PST
07/07/2010 11:48:57	Ason Asai	201216	31364	452395	Ready to Talking	07/07/2010 11:48:32
07/07/2010 11:48:59	Ason Asai	201216	31364	452395	Ready to Talking	07/07/2010 11:48:34
07/07/2010 13:57:54	Ason Asai	201216	31364	452395	Ready to Talking	07/07/2010 13:57:29
07/07/2010 18:30:03	Ason Asai	201216	31364	452395
07/07/2010 14:37:22	Ason Asai	201216	31364	452395	Ready to Talking	07/07/2010 14:36:56
07/07/2010 09:27:11	Ason Asai	201216	31364	452395
07/07/2010 10:20:46	Ason Asai	201216	31364	452395

Pls help ...Thanks

How to add a new article to existing publisher in transactional replication?

sql_er — Mon, 26 Jul 2010 22:09:45 GMT

Hi, We have transactional replication set up on our MSSQL 2000 servers. I am trying to add a new article to the publisher, but it does not seem to work correctly. Using the GUI interface, I added a new article. I then ran the Snapshot agent, which seems to have not done anything. I checked the publisher and see that i have 3 replication stored procedures created there. However, I do not have them in the subscriber. I do not want to reinitialize everything. What can I do in this case to simply add an article to an existing publisher? Thank you!

Log shipping Copy Job is Failing

RVG — Tue, 27 Jul 2010 16:03:25 GMT

Hi All,

I tried to implement log shipping between two servers and I was successful in implementing but at the secondary server I'm not able to copy jobs on the secondary server from primary server. Everything is in the network and I'm not using any switches for transferring log between two servers.

Bye,

Ravi

Analysis Services in SQL Server 2008

max_yogesh2002 — Tue, 27 Jul 2010 17:58:52 GMT

I want to retrive the Current running value of the Following Analysis Service using SQL Query.

1.Ad Hoc data mining

2.Anonymous connection

3.Linked Objects

4/User Defined Functions

Please help me

Thank You.

Overflow Error with Performance DashBoard

sonnysingh — Wed, 07 Jul 2010 20:17:23 GMT

I have following error came up when I click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further info (reports for the last 15 minutes).

"Difference of two datetime columns caused overflow at runtime".

I have seen the solution and implemented in usp_Main_GetSessionInfo SP but error still coming up....

"sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,"

Thanks in Advance

logon logout auditing

avipenina — Wed, 21 Jul 2010 10:37:44 GMT

Hi,
I need to audit after logon and logout for users.
i use this article http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/
but i need to adjust it for this scenario.
i need to capture the first logon for the user and the last logout for the user and not all logon and logout that the user do while he is in the ERP.
how can i adjust the script for that or do i need different approach?

THX

Views v Stored Procedures

Tricky — Tue, 24 Feb 2009 05:14:24 GMT

Hello,

We are in the process of familiarising ourselves with SQL 2008 having spent a number of years using 2000. My previous understanding was that amongst other considerations, you may choose a stored procedure over a view as stored procedures are more efficient, due to caching execution plans etc. Is this still the case in 2008?

Thanks in advance for your advice.

Applying Service Pack

danny123 — Mon, 26 Jul 2010 21:09:56 GMT

Hi there,

We are planning to apply Service Pack 2 for SQL Server 2005 servers. We have Active - Passive clustering.

Is there anything we need to be carefull about. I am concerned about switching the services to another node after applying SP to first node because there will be a difference between configuration of two nodes at that time. One will be running on SP 1 and other on SP 2.

Thanks in advance!

In reporting services within a procedure if you want to call a report and save that report to a file location is there an utility like dtexec?

lcerni — Tue, 20 Jul 2010 15:44:03 GMT

I have learned that to execute a SSIS package from a procedure you can use the following code: dtexec /SQL "\PackageName" /Decrypt "abcde"'

In reporting services within a procedure if you want to call a report and save that report to a file location is there an utility like dtexec?

How to find a frequently executed sql that causes serious blocking issues?

Jelly0228 — Fri, 23 Jul 2010 08:49:08 GMT

Recently there are serious blocking issues occured in one of our product databases, though I turned ON the 1222&3605 trace flags, there are no useful information written into the error log. But I could tell from the "Blk by" field of sp_who2 that there were many sessions blocked. The number of blocked sessions were too much to find the first spid in the blocking chain.

The problem disappeared without any efforts been made. My guess was: someone used one or more thread in the client application to process data, selecting a few rows from a table for example, after the blocking encountered, he or she stopped the thread secretly. The exection time for a single selection is very short, but the exection interval is too frequently, may 5~7 times per a second. If there are other sessions updating the same table, blocking occurs. Parts of my colleagues like such kind of ways to process data, which really make me have a headache.

I don't know why such blockings would be recorded in the error log by SQL Server. Does anyone have a suggestion that how to find the problem session?

Thanks.

.rss file

lcerni — Thu, 22 Jul 2010 16:06:03 GMT

Reporting Services is absolutely new to me. I have been requested to followed the steps in this blog "http://sqlblog.com/blogs/roman_rehak/archive/2009/06/12/ssrs-report-rendering-from-command-prompt.aspx" to export the data to an excel spreadsheet from one report.

This report (I guess this is the rdl file?) that I am using works due to a default parameter value that we have set @parameter. Basically the query behind this is SELECT * FROM TableName WHERE Parameter=@Parameter

Is it possible to create another report service script (.rss) to execute the same ABC report but with the additional functionality to pass in different parameter values for the @parameter and save the results to Excel.

For example, I want to run this rdl file with Query1 (SELECT * FROM TableName WHERE Parameter='X') and export it into an excel worksheet with Sheet1 labeled as X. Then I want to rerun this same rdl file with Query2 (SELECT * FROM TableName WHERE Parameter='Y') and export it into the same worksheet with Sheet2 labeled as Y.

This is the .rss file that I have created. Is it possible to add the @parameter to it? This looks like VB to me and I really don't know VB.

I think all I need is to alter the FileStream area but I have not been successful.

Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\temp\reports\Test.xls"
Dim reportPath as String = "/ReportTest/ABC"

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim extension as string = Nothing
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim streamIDs() as string = Nothing
Dim results() as Byte

rs.LoadReport(reportPath, historyID)

results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub

USER IS UNABLE TO REACH THE DATABASE WITH ERROR: 17189

shashank sharma — Wed, 21 Jul 2010 23:46:05 GMT

Hi all,

I know you all gonna direct me to to memory issue, with below error. But would like to know if there is any time beeing, fix for this issue, without restarting SQL Server.

Error:

Event Type:        Error
Event Source:        MSSQLSERVER
Event Category:        (4)
Event ID:        17189
Date:                7/21/2010
Time:                6:49:43 PM
User:                N/A
Computer:        XX XXX XXX XXXX
Description:
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 11.111.111.11]

SSIS on 2 nodes cluster

shabnyc — Wed, 23 Jun 2010 15:06:09 GMT

Hi All, I am new to cluster environment.

we have 2 nodes cluster active/passive, we have an SSIS package in the virtual node. the package should run and copy data from another server into databases in the cluster. the package always fails. my question is where should we execute the ssis from? Is it from virtual machine or from the active node. when I want to add objects to the database in the cluster, should I add it in the virtual machine or add to the active node.

your help is appreciated

Encryption error when creating creditial in sql 2005

contactjimmyus — Wed, 21 Jul 2010 04:03:13 GMT

I have to create a proxy account to run xp_cmdshell procedure in 2005 .I tried to create a credintial and it gives me decryption error .Is this was with service master key issue ? .I dont have the old copy of the service master key .Did recreating the master key will resolve the issue.In the server there is no database keys used .Only in msdb there exist ms_agentkey .....The data base was moved from an old server to a new machine 4 years back

Recreating the master key will resolve the error ? Please suggest the alternatives so that i can create the proxy account on the server.

@@version year

i.netdeveloper — Thu, 15 Jul 2010 19:01:51 GMT

Hi, I want to a script based on sql server version year, because some objects are not supported in 2000. We have two enviorments.

something like

if @@version = 'Microsoft Sqlserver 2000'

--do this

if @@version ='Microsoft Sqlserver 2008'

-- do this

I really appreciate your help.

Thanks...

How to save binary file from Sql Server database to the server hard disk using SQL

tarekj — Mon, 19 Jul 2010 07:43:46 GMT

Hi, I'm Using: SELECT * FROM OPENROWSET(BULK 'G:\1.jpg', SINGLE_BLOB) to save binary files from hard disk to sql server database Now I need an SQL code to retrieve a binary file from Sql Server database to hard disk. Also, is there a better method than using " OPENROWSET "?

Migrate log shipping standby server to new server

yhchan2005 — Mon, 19 Jul 2010 04:37:37 GMT

Hi, my company just bought a new server with high powerful spec. currently i am using 1 server which is doing log shipping standby server, would like to know the step to transfer/ move the database to the new server. My DB size now is around 400GB, backup and restore will take very long time, hope to get some simple way to do it. thanks.

What is the internal representation of all the datatypes.

baburk — Thu, 15 Jul 2010 07:04:33 GMT

Hi all,

What is the internal representation of all the datatypes.

Thanks,
Babu Kumaraasamy

@@version year

i.netdeveloper — Thu, 15 Jul 2010 19:04:06 GMT

Hi, I want to a script based on sql server version year, because some objects are not supported in 2000. We have two enviorments.

something like

if @@version = 'Microsoft Sqlserver 2000'

--do this

if @@version ='Microsoft Sqlserver 2008'

-- do this

I really appreciate your help.

Thanks...

How to monitor the memory usage through DBCC Perf

y.koteswarrao — Thu, 15 Jul 2010 07:35:25 GMT

Hi,

Is it possible to monitor the memory usage through DBCC Perf command..........

Koteswarrao

Login Locked Out Tab is disabled

y.koteswarrao — Wed, 14 Jul 2010 14:21:47 GMT

Hi,

Iam a sysadmin,i saw that Login properties->status->"Login is Locked Out tab is showing disabled always,unable to check or uncheck the box.........

Use SQL SERVER 2005 32bit

If any one know please let me know

koteswar rao

Primary Key required for "Junction table" ?

Tricky — Wed, 14 Jul 2010 05:56:25 GMT

I have a table which is purely used for creating many-to-many relationships, hence all fields are foreign keys of other tables. Should I still include a primary key for this table?

Disabling Snapshot Replication

lcerni — Tue, 13 Jul 2010 19:19:24 GMT

Today I set up Snapshot Replication to replicate some tables from production to development. Then another individual went to rename one of the tables and recreate the replicated table. Is there a way to disable Snapshot Replication (and not delete the whole process) so that the individual can rename the table to x_20100713 and then recreate the table x? Then I would enable Snapshot Replication.