Date: 07/24/07     Keywords: sql, web, microsoft

Two questions in one week. Sheesh. I apologize in advance.

As someone on the MS forums said, assigning values to variables in SSIS is way harder than it needs to be. I've read about fifty webpages on this, and they all boil down to the same two basic solutions, and both give me the same errors. I've tried this two different ways:

Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
	Public Sub Main()
        '
        Dim vars As Variables
        Dts.VariableDispenser.LockOneForWrite("intTYPeriodFrom", vars)
        vars(0).Value = CInt(Dts.Variables("intThisWeekSysno").Value) - 51
        vars.Unlock()
        Dts.TaskResult = Dts.Results.Success
    End Sub

Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
	Public Sub Main()
        WriteVariable("intTYPeriodFrom", CInt(Dts.Variables("intThisWeekSysno").Value) - 51)
        Dts.TaskResult = Dts.Results.Success
    End Sub

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Integer)
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForWrite(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Source: http://community.livejournal.com/sqlserver/61240.html

Date: 07/23/07     Keywords: database, sql

Another dumb question. At least I give you guys the easy ones, right? SQL2K5, latest SP, etc etc.

I'm trying to pull everything to the right of a comma in a cell. To spare me having to upload data, I've recreated it in a quick procedure using a variable to store the location of one of the cells in the table:


DECLARE @Author CHAR(50)
SET @Author = 'Aarseth, Espen '
PRINT LEN(@Author)
PRINT CHARINDEX(',',@Author)+1
PRINT (LEN(@Author)-(CHARINDEX(',',@Author)+1))
PRINT 'right(' + ltrim(rtrim(@Author)) + ', ' + CAST(LEN(@Author)-(CHARINDEX(',',@Author)+1) AS char(2)) + ') = '
PRINT right(@Author, (LEN(@Author)-(CHARINDEX(',',@Author)+1)))


My problem seems to stem from SQL treating the LEN() and RIGHT() commands differently, as far as I can tell-- LEN(@Author) returns 16, which is the actual length of the data in the field, but when I use RIGHT(), the padding is included, and consequently I get a field full of spaces. I've attempted every combination of LTRIM(), RTRIM() and @Author I can think of, and I still end up with a field full of spaces as a result set. When I declare @Author as char(16), I get the proper results, but in the table itself, there's no way to do that (multiple author names are, for obvious reasons, variable in length) short of using a cursor and going row by row, and I'd rather not do that (I'm not fond of cursors in the first place, and this is a relatively big table for a scan-and-modify routine, so I may run into time concerns).

I vaguely recall there being some variation on the SPLIT() command in T-SQL, but the sieve that is my brain has forgotten what it's called, and searching on "split" in books online is coming up with squadoosh. Anyone know what it is I'm forgetting? (And whether it's possible to use the command to update two to four colums in the database at once?)

Thanks.

Source: http://community.livejournal.com/sqlserver/60987.html

Date: 07/09/07     Keywords: browser, sql

Okay, this should be simple...

I'm trying to find the cube browser in SQL Server 2005. I cannot for the life of me do so. Can anyone point me to it? Thanks.

Source: http://community.livejournal.com/sqlserver/60894.html

Date: 06/20/07     Keywords: sql, web, microsoft

SSIS.

Can anyone please explain to me what the above error means and how to correct it? Microsoft's error reference is its usual helpful self, and it seems no one has gotten this error (or asked about it anywhere on the web) in my particular situation. Before someone suggests it, no, I cannot permanently change the type of output.

I'm trying to take the restuls of a stored procedure and export them to Excel in a data flow task. This is a common thing at my workplace, and we have hundreds of them that work correctly. Every once in a while, though, one of them starts dying, and it always throws the error in the subject line, invariably preceded by a DTS_E_OLEDBERROR. The going wisdom for people using OLEDB or SQL SERVER destinations is that it actually is what it says it is-- there's a problem with a transformation. So I figured I'd give that a shot with the Excel Destination. I switched it over to Fast Load (in order to be able to get to the MaxBatchSize parameter, to set it to 1-- I don't usually use it), dropped all the columns but one, and tried to execute the task with that single mapping, of a four-byte signed integer. I redirected the error output to a text file, but it's a four-byte signed integer, how can anything go wrong?

