Monday, September 23, 2013

[SQL Server 2008 issues] How to port no on Sql server

[SQL Server 2008 issues] How to port no on Sql server

How to port no on Sql server

Posted: 17 Sep 2013 07:59 PM PDT

Can any one know where to find port id in sql server.I am new to sql server dba ...please help.Thanks & Regardsshiva

How to get exact values for a constraint in SQL Server??

Posted: 22 Sep 2013 04:40 PM PDT

Is there a way to get the exact values for a constraint on a column? I need to display these values in a list of options to choose from in a webpage, but do not want to parse the expression as the result of the following query:select sys.check_constraints.definitionfrom sys.check_constraintsinner join sys.columns on sys.check_constraints.parent_object_id = sys.columns.object_idinner join sys.tables on sys.check_constraints.parent_object_id = sys.tables.object_idwhere sys.tables.name = 'myTable'and sys.columns.name = 'myColumn'and sys.columns.column_id = sys.check_constraints.parent_column_idParsing the expression will be error-prone and very cumbersome. There must be a way to get the exact values of '1D', '2D', '3D', '4D', for example when constraint is created using these values:alter table myTable add constraint CK_myColumn_Values check (myColumn in ('1D', '2D', '3D', '4D'))thanks for the help.

Reporting Services scheduling- hourly between 8 and 5 impossible?

Posted: 04 Jun 2012 05:14 AM PDT

Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something? If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?

SQL Server 2008 cluster node going down unexpectedly

Posted: 01 May 2013 04:17 AM PDT

Last night our primary SQL Server node went down and failed over to the secondary node.I was actually on the server at the moment having just launched a trace to troubleshoot a particular query when suddenly I lost all connectivity to SQL Server.Our setup is:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 2 Node Active/Passive Cluster.Here is what I found in the Administrative Log :[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation.We have SQL Server and SQL Server agent are running under designated network accounts.SQL Server Browser is running under a Local account.Never had that issue before in 2 years we've been using the server.The SQL Server error log did not reveal much. The very last event in the error log before the node went down is:2013-04-30 20:06:48.970 spid133 SQL Trace ID 2 was started by login "sa". Thank you for your help

Rolling 3 month average cost help

Posted: 22 Sep 2013 03:16 PM PDT

Hi guys,I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out for a couple days now, as well as get help, but to no avail.What I'm trying to do is replicate a 3 month average cost metric that I have in excel to a sql query, so that I can use it in SSRS as a data set. In excel I take the avergae of the sum of the cost and divide it by the count of members.I have two tables that derive the data. Below is example of my tables:Table name: AddmissionContract Admissiondate SumofCost 0606 200701 8639.380607 200702 22895.940608 200703 123752.28null 200704 61378.49Table name: MembersContract Admissiondate CountofMembers0606 200701 860607 200702 1020608 200703 90null 200704 120

Passing Type Table as parameters into Function

Posted: 22 Sep 2013 06:00 AM PDT

Hi All,I am having an experiment with passing tables as parameters into Functions. I have a table type set-up as followsCREATE TYPE MainProd AS TABLE( Year VARCHAR(4), ID 1 INT, ID 2 INT, hours INT)GOI have a function that uses the MainProd as a read only type.When I try to use the function I find I have to first declare a variable with this type, an exampleDECLARE @P MainProdThen call the function in SQL using the @P variable after inserting table values into @PSELECT * FROM [dbo].[MyBestProd] (@P) AS XCan I simply not write the SQL in the function parameter instead of having to use the @P parameter?I triedSELECT * FROM [dbo].[MyBestProd] ([my sql statement here]) AS Xbut it did not like it.ThanksEliza

Stored procedure invoking Elastic Search through HTTP Post. How to increase the 8K limit?

Posted: 22 Sep 2013 11:44 AM PDT

Hi,On one hand, I would like to use SQL Server database to store all the data and use this database for all type of editing.On the other hand, I would like to use Elastic Search to run queries.Therefore I need both "databases" to systematically be synchronized.Since both "systems" are independent from each other, in order to make sure that both are in sync, I thought of the following way of proceeding:I would use a stored procedure with 2 transactions:Transaction B would apply all necessary modifications to the data, stored in SQL server databaseTransaction A would contain Transaction B but also calls to Elastic Search via HTTP POST. If this HTTP Post returns 200 (OK), then Transaction A is committed.This would give something like:BEGIN TRANS A BEGIN TRANS B ... do everything linked to SQL Server database COMMIT TRANS B -- Invoke ElasticSearch through HTTP COMMIT TRANS AIn order to invoke Elastic Search via HTTP, from SQL Server Stored Procedure, I found the following piece of code:DECLARE @URI varchar(8000), @output_XML varchar(8000), @result int, @object int set @URI = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT' EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUTIF @result <> 0 BEGIN RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1) RETURN END EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @URI, false IF @result <>0 BEGIN RAISERROR('sp_OAMethod Open failed', 16,1) RETURN END EXEC @result = sp_OAMethod @object, SEND, NULL, '' IF @result <>0 BEGIN RAISERROR('sp_OAMethod SEND failed', 16,1) RETURN ENDEXEC @result = sp_OAGetProperty @object, 'responseText', @output_XML OUTPUT IF @result <>0 BEGIN RAISERROR('sp_OAGetProperty responseText failed', 16,1) RETURN END SELECT @output_XML StockQouteXMLEXEC @result = sp_OADestroy @object IF @result <>0 BEGIN RAISERROR('sp_OAGetProperty responseText failed', 16,1) RETURN END Everything looks fine EXCEPT that ServerXMLHTTP has a limit of 8K for the POST and I need to POST much more than 8K ...Does anyone have a solution to suggest?Many thanks in advance,

Stored Procedure calling other stored procedure

Posted: 22 Sep 2013 04:36 AM PDT

Hi All,Can someone help me on how to tackle this.I have a stored procedure on my server called usp_RPT_HSG_JobType. This procedure returns the job type code and name that I want to use in another procedure. I have this other procedure which I called usp_RPT_HSG_Repairs by declaring a table variable as follows:CREATE PROC usp_RPT_HSG_RepairsASBEGINDECLARE @Repairs TABLE Jobnumber INT , Jobtype nvarchar (20)INSERT INTO @RepairsSELECT r.jobnumber re.codeFROM repheader rINNER JOIN repairtype re ON re.code = r.code-- Output for detailsSELECT * FROM @RepairsWHERE Jobtype IN (How do I then call the other procedure usp_RPT_HSG_JobType here?) ENDThanks for answeringEO

How to Move Table with Non-Clustered Index?

Posted: 22 Sep 2013 12:48 AM PDT

I am moving tables with clustered and non-clustered indexes to a different filegroup. The clustered index moved the table and data as expected. I tried moving a non-clustered index through Management Studio thinking it would move the table but it didn't. I see the index on the new filegroup but the table/data is still on the primary. I also noticed there is now an empty new table created on the filegroup. Can someone explain how to move tables with non-clustered indexes?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog