Date: 03/11/05     Keywords: no keywords

I need to import some data each night. I'll need to do a few compares to decide what data does and doesnt get imported.

I'd like to do this with a stored procedure because that's what I feel will be the fastest way to do it but I feel guilty for not using DTS. It seems like thats the standard for this type of import but I'd get the job done faster if I used a stored procedure instead of creating that same routine using a GUI/DTS.

What do you think?

Source: http://www.livejournal.com/community/sqlserver/21019.html

Date: 02/23/05     Keywords: no keywords

edited.
found the problem. it's a documented bug.
thanks :)

Source: http://www.livejournal.com/community/sqlserver/18962.html

Date: 02/22/05     Keywords: database, asp, sql

I have this really obnoxious scenario where I need to take the table structure from one of my SQL Server 2000 databases, and copy all of the tables down to a new MS Access database. There's a LARGE NUMBER of tables, so recreating each table in Access manually should be a last resort.

I've looked into using the DTS wizard, but when Exporting Data, I cannot simply execute the "CREATE TABLE" statements without copying the data it seems? That, or if I modify the DTS package by hand afterwards, DTS sets up each table as a separate command stream, so I have to manually modify each to rip out the data transfer portion. This is extremely irksome.

Then I speculated on generating the "CREATE TABLE" T-SQL via SQL Server and running that against the Access db. Not being terribly familiar with Access, I'm unaware of any actual SQL front-end. But from my 'net application dev days, I have an old ASP page which I'd use on remote servers to modify client Access dbs that I otherwise didn't have access to. So thought of using that as my front-end, but then realized that the T-SQL generated code would NOT be Access friendly due to differing datatypes!

So I'm growing rather irritated with this situation and thought I'd ask folks out here for advice on this? Any ideas would be greatly appreciated!

Source: http://www.livejournal.com/community/sqlserver/18878.html

Date: 02/10/05     Keywords: php, html, sql, web

My boss called me over and asked me to write a T-SQL function on behalf of our web guy, that would arbitrarily strip out all HTML tags from a VARCHAR. In any other language like PHP, Perl, etc., I'd employ Regular Expressions to do this globally. However, T-SQL only really has PATINDEX and CHARINDEX, those aren't all that powerful.

I did wind up coming up with an iterative solution, which I thought I'd share with everyone to get suggestions and feedback.



CREATE TABLE #tmpText (
id INT IDENTITY(1, 1),
data varchar(4000)
)

INSERT INTO #tmpText (data)
VALUES ('this is some html')

INSERT #tmpText VALUES (
'


SOME HTML text after the body'
)

INSERT #tmpText VALUES (
'

Another HTML text after the body'
)

-- WHILE a '<' and '>' pair are present and the former's position is less than the latter's position value
-- Find a '<' then the next '>' positions, and run a stuff to remove whatever's in between!

BEGIN TRANSACTION
WHILE EXISTS (SELECT 1
FROM #tmpText
WHERE PATINDEX('%<%>%', data) > 0
)
BEGIN
UPDATE #tmpText
SET data = STUFF(data, PATINDEX('%<%>%', data), CHARINDEX('>', data) - PATINDEX('%<%>%', data) + 1, '')
WHERE PATINDEX('%<%>%', data) > 0
END

SELECT *
FROM #tmpText

ROLLBACK



I'm going to keep trying to dissect this in order to come up with a cleaner, non-iterative solution. If anyone else already has a function that does this in one fell-swoop, rather than via looping, PLEASE share it! :-)

UPDATE: I've made further code changes to handle a few odd circumstances and have finished the function. Thought I'd share it with everyone for a "peer review." Please comment away!


ALTER FUNCTION uf_stripHTML
(
   @strHTML varchar(8000),
   @flgFormat int = 1
)
RETURNS varchar(8000)
AS
BEGIN

--------------------------------------------------------------------------------------------------
-- Date Written: 02-10-2005
-- Purpose: Arbitrarily strip all text between all pairs of < > tags. This SHOULD be
-- HTML but theoretically could be other data?
----------------------------------------------------------------------------------------------------
-- Input Parameters: @strHTML = The string that we are stripping HTML from.
-- @flgFormat = If set to 1 (DEFAULT), then the function will attempt to
-- preserve basic formatting by detecting non-breaking spaces, start and end
-- paragraph tags, and break-return tags, and replacing them with their
-- ASCII equivalents.
----------------------------------------------------------------------------------------------------
-- Comments: This solution employs an iterative algorithm to repeatedly sweep through
-- the variable's text, removing HTML tags one at a time.
----------------------------------------------------------------------------------------------------

DECLARE @ltPosition int