Here's a piece of the text file that comes out.

,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,1,,,,,,,,,,,,-1071607685,-1
,,,,,,2,,,,,,,,,,,,-1071607685,-1
,,,,,,2,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1

Obviously, the problem is not with four bytes being too small to hold the integers that are failing out on me. The brilliant -1071607685 error code translates to "no status is available." Which tells me less than nothing.

I have deleted and recreated the destination spreadhseet to make sure there's no corruption in a template or anything. I've tried this single-mapping approach with every column (Unicode string, four-byte signed int, and currency), and, of course, with all columns. I've read every page I can find on the internet about DTS_E_OLEDBERROR, DTS_E_INDUCEDTRANSFORMFAILUREONERROR, and -1071607685, and tried every applicable suggestion. It works wonderfully when I send the output to a flat file destination, but as I said previously, that is not an option that's available to me in production, only in testing-- I must use the Excel Destination for this client.

I don't even know where to begin looking at this point. I've exhausted all my possibilities. Anyone have any other ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/60479.html

Date: 06/13/07     Keywords: no keywords

Hi guys!

wts24h  community is starting a series of competitions!
Here is the first competition!

"Open Letter to Bill Gates".

The best short story will be awarded 100$ in PayPal.
The winner will be chosen by the greatest number of votes.
You can post your short stories here for 7 days,
and then voting starts:-)


pic from Freaking News.com

Source: http://community.livejournal.com/sqlserver/60295.html

Date: 06/08/07     Keywords: microsoft

I am now fully aware, after two days of trying to figure this out, that Microsoft has not manufactured a Jet driver for 64-bit SSIS, and has no plans to do so.

Has anyone come out with a third-party Jet driver that's available now?

Source: http://community.livejournal.com/sqlserver/60055.html

Date: 06/03/07     Keywords: programming, database, sql

I'm trying to update my resume with the sql server tasks I've been doing for the last 2+ yrs, but I'm really stuck on what exactly to put and how to word it.

Quick background: I've heavily programmed in and used MS Access since 1994. In many cases, I did the db design, creation, and upgrades for all tables, queries, forms, and reports. I often created the app that other people use. It took me a while to find a job where I could learn and use SQL Server. I finally found that about 2.5 years ago (there's a lot of Access programming using a tool already created at my company, with a SQL Server backend).

Could really use some input!


I'm really not performing SQL Server DBA work; the majority of the tasks I tend to perform are:
- writing and running simple select queries

- writing and running complex queries (several inner and/or left joins) to select, append (insert into), update, or create a new table from a data set using particular criteria

- processing/manipulating large data sets (1 million + records) to drill down to a smaller set that can be handled by the Access app

- incorporating CASE statements into the queries

- importing a table into SQL Server from text files, spreadsheets, or Access tables, using the Import/Export tool (a task I do only very occasionally)

Any suggestions on how I can word this in the resume?

Oh, and I can tell you as an experienced Access programmer (who started from v2, where you had to really understand Access and database concepts and normalization, no cute little wizards to help) that SQL Server really ROCKS and I prefer it. Now, to be able to use it more...

Source: http://community.livejournal.com/sqlserver/59901.html

Date: 05/31/07     Keywords: database, sql

Okay, this one's got me stumped...

I'm converting SQL Server 2000 DTS packages to SQL Server 2005. It seems like what I'm trying to do should be simple: An Execute SQL Task runs a stored procedure, then stores the output in an Object called ResultSet. (The Execute SQL task isn't working, but that's beside the point right now.) Now, what I need to do is export the result set into Excel. Simple, right? Take the result set and export into Excel using a data flow.

