Date: 02/14/06     Keywords: sql

Anybody use Apex SQL Edit? If so is it worth the money? If not, any recommendations? I have Tried SQL Buddy and SQLPrompt but I am looking for a robust SQL IDE Solution.

Any Input would be great.

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

Date: 02/10/06     Keywords: database, sql

Hi all,
I'm new to SQL Server, I'm an Oracle developer but have been handed a project to convert some reports to run against a SQL Server database using Crystal Reports. Is there anyone here with experience in developing Crystal Reports against a SQL server database that I can bounce a couple of basic questions off?

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

Date: 02/04/06     Keywords: software, database

I work for a software company and we have this huge query used for billing and it has not changed but in two weeks time this query takes a lot more time to run. It use to take 10-15 minutes and now it takes 3 hours to complete. Only a week or two worth of data has been added to this database which a customer has had for 2-3 years. I'm stumped on how this could happen. Is there a tool to test the data to see if the data is messing up the query (bad values in a column?) I've tried the execution plan but it wasn't much help in Query Analyzer. We are thinking about rewriting the query which would be good but if there is bad information in the database that would be good to find.

Thanks for any suggestions anyone may have.

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

Date: 01/30/06     Keywords: no keywords

Sorry, I can't help you bring back your data. It's status is "Truly Fucked".

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

Date: 01/26/06     Keywords: sql, web

Hey all... I don't really consider myself much of a SQL Server Guru yet. However, my company wants me to start teaching a web developer the basics of SQL Server. I have about an hour with him tomorrow, and am at a bit of a loss on what should be covered. I've already pointed him to a good SQL reference book and was thinking on working him up to various "challenges" where I'll give him a handful of tables, and ask for a query that generates a given output.

That all being said, what specific things do you all think a (relative) newbie of SQL Server *NEEDS* to know?

thanks for your help.

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

Date: 01/26/06     Keywords: google

I'm trying to create a stored procedure that will generate and return a unique identifier when called.

Here's my stored procedure code:

CREATE PROCEDURE sp_tc_createuniqueid AS

DECLARE @uid uniqueidentifier
SET @uid = newid()

RETURN @uid

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

Date: 01/24/06     Keywords: sql

I'm hoping someone knows, because I've been looking on the Internet for the past hour and a half. I have seen this question asked many, many times, but I have no yet seen an answer. sorry if I've asked this exact question before. Maybe someone's new here and knows.

I'm getting the following output:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 10 (underdecis).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 11 (yesno).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 3, column 10 (underdecis).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 4, column 11 (yesno).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 5, column 10 (underdecis).
[etc., etc. ad nauseam]

...from the following command:

bulk insert edi.dbo.hdstaging2 from '\\tesla\sql data\load\hd.txt' with ( DATAFILETYPE = 'char')



Here's the data that corresponds to the above error messages:

HD	030		HLT	NO PREF	FAM		
HD	030		HLT	NO PREF		  
HD	030		HLT	NO PREF		                                   
HD	030		HLT	NO PREF	EMP		                           
HD	030		HLT	NO PREF	FAM		

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

Date: 01/19/06     Keywords: database, asp, sql, web, microsoft

Okay, here's one for you. Not sure whether I should be posting here or in csharp; figured I'd try here first.

Trying to set up a SqlConnection in C#.NET (2005 Express Edition). When I test the connection, I get the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

All well and good... except the machine to which I'm trying to connect is running SQL Server 2000, not 2005.

There is no connection string as of yet-- it's simply trying to locate the database and access the data (which, I should add, I can do just fine with Enterprise Manager, Query Analyzer, etc.). All I have right now is an "Add Connection" dialog box that has the DSP (Microsoft Sql Server Database File (SqlClient)) and the physical path to the database file (\\edison\c$\Program Files\Microsoft SQL Server\MSSQL\Data\Hospital Rates_Data.MDF). I hit "test connection," and up comes the error.

