Quick and simple row_number solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;

SELECT PK, Terminal, MarketDate = CAST(d.MarketDate AS DATETIME)

,d.Prod, d.Cost

INTO #TestTable

FROM (

SELECT 1, 1, '2014-12-01 15:04:00.000','A', 2.23...

peter.cox (12/11/2014)

---

Sorry! Been ages.

It was just a business requirement to search a free form field. Obviously with that being the case, people have the ability to put the data in...

Try this for a size

😎

declare @storedprocedure_name nvarchar(30) = 'my_proc_name';

SELECT N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) +

N'IF EXISTS (SELECT * FROM sysobjects...

Has the appeal of Cusano Rojo:rolleyes:,but it works

😎

Alan.B (12/10/2014)

---

I'm curious (general question to everyone)...

Wouldn't this be faster?

-- the function

CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1...

djj (12/10/2014)

---

Thanks Eirikur. just one question, what does "& 0x7FFF" do? I realize that the & is for "bitwise and", however I am not very good at...

I know it´s not perfect, the dynamic is just to contain the whole thing in a single batch, took it from an old code of mine which took the table...

Good catch, never tested the functions for a null value:pinch:, here is a revised version

😎

/* Eirikur's modification of Jeff's Modification of Erikur's Function */

GO

IF OBJECT_ID('dbo.DigitsOnlyEE') IS NOT NULL DROP FUNCTION...

Quick solution using a calculated column

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_ROWNUM') IS NOT NULL DROP TABLE dbo.TBL_ROWNUM;

IF OBJECT_ID('dbo.UDF_COUNT_ROWS') IS NOT NULL DROP FUNCTION dbo.UDF_COUNT_ROWS;

DECLARE @COUNT_FUNC NVARCHAR(MAX) = N'

CREATE FUNCTION dbo.UDF_COUNT_ROWS

(

...

Quick cross-tab solution (hard coded)

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#BATCH') IS NOT NULL DROP TABLE #BATCH;

CREATE TABLE #BATCH

(

batch_Id VARCHAR(36)...

Quick thought, while the SSH will work it is not a perfect solution, relatively slow, increased attack surface etc.. Why not use ODBC connection from the UX? Or schedule a...

Quick solution using the nodes method

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @MyXML XML

SET @MyXML = '<RS>

<R id="6330">

<WF id="71445">

<WFS id="12790"> <fname>John12790</fname> </WFS>

<WFS id="12791"> <fname>Eric12791</fname> </WFS>

</WF>

</R>

<R id="6331">

<WF id="71446">

<WFS id="12793"> <fname>John12793</fname> </WFS>

<WFS id="12794"> <fname>Eric12794</fname> </WFS>

</WF>

</R>

</RS>';

SELECT...

Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box 😀

😎

Koen Verbeeck (12/8/2014)

---

New type of spam:

http://www.sqlservercentral.com/Forums/Topic1636920-391-1.aspx#bm1642200

They enter a reply with the first reply of the thread copied into it (in this case, my reply) and then add a URL.

More annoying...

Good points Steve and thank you for this editorial, brought back some nice memories! Back in the "old" days I used to make a living mountaineering and climbing, first rule...