Except, umm, there doesn't seem to be any source in the data flow screen that specifies a variable. I realize the easiest way to do this would be to store the results of the stored procedure in an actual table and just export the table to Excel, but I'm not entirely sure I have create permission on the database.

Anyone tried to do this? Is there something painfully obvious that I'm overlooking? Thanks.

Source: http://community.livejournal.com/sqlserver/59141.html

Date: 05/17/07     Keywords: sql

SQL Server 2005 Express edition. I installed the advanced tools and the toolkit, but when I try to run Business Intelligence Development Studio, devenv.exe is not found. I did a full hard drive search and confirmed that it is not, in fact, there. I tried to reinstall the toolkit, but it says the entire toolkit is already installed on the drive. Obviously, this is not the case...

any idea how I can reinstall that portion without having to uninstall the entire toolkit? (And if I have to do that, what do I look for in add/remove programs, since there is no entry for SQL Server 2005 toolkit?)

Thanks.

Source: http://community.livejournal.com/sqlserver/59058.html

Date: 05/01/07     Keywords: sql

Can anyone recommend a good SQL Course (classroom w/ instructor) for a Production (a.k.a. Physical ) DBA to take? I'm mainly interested in migration from 2000, clustering, and maintaining a SQL 2005 environment. I'm already very knowledgeable in SQL 2000, so this would just be a class to "upgrade" my skillset. Anyone out there have any luck with some classes like this ?

Source: http://community.livejournal.com/sqlserver/58696.html

Date: 04/20/07     Keywords: sql

My thirst for SQL knowledge continues.

Four tables: ASSIGNMENT, PROFESSION, SPECIALIZATIONS, ASSIGNMENT_TO_SPECIALIZATION

I want to retrieve all assignments and their associated profession and specializations. While each assignment has only 1 profession, it can have multiple specializations. The m-to-m table, ASSIGNMENT_TO_SPECIALIZATION, handles this possibility.

How can I query this so that I get unique assignments returned? Since there is a possibility for multiple specializations wouldn't that return the same assignment multiple times? Would a GROUP BY do it in this case?

Source: http://community.livejournal.com/sqlserver/58413.html

Date: 04/19/07     Keywords: no keywords

I have a query that connects 4 tables.

hiree, nurse_flag_list, nurse_flag_type, facility

HIREE
id
name

FACILITY
id
facility

NURSE_FLAG_TYPE
id
flag_color

NURSE_FLAG_LIST
hiree_id (FK to HIREE)
nurse_flag_lookup_id (FK to NURSE_FLAG_TYPE)
facility_id (FK to FACILITY)

A hiree can work at multiple facilities, so a person may have multiple records in NURSE_FLAG_LIST. Would an INNER JOIN against the facility table be correct in this statement?

SELECT h.id, h.first_name, h.last_name, nf.*, f.name AS facility, nft.name AS flagcolor
FROM hiree INNER JOIN nurse_flag_list nfl ON nfl.hiree_id = h.id
INNER JOIN nurse_flag_type nft ON nft.nurse_flag_lookup_id = nfl.id
INNER JOIN facility f ON f.id = nf.facility_id

Source: http://community.livejournal.com/sqlserver/58119.html

Date: 04/19/07     Keywords: sql

What is the maximum number of nested sub queries that can be used in SQL?



Thanks in advance.

Source: http://community.livejournal.com/sqlserver/57917.html

Date: 04/18/07     Keywords: sql

I have a multiple-table query that will involve either a left or right join, but am not sure how to approach this.

table PERSON is primary table, with PK ID
table SKILL is primary table, with PK SID
table PERSON_SKILL is foreign m-to-m table, with FK PID = PERSON.PID and FK SID = SKILL.ID
table PERSON_JOBS is foreign table, with FK PID = PERSON.ID, 1 to 1

I would need to query all these at once. How accurate is this?

