sql-server

Running a simple VBA script to test a connection

I'm trying to test the connection of a GoDaddy SQL Server database. I'm getting an 'invalid connection string attribute.' What's wrong with this script? Dim cnn As ADODB.Connection Dim canConnect As Boolean Public Sub TestConnection() Set cnn = New ADODB.Connection cnn.Open "Provider=sqloledb;Data Source=GoDaddyServer.com...

SQL Server: how to optimize "like" queries?

I have a query that searches for clients using "like" with wildcard. For example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%')...

In SQL Server merge replication, how does reinitializing work?

I have set up a pull subscription to a merge publication in SQL Server. I use parameterized row filters on some tables. This works fine with the initial synchronization - just the rows using the filter arrive in the replicated (client) database. However, at some later point I'd like to be able to synchronize the replicated database agai...

How can I split a LINESTRING into two LINESTRINGs at a given point?

Hello, I'm trying to write a function that will split a LINESTRING into two LINESTRINGs given the split point. What I'm trying to achieve is a function that given a LINESTRING and a distance, it will return N LINESTRINGS for the original linestring splitted at multiples of that distance. This is what I have so far (I'm using SQL Server...

Retrieving varbinary output from a query in sql server into classic ASP

Hi, I'm trying to retrieve a varbinary output value from a query running on SQL Server 2005 into Classic ASP. The ASP execution just fails when it comes to part of code that is simply taking a varbinary output into a string. So I guess we gotta handle it some other way. Actually, I'm trying to set (sp_setapprole) and unset (sp_unsetapp...

SQL CASE Question

Hiya, I don't know if this can be done but I thought I'd ask. What I want to do is have a case statement query and if a 1 begin another action. if 0 don't do anything. For Example select CASE WHEN client.deathofdeath = yes THEN 1 do another select in here (which is another table) Else 0 End AS DeathDate From Client client...

Committing Transaction

Hi http://msdn.microsoft.com/en-us/library/ms189797.aspx In this link they are committing a transaction within catch clause IF (XACT_STATE()) = 1, I don't get it, if there is an error why they are committing it? even if the problem in select statement and there is no big deal committing it, why don't just roll it back. Thanks ...

Stored procedure with output parameters vs. table-valued function?

Which approach is better to use if I need a member (sp or func) returning 2 parameters: CREATE PROCEDURE Test @in INT, @outID INT OUT, @amount DECIMAL OUT AS BEGIN ... END or CREATE FUNCTION Test ( @in INT ) RETURNS @ret TABLE (outID INT, amount DECIMAL) AS BEGIN ... END What are pros and cons of each approach con...

How to export more than 1MB in XML format using sqlcmd and without an input file?

Hello, In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a "for xml path.." clause at the end. I read on BOL that if I don't want my output truncated when reaching 1MB file size, I have to use this :XML ON command, but it s...

Which is faster join

Which is faster SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique or SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID ...

How can I return a substring of a LINESTRING in SQL Server 2008 Spatial?

Say I have a LINESTRING defined as LINESTRING(-122.360 47.656, -122.343 47.656, -122.310 47.690, -122.310 47.670, -122.300 47.630) And I want to get a substring from POINT(-122.360 47.656) to POINT(-122.310 47.690) How can I return a substring of a LINESTRING in SQL Server 2008 Spatial? ...

Does MySQL have an equivalent of SQL Server "indexed views"?

Does MySQL have an equalavent to SQL Servers "indexed view" functionality? http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query What I'm specifically looking for is a way for MySQL to create a "view" that will return results faster than simply performing the underline view's query/sql. ...

SQL Query - group by more than one column, but distinct

I have a bidding table, as follows: SellID INT FOREIGN KEY REFERENCES SellItem(SellID), CusID INT FOREIGN KEY REFERENCES Customer(CusID), Amount FLOAT NOT NULL, BidTime DATETIME DEFAULT getdate() Now in my website I need to show the user the current bids; only the highest bid but without repeating the same user. SELECT CusID, ...

Building Stored Procedure to group data into ranges with roughly equal results in each bucket

I am trying to build one procedure to take a large amount of data and create 5 range buckets to display the data. the buckets ranges will have to be set according to the results. Here is my existing SP GO /****** Object: StoredProcedure [dbo].[sp_GetRangeCounts] Script Date: 03/28/2010 19:50:45 ******/ SET ANSI_NULLS ON GO SET QUO...

Reporting Services: Subscription PDF generated Dundas Chart looks different from Preview Chart

Situation: Running a report on SQL Server Reporting Services When I do a Preview of the Report all my charts look fine. No problems. When I run a subscription for the report, the chart look different. Like completely different legends are gone etc. Any ideas what the cause would be? BTW the charts are Dundas Charts. ...

ETL mechanisms for MySQL to SQL Server over WAN

I’m looking for some feedback on mechanisms to batch data from MySQL Community Server 5.1.32 with an external host down to an internal SQL Server 05 Enterprise machine over VPN. The external box accumulates data throughout business hours (about 100Mb per day), which then needs to be transferred internationally across a WAN connection (qu...

display image on a report based on two date

Hi all I am using sql server 2005 reporting service to generate report base on a database. There are two columns which are datetime type ColumnA and ColumnB. The report would display a KPI image on this report by comparing these two columns.Below is the expression for selecting image SWITCH(DateDiff("d",Fields!ColumnA.Value,Fields!Colu...

SQL Server and Identity column

Hi, I just noticed that if I have an identity column in a table, when I insert new rows SQL Server 2008 is automatically filling up the sequence if there are discontinuity. I mean, if in my identity column I have 1,2,5,6 if I insert other two rows in the table the system puts automatically 3,7 in the identity column. Do you know how t...

Retrieving the an SQL Agent job's specific error

I am using msdb..sp_help_job to access whether a job succeeded or failed and can retrieve a general error. But, I want to access the specific error for the step that failed. I cannot seem to find it. It is not in this list of helpful stored procedures provided by MS http://msdn.microsoft.com/en-us/library/ms187763%28v=SQL.100%29.aspx ...

Does the speed of the query depend on the number of rows in the table?

Let's say I have this query: select * from table1 r where r.x = 5 Does the speed of this query depend on the number of rows that are present in table1? ...