How to call webservice from TSQL? (SQL SERVER 2000)

I want to call a webservice from TSQL in SQL Server 2000. I tried with the following code: Declare @Object as Int; Declare @ResponseText as Varchar(8000); Exec sp_OACreate ‘MSXML2.XMLHTTP’, @Object OUT; Exec sp_OAMethod @Object, ‘open’, NULL, ‘get’,’http://server/ws/service1.asmx/Test’, ‘false’; Exec sp_OAMethod @Object, ‘send’; Exec sp_OAMethod @Object, ‘responseText’, @ResponseText OUTPUT; Select @ResponseText Resultado; Exec sp_OADestroy @Object; For […]

Splitting text in SQL Server stored procedure

I’m working with a database, where one of the fields I extract is something like: 1-117 3-134 3-133 Each of these number sets represents a different set of data in another table. Taking 1-117 as an example, 1 = equipment ID, and 117 = equipment settings. I have another table from which I need to […]

SQL Server / T-SQL need to modify dynamic stored procedure?

I have a stored procedure that dynamically generates MERGE TSQL statements to handle SCD Type 1 updates between the given Dimension / Staging table dump. Here is the T-SQL code (modified from the original code by Alex Whittles): USE [OPPY_DWUSD] GO ALTER PROCEDURE [dbo].[GenerateMerge] /*************************************************************** * * * Script for use with blog post * […]

Using ADO .NET convert datatime from sql Server to DateTime in VB .NET

I have created a Web Service in Visual Studio 2005, using VB .NET language. This Web Service interacts with a database on Sql Server through ADO .NET. Using ADO .NET I get one table with this code conn = New SqlConnection(Utilities.ConnectionString) sqlcmd = New SqlClient.SqlCommand() sqlcmd.Connection = conn sqlcmd.CommandType = CommandType.Text sqlcmd.CommandText = “select * […]

Select Company Names with more than X Orders

The Order table has the list of orders as well as a Customer ID for every order. And different orders can have the same customer ID , meaning a company has put in multiple orders (Given that Customer ID is the ID for the company). Company Name comes from the Customer Table and my goal […]

Update using XML in SQL Server

I am trying to update a table using an xml. UPDATE dbo.M_Picture SET Sequence = T.c.query(‘Sequence’) FROM dbo.M_Picture pic INNER JOIN @xml.nodes(‘/pictures/picture’) T(c) ON pic.PictureId = T.c.query(‘pictureId’) –I guess issue is in this line The XML I am using is <pictures> <picture> <pictureId>30</pictureId> <Sequence>4</Sequence> </picture> <picture> <pictureId>31</pictureId> <Sequence>4</Sequence> </picture> </pictures> The error message I am […]

Selecting Change_Tracking_Current_Version() in SQL Server 2008 table trigger

I am using SQL Server 2008 and Change tracking is enabled on my Database. On one of my tables in the database, I have created the following Trigger. CREATE TRIGGER [dbo].[tr_student] ON [dbo].[Student] FOR UPDATE,DELETE AS BEGIN SET NOCOUNT ON; SELECT CHANGE_TRACKING_CURRENT_VERSION() END The purpose of this Trigger is – when I update or delete […]

SQL Server stored procedure no statements run when from RPC

I have a SQL Server 2000 database with a stored procedure that deletes a row from a specific table, given its id. When I call the stored procedure from VB.NET, it does not delete the row, but running the same script directly on the database via SSMS, it works. Here’s my chain of events: Start […]

family tree sql query

I am working on Family tree application in with sql server 2008. My main table is like this:- ID int PK Name Varchar(50) MotherID int FatherID int ID MotherID FatherID Name 1 NULL NULL My Grand Father 2 NULL NULL My Grand Mother 3 someid someid My Mother 4 2 1 My Father 5 […]

Count records that span multiple date range

ACCOUNT Amount DATE 1 50 01-2010 1 100 03-2010 1 100 02-2011 2 100 01-2011 2 50 05-2011 2 50 09-2011 3 100 03-2012 3 100 03-2013 Is there a query structure that will allow me to count distinct accounts that has spanned current and past year? For example, account 1 has amounts in 2011 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.