SELECT p.* pj.job, ps.skill
FROM PERSON p
INNER JOIN PERSON_SKILL ps on ps.pid = p.id
INNER JOIN SKILL s ON s.id = ps.sid
INNER JOIN PERSON_JOBS pj ON j.pid = p.id

Source: http://community.livejournal.com/sqlserver/57623.html

Date: 04/05/07     Keywords: asp, sql, web

I'm building a web application that will use Reporting Services as a reporting tool.

The question I have is this: I have a report that shows projects by various categories (which are parameters to the report). How do I set the Reporting Services parameters without creating my own drop-lists on the aspx page? In the Report Viewer control, I can enter the option "ReportViewer1.ShowParameterPrompts = true;" but I then run the code and change a parameter, it just posts back and doesn't change the parameter's value (like it does from the report server). Am I missing something, or will I have to code drop-boxes for the parameters?

Thanks for any help you can give.

Cross-posted to sqlserver and csharp

Source: http://community.livejournal.com/sqlserver/57483.html

Date: 03/22/07     Keywords: sql, web

SQL2K.

I'm trying to call a DTS package from a stored procedure. I get the following error:

Error string: ActiveX Scripting was not able to initialize the script execution engine.

The package runs correctly when I run it straight from Enterprise Manager.
I have reregistered the DLLs suggested in the solution at sqldts.com and get the same error.
I've pulled out all the variables and distilled the command to:

EXEC master..xp_cmdshell 'DTSRun /S "EDISON" /U "sa" /P "--------" /N "VariablePassTest"

...and I still get the error.

(The package at present contains nothing but an activex script task whose only line of substance is a messagebox displaying the value of the passed variable.)

Anyone know of any alternative reasons why this error would appear? The Intarwebz are being somewhat unhelpful, as everything seems to point back to re-registering the DLLs. TIA.

Source: http://community.livejournal.com/sqlserver/57176.html

Date: 03/21/07     Keywords: asp, sql

aspnet_regsql cannot connect to the server (error 26). I have setup TCP and Pipes, also allowed server exe in windows firewall. I have SQL EXPRESS 2005, it works good with asp .net. Does anybody know?

Source: http://community.livejournal.com/sqlserver/56916.html

Date: 03/20/07     Keywords: xml, web

Dear friends!
We are glad to you to inform on opening new, absolutely free-of-charge site about online XML-validator and web-based editor.

For what to pay huge money for different win-based editors or to put the unchecked cracked copies?
Use our site, and you should not install anything on computer.
Our site does not contain advertising, is quickly loaded,consumes not enough traffic and quickly processes your data.
At your service XML visualisation not only in the form of TREE, but also in the form of GRID, that is actually avaliable only in very expensive windows XML editors.

all what you need to do-its just register for free

See you in http://xmlia.com

Source: http://community.livejournal.com/sqlserver/56743.html

Date: 01/29/07     Keywords: sql

I would like to trim off everything past a certain point in a return left. Example Return: This is my information
Desired Return: information

This works just fine and dandy in Crystal but I know instr doesnt work in SQL how can I convert/overcome this hurdle

Example in crystal:
Right("information", (Len("Information")) - (InStr(1,"information", ' ' )))

Thanks for any help...

Source: http://community.livejournal.com/sqlserver/55892.html

Date: 01/25/07     Keywords: no keywords

I have a table of scores that I need to query. There are multiple scores per row. The scores are stored numerically, but have a text equivalent stored in a separate table.

So my scores table has other fields as well as score fields with the scores stored numerically, for instance:
iReader1Score
iReader2Score
iReader3Score

My scoresText table has the text equivalent of the scores and an iReaderScore that matches with the iReaderXScores in the scores table:
iReaderScore
sScoreText

I need to extract the other data from the scores table as well as my scores displayed as their text equivalents.

Any help would be appreciated. Let me know if more information is required.

(x-posted)

Source: http://community.livejournal.com/sqlserver/55654.html