-- If flgFormat is 1, then replace pre-determined list of tags and characters with
-- corresponding values!
IF @flgFormat = 1
BEGIN
   SET @strHTML = REPLACE(@strHTML, '

', CHAR(10) + CHAR(13))
   SET @strHTML = REPLACE(@strHTML, '

Source: http://www.livejournal.com/community/sqlserver/17939.html

Date: 02/09/05     Keywords: xml, sql

This is awesome! Do any of you know about/understand the new XML datatype in SQL Server? I'm at VSLIVE! in San Francisco at a session and I'm super excited about it. I didn't understand it before but this presenter is great and I now understand it. I'm going to post more once I have time but yeah..anyone else excited about this?

Source: http://www.livejournal.com/community/sqlserver/17891.html

Date: 02/07/05     Keywords: no keywords

OK, it's goal time at the office, the time where we pick 3 objectives we'd like to achieve over the next 3 months. I'd like one of those achievements to pass the MCDBA test# 70-229, but I'm not sure how much study time I'll need. I passed 70-228 with 92% of the questions correct. I took a Test King online test for 70-229 and I only got 56% correct, so I definitely have some more to learn. Has anyone on this list passed this test? How much study time would it be advised to devote to this?

Source: http://www.livejournal.com/community/sqlserver/17549.html

Date: 01/28/05     Keywords: no keywords

There must be a way to get this to work without more than 1 line of code..

UPDATE bannedIPs set offenseCount = offenseCount + convert(int,('select count(*) as offenseCount from iislog where ipaddr = ' + ipaddr))

I tried exec 'select..... not expecting it to work and of course it didn't.. any ideas?

Got it :) FYIL: UPDATE bannedIPs set offenseCount = offenseCount + convert(int,(select count(*) as offenseCount from iislog where ipaddr = bannedIPs.ipaddr))

Source: http://www.livejournal.com/community/sqlserver/17275.html

Date: 01/28/05     Keywords: sql

Does SQL Server log DROP TABLE like it does DELETE FROM? It doesnt' seem like it but I want to make sure.

Thanks!

Source: http://www.livejournal.com/community/sqlserver/16742.html

Date: 01/25/05     Keywords: database, sql

Hey, does anyone know how to set up a SQL Profiler Trace to log reads/writes to a specific database object? I tried reporting on object name, but so far all I can get to show up is Locks on ObjectName and the object name shows up blank. Any ideas?

Source: http://www.livejournal.com/community/sqlserver/16563.html

Date: 01/25/05     Keywords: microsoft

http://support.microsoft.com/?kbid=888799

Source: http://www.livejournal.com/community/sqlserver/16216.html

Date: 01/14/05     Keywords: software, asp, virus, microsoft

Here's a few things I learned yesterday that might be helpful for all. (After working through some frustrating bugs.)

1) Enable MS DTC in the Add/Remove Programs app in Control Panel ( it's under Windows Options --> Application Server --> Details. This service is no longer enabled by default in the Windows setup.
2) You have to set up a resource for the MS DTC service. ( Read about it here, in the transcript portion: http://support.microsoft.com/default.aspx?scid=kb;en-us;888121 )
3) UNINSTALL TERMINAL SERVICES ON ALL NODES!! ( Even if you don't think you installed it in the first place!! )
4) TURN OFF VIRUS SCAN SOFTWARE ( and any other unecessary services that may be running)
5) Make sure you set up a server alias via the Client Network Utility on the node from which you're doing the installation BEFORE launching setup app. Make sure your service account has rights to log in to both nodes.

Source: http://www.livejournal.com/community/sqlserver/15861.html

Date: 01/13/05     Keywords: sql

I'm getting some weird behavior in a function I wrote.