I am aware of this article at support.microsoft.com. SQL Server 2000 was installed on this machine at least a year before 2005 was released, so that's not the problem (at least, not as they explain it here).Still, I attempted the workaround, and the user they specify does not exist, so I assume that 2005 was never installed on that machine. I've found no help through a websearch; is there no one that doesn't ask questions specifically related to ASP.NET? Sheesh.

If anyone has any ideas, I'd be grateful. Thanks.

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

Date: 01/18/06     Keywords: sql

Hi all,

I'm looking for a way to update a table based upon a recordset returned from a stored procedure.

It appears as if you can't join on the results from a stored procedure nor loop over those records using a cursor.

I will be the first to admit, I often miss things when combing through documentation, but I was hoping to use a join in an update statement (which I could do, but I would prefer not to duplicate the logic that I've already got stored in this stored procedure)

If anyone can offer any suggestions, I would be most greatful.


--gryff

EDIT: I'm using SQL Server 2000

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

Date: 01/18/06     Keywords: sql

Hi all,

I'm looking for a way to update a table based upon a recordset returned from a stored procedure.

It appears as if you can't join on the results from a stored procedure nor loop over those records using a cursor.

I will be the first to admit, I often miss things when combing through documentation, but I was hoping to use a join in an update statement (which I could do, but I would prefer not to duplicate the logic that I've already got stored in this stored procedure)

If anyone can offer any suggestions, I would be most greatful.


--gryff

EDIT: I'm using SQL Server 2000

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

Date: 01/17/06     Keywords: no keywords

I'm trying to do this in ONE query - although I'm not sure that's possible.

Basically, I need a list of children ordered by the center and classroom they were enrolled in as of a certain date. Current center and classroom data is stored in the child table with the child's info but if/when they are transferred, the info about their old center, old classroom, new center and new classroom is stored in the transfers table. Any ideas on how to combine this into ONE big query?

(I like the way a recent poster formatted their question. I'll try to emulate theirs in hopes of giving the most info.)

child
- ID
- first
- last
- dob
- enrollment_date
- drop_date
- center
- classroom

eligibility
- child_ID
- income_eligible

centers
- ID
- name

classrooms
- ID
- name

transfers
- ID
- child_id
- old_center
- old_classroom
- new_center
- new_classroom
- transfer_date

This query gives me the data I need for each child, ordered by Center (school) and Classroom:

SELECT child.ID AS 'ChildID', child.first + ' ' + child.last AS 'Child Name', child.dob AS 'Date of Birth', child.enrollment_date AS 'Enrollment Date', centers.name AS 'Center', classrooms.name AS 'Classroom', eligibility.income_eligible AS 'Income Eligible at Enrollment?' FROM child INNER JOIN eligibility ON child.ID = eligibility.child_id INNER JOIN centers ON child.center = centers.ID INNER JOIN classrooms ON child.classroom = classrooms.ID WHERE child.enrollment_date <= '10/31/2005' AND (child.drop_date is NULL OR child.drop_date > '10/31/2005') ORDER BY child.center, child.classroom, child.last, child.first

SELECT child_id, old_center, old_classroom, new_center, new_classroom, transfer_date FROM transfers WHERE transfer_date > '10/31/2005'

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

Date: 01/17/06     Keywords: no keywords

I'm trying to do this in ONE query - although I'm not sure that's possible.

Basically, I need a list of children ordered by the center and classroom they were enrolled in as of a certain date. Current center and classroom data is stored in the child table with the child's info but if/when they are transferred, the info about their old center, old classroom, new center and new classroom is stored in the transfers table. Any ideas on how to combine this into ONE big query?

(I like the way a recent poster formatted their question. I'll try to emulate theirs in hopes of giving the most info.)

child
- ID
- first
- last
- dob
- enrollment_date
- drop_date
- center
- classroom

eligibility
- child_ID
- income_eligible

centers
- ID
- name

classrooms
- ID
- name

transfers
- ID
- child_id
- old_center
- old_classroom
- new_center
- new_classroom
- transfer_date

This query gives me the data I need for each child, ordered by Center (school) and Classroom:

SELECT child.ID AS 'ChildID', child.first + ' ' + child.last AS 'Child Name', child.dob AS 'Date of Birth', child.enrollment_date AS 'Enrollment Date', centers.name AS 'Center', classrooms.name AS 'Classroom', eligibility.income_eligible AS 'Income Eligible at Enrollment?' FROM child INNER JOIN eligibility ON child.ID = eligibility.child_id INNER JOIN centers ON child.center = centers.ID INNER JOIN classrooms ON child.classroom = classrooms.ID WHERE child.enrollment_date <= '10/31/2005' AND (child.drop_date is NULL OR child.drop_date > '10/31/2005') ORDER BY child.center, child.classroom, child.last, child.first

SELECT child_id, old_center, old_classroom, new_center, new_classroom, transfer_date FROM transfers WHERE transfer_date > '10/31/2005'

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

Date: 01/17/06     Keywords: database, sql

A new product that might be of interest to users in this community: TextConverter

Synopsis:
- extraction of data from text files (from simple CSV to complex)
- script-enabled transformation
- load into any database including SQL Server

Any feedback would be greatly appreciated.

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

Date: 01/17/06     Keywords: database, sql

A new product that might be of interest to users in this community: TextConverter

Synopsis:
- extraction of data from text files (from simple CSV to complex)
- script-enabled transformation
- load into any database including SQL Server

Any feedback would be greatly appreciated.

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

Date: 01/15/06     Keywords: sql

table_name
- column_name

utterance
- utteranceID
- speakerID

utteranceHistory
- utteranceHistoryID
- utteranceID
- speakerName
- workTypeDescription
- businessEntityDescription
- eventDate

speaker
- speakerID
- personID

person
- personID
- firstName
- lastName

the task: i need to collect all occurrences when a speakername had been changed in the system. returning in this query: the utteranceID, original speakerName, and final speakerName. I am using MS SQL Server, so i can't use a MINUS as i would have liked to.

-- here is what i tried
-- sadly this is lacking by not returning all of the rows where a speakerName change has occurred
select uh1.utteranceID,
uh1.speakerName AS originalSpearkerName,
uh2.speakerName AS finalSpearkerName
from utteranceHistory uh1 INNER JOIN utteranceHistory uh2
ON (uh1.locationId = uh2.locationId
AND uh1.utteranceID = uh2.utteranceID
AND uh1.utteranceHistoryID <> uh2.utteranceHistoryID)
WHERE uh1.speakerName <> uh2.speakerName
AND uh1.utteranceHistoryID = (SELECT MIN(utteranceHistoryID) FROM
utteranceHistory where utteranceID = uh1.utteranceID)
AND uh2.utteranceHistoryID = (SELECT MAX(utteranceHistoryID) FROM
utteranceHistory utteranceID = uh2.utteranceID)
ORDER BY uh1.utteranceID

-- here is what returns all the changes in the speakerName, however
-- returns two rows per utteranceId, which is really not easy to format
-- especially when the results i am working with are in the thousands.

SELECT utteranceID, speakerName
FROM utteranceHistory
WHERE utteranceID IN
(
SELECT utteranceID
FROM utteranceHistory
WHERE speakerName IS NOT NULL
GROUP BY utteranceid HAVING count(utteranceid) =2
)
and speakerName IS NOT NULL
ORDER BY utteranceid, eventdate

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

Date: 01/15/06     Keywords: sql

table_name
- column_name

utterance
- utteranceID
- speakerID

utteranceHistory
- utteranceHistoryID
- utteranceID
- speakerName
- workTypeDescription
- businessEntityDescription
- eventDate

speaker
- speakerID
- personID

person
- personID
- firstName
- lastName

the task: i need to collect all occurrences when a speakername had been changed in the system. returning in this query: the utteranceID, original speakerName, and final speakerName. I am using MS SQL Server, so i can't use a MINUS as i would have liked to.

-- here is what i tried
-- sadly this is lacking by not returning all of the rows where a speakerName change has occurred
select uh1.utteranceID,
uh1.speakerName AS originalSpearkerName,
uh2.speakerName AS finalSpearkerName
from utteranceHistory uh1 INNER JOIN utteranceHistory uh2
ON (uh1.locationId = uh2.locationId
AND uh1.utteranceID = uh2.utteranceID
AND uh1.utteranceHistoryID <> uh2.utteranceHistoryID)
WHERE uh1.speakerName <> uh2.speakerName
AND uh1.utteranceHistoryID = (SELECT MIN(utteranceHistoryID) FROM
utteranceHistory where utteranceID = uh1.utteranceID)
AND uh2.utteranceHistoryID = (SELECT MAX(utteranceHistoryID) FROM
utteranceHistory utteranceID = uh2.utteranceID)
ORDER BY uh1.utteranceID

-- here is what returns all the changes in the speakerName, however
-- returns two rows per utteranceId, which is really not easy to format
-- especially when the results i am working with are in the thousands.

SELECT utteranceID, speakerName
FROM utteranceHistory
WHERE utteranceID IN
(
SELECT utteranceID
FROM utteranceHistory
WHERE speakerName IS NOT NULL
GROUP BY utteranceid HAVING count(utteranceid) =2
)
and speakerName IS NOT NULL
ORDER BY utteranceid, eventdate

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

Date: 01/12/06     Keywords: no keywords

Get the space consumed by a particular table or group of tables, including their indexes:

select
substring(object_name(o.id),1,40) "Table Name",
(sum(convert(float,used)) * min(d.low) ) / 1024000.0 MBytes
from sysindexes i, master.dbo.spt_values d, sysobjects o
where o.id = i.id
and indid in (0, 1, 255) and d.number = 1 and d.type = 'E'
and object_name(o.id) like ('% %')
group by substring(object_name(o.id),1,40)
order by mbytes desc
compute sum((sum(convert(float,used)) * min(d.low) ) / 1024000.0)

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

Date: 01/12/06     Keywords: no keywords

Get the space consumed by a particular table or group of tables, including their indexes:

select
substring(object_name(o.id),1,40) "Table Name",
(sum(convert(float,used)) * min(d.low) ) / 1024000.0 MBytes
from sysindexes i, master.dbo.spt_values d, sysobjects o
where o.id = i.id
and indid in (0, 1, 255) and d.number = 1 and d.type = 'E'
and object_name(o.id) like ('% %')
group by substring(object_name(o.id),1,40)
order by mbytes desc
compute sum((sum(convert(float,used)) * min(d.low) ) / 1024000.0)

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

Date: 01/03/06     Keywords: database, sql

Just joined. Looks like a nice comm.

Quick question:

I have a production database that is about 1 GB in size. I have been tasked to script that baby out into sql files. The tables, procedures, etc. should be little problem (thanks to the database scripting wizard), but the data is eluding me. How can I (relatively automated) script table data into a series of INSERT statements?

Is there any way to avoid manually typing:
"INSERT INTO tbl_WHATEVER (ID, FIELD1, FIELD2, ...) VALUES (1, 1, 1, ...)"?

Thanks in advance!

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

Date: 01/03/06     Keywords: database, sql

Just joined. Looks like a nice comm.

Quick question:

I have a production database that is about 1 GB in size. I have been tasked to script that baby out into sql files. The tables, procedures, etc. should be little problem (thanks to the database scripting wizard), but the data is eluding me. How can I (relatively automated) script table data into a series of INSERT statements?

Is there any way to avoid manually typing:
"INSERT INTO tbl_WHATEVER (ID, FIELD1, FIELD2, ...) VALUES (1, 1, 1, ...)"?

Thanks in advance!

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