Private Function GetServerStatus() As Integer
Dim strDataSource As String
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intStatus As Integer
strDataSource = ConfigurationSettings.AppSettings("DSN")
myConnection = New SqlConnection(strDataSource)
myCommand = New SqlCommand("GetSystemStatus", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myConnection.Open()
intStatus = myCommand.ExecuteNonQuery()
myConnection.Close()
Return intStatus
End Function

It uses the stored procedure "GetSystemStatus":
CREATE PROCEDURE dbo.GetSystemStatus
AS
DECLARE @SystemStatus int
SELECT @SystemStatus=MAX(systemstatus)
FROM Config
RETURN @SystemStatus
GO

The weird behavior is that GetServerStatus() returns -1, but when I run GetSystemStatus in Query Analyzer, it returns 0 (or whatever the value is).

Source: http://www.livejournal.com/community/sqlserver/15376.html

Date: 01/10/05     Keywords: sql

hi every body
i want your help to just find out the best book for learning SQL
i am from india

Source: http://www.livejournal.com/community/sqlserver/15123.html

Date: 01/07/05     Keywords: no keywords

Sorry to keep bugging y'all.

I have inappropriate char(9)s popping up in various places in a table. Not a problem except that I'm exporting it to a space-delimited file, and they're messing up the formatting. So I'm trying to get rid of them with update queries. It's not working. Can anyone tell me why?

Here's an example of one of the update queries:

update elig set emembno = '' + right(emembno, 10) where substring(emembno,1,1) = char(9)

I run it, the char(9) is not replaced, it stays there.

Thanks.

Source: http://www.livejournal.com/community/sqlserver/14859.html

Date: 01/06/05     Keywords: sql

i've converted over 150 access queries today that didn't autoconvert during upsizing to sql because of sql language difference. These are the only 2 left i am stuck on. they are cross tab queries with a pivot.

Can you help?

[1] query1:

TRANSFORM Min(statusall_sub1.makesymb) AS MinOfmakesymb
SELECT statusall_sub1.site, statusall_sub1.opsoffice
FROM statusall_sub1
GROUP BY statusall_sub1.site, statusall_sub1.opsoffice
ORDER BY statusall_sub1.site, statusall_sub1.opsoffice, statusall_sub1.sectionid
PIVOT statusall_sub1.sectionid

[2] query2:

TRANSFORM First(sites.transferto) AS FirstOftransferto
SELECT sites.closuretype
FROM sites
WHERE (((sites.closuretype) Is Not Null))
GROUP BY sites.closuretype
PIVOT sites.site;

Source: http://www.livejournal.com/community/sqlserver/14733.html

Date: 01/05/05     Keywords: sql

i feel like a sql goddess today. seriously.

i had to convert a complicated multicondition access iif statement to a working sql statement -sql does not allow iif.  i've been working on this for days, when suddenly, just now, in the midst of exhaustion i discovered the answer..take a look..

before[access]:

IIf([keyms]=1,"-C") & IIf([pmp]=1,"-P") & IIf([regulatory]=1,"-R") & IIf([ipabs]=1,"-I") & IIf([perfobj]=1,"-PO") & " (" & [cdlevel] & ")" AS mstype
FROM milestones;

after [sql]:

CREATE VIEW dbo.milestonesq
AS
SELECT    milestones.*, CASE WHEN mscomplete IS NULL THEN 0 ELSE 1 END AS cmplt, replace(replace(cast(replace(keyms,'1','-C') as varchar)+ cast(replace(pmp,'1','-P') as varchar)+cast(replace(regulatory,'1','-R')as varchar)+cast(replace(ipabs,'1','-I')as varchar)+cast(replace(perfobj,'1','-PO')as varchar)+'('+cast(cdlevel as varchar)+')','0',''),'()','(0)') AS mstype
FROM        milestones

Source: http://www.livejournal.com/community/sqlserver/14438.html

Date: 01/04/05     Keywords: no keywords

Okay, I'm confused. I know the length of each field in a table. I'm conforming to it within the select statement (e.g., insert into tableA (columnA, columnB...) select left(column1A,10), left(column1B,10)... from tableB). So why am I getting the "string or binary data would be truncated" error? All columns in the table are character columns, so no integers running out of room or anything. The MDAC2.7 patch has been applied, so it's not the old bug.

Any ideas? Thanks.

Source: http://www.livejournal.com/community/sqlserver/13908.html

Date: 12/27/04     Keywords: software, sql, security

Does anyone know of a quick reference I could provide to the it security folks at my work that outlines what file extensions, ports, and dll's sql server uses? They've gone hog wild with 'security' software here to the point that they invariably end up shutting down one behavior or another within SQL each time they do a 'security upgrade'. Grrrr.

Source: http://www.livejournal.com/community/sqlserver/13319.html

Date: 12/23/04     Keywords: mysql, database, sql, postgresql

cross-posted to databases

For privacy protection within an application, I hash email addresses into MD5. MySQL, PostgreSQL, and Oracle all have a scalar function that allows you to hash a column/string -- for example MD5(emailaddr).

I have yet to find such a function, or a handy library/package for creating such a function in SQL Server 2000. Did I miss something? recommendations?

Source: http://www.livejournal.com/community/sqlserver/13063.html

Date: 12/22/04     Keywords: sql

Generic question, because you really don't want to read all the code. It's a lot of code. Really. A lot of code.

I've got a bunch of small pieces of T-SQL code in SQL Server 2000 that all go together to make one big procedure. (Actually, part of one big procedure-- it's not quite done yet.) When I run the first thirty parts together, they take about seventeen minutes, and I collectively think of them as part one. The second part takes five and a half minutes. The third part takes thirty seconds. The tables involved run from about 400k to one million records, so I'm not dealing with massive tables or anything. Everything's indexed well, etc. And when I run them separately, they all run quite quickly, and everyone's happy.

When I try to put them all together in one procedure, be it in a stored procedure, a Query Analyzer window, or a "SQL Command" DTS package step, they start running... and never stop. Well, they may stop at one point, but I stopped it running after twnty-five hours and forty-eight minutes.

Anyone seen anything like this before?

Source: http://www.livejournal.com/community/sqlserver/12893.html