Solved

TSQL Update record in table2 with data from multiple records in table1

Posted on 2013-01-20
41
544 Views
Last Modified: 2013-01-28
How do I code tsql statement(s) to update the sections field in table2 with all the sections for the same course in the same semesterPS.  I'd like to separate each section with a comma and space. For example: semesterPS = 2133, course = NTDT200, sections = 176, 194, 195.  Thank you.

data in table1
data in table1
data in table2
data in table2
0
Comment
Question by:scover22
  • 12
  • 8
  • 8
  • +3
41 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 38799285
scover22,

Can you explain why you would want to do this?  It denormalizes the data to an extreme degree.  There are options to this.  On would be a query or a materialized view.

Could you provide more details on the application and the amount of data in your applicatio?

Best regards,

AielloJ
0
 

Author Comment

by:scover22
ID: 38799385
If you can suggest another way to do it, I'll be happy to consider it.  The sections field is used on some reports, but otherwise the data in table2 is no longer used.  I can't explain why it was done this way other than ignorance at the time of initial development and no strict commitment to normalization.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 38799456
I agree with AielloJ: this is a very bad idea.

Assuming you leave your data as it is in table1, which is normalized, then you can simply use a query like this to generate your report output:

CREATE TABLE table1 (SemesterPS int, Semester varchar(20), Course varchar(50), Section varchar(20))

INSERT INTO table1 (SemesterPS, Semester, Course, Section) VALUES
(2133, '13S', 'ART180', '194'),
(2133, '13S', 'FASH666', '000'),
(2133, '13S', 'MEEG818', '194'),
(2133, '13S', 'NTDT200', '176'),
(2133, '13S', 'NTDT200', '194'),
(2133, '13S', 'NTDT200', '195'),
(2133, '13S', 'WOMS414', '194'),
(2133, '13S', 'WOMS414', '195'),
(2135, '13J', 'ART180', '194'),
(2135, '13J', 'ART180', '195'),
(2135, '13J', 'FASH666', '006'),
(2135, '13J', 'NTDT200', '194'),
(2135, '13J', 'NTDT200', '195')

SELECT a.SemesterPS, a.Semester, a.Course,
    SUBSTRING((SELECT ', ' + b.Section
    FROM table1 b
    WHERE a.SemesterPS = b.SemesterPS AND a.Semester = b.Semester AND a.Course = b.Course
    ORDER BY b.Section
    FOR XML PATH('')), 3, 8000) AS Sections
FROM table1 a
GROUP BY a.SemesterPS, a.Semester, a.Course
ORDER BY a.SemesterPS, a.Semester, a.Course

DROP TABLE table1

Open in new window

0
 

Author Comment

by:scover22
ID: 38801869
I've been looking at the entire application and I think I can eliminate this non-normalized data.  I would like to use Matthew's sql in case I need the section numbers grouped.
I changed Matthew's sql to use the actual tables but I'm getting a error
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FOR'.

The field Semester is not needed so I removed it from his sample code.  I've never used XML before and that seems to be where the error is. What am I missing? Also will this start the string with a comma?  Thanks.

Here is the sql:
SELECT a.SemesterPS, a.Course,
    SUBSTRING((SELECT ', ' + b.Section
    FROM SemesterCourses b
    WHERE a.SemesterPS = b.SemesterPS AND a.Course = b.Course
    ORDER BY b.Section
    FOR XML PATH('')), 3, 8000) AS Sections
FROM  Textbooks a
GROUP BY a.SemesterPS, a.Course
ORDER BY a.SemesterPS, a.Course
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38801931
I apologize, I am just now noticing that you indicated SQL Server 2000 as your version.  FOR XML PATH debuted in SQL Server 2005, so it will not work in 2000.

Please clarify which version of SQL Server you are using.

As for the string starting with a comma: that is why I used SUBSTRING, to remove the leading delimiter :)
0
 

Author Comment

by:scover22
ID: 38802027
I'm using SQL Server 2000  or as it shows in all programs Microsoft SQL Server 7.0
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38802230
Not an answer to your question, but Microsoft will stop all support for SQL Server 2000 come April: http://www.microsoft.com/en-us/sqlserver/support/support-updates.aspx

I hope you have a migration plan in place :)

Anyway, a UDF-based approach should work.  I modified my example to use this; you can adapt it to your needs:

CREATE TABLE table1 (SemesterPS int, Semester varchar(20), Course varchar(50), Section varchar(20))

INSERT INTO table1 (SemesterPS, Semester, Course, Section) VALUES
(2133, '13S', 'ART180', '194'),
(2133, '13S', 'FASH666', '000'),
(2133, '13S', 'MEEG818', '194'),
(2133, '13S', 'NTDT200', '176'),
(2133, '13S', 'NTDT200', '194'),
(2133, '13S', 'NTDT200', '195'),
(2133, '13S', 'WOMS414', '194'),
(2133, '13S', 'WOMS414', '195'),
(2135, '13J', 'ART180', '194'),
(2135, '13J', 'ART180', '195'),
(2135, '13J', 'FASH666', '006'),
(2135, '13J', 'NTDT200', '194'),
(2135, '13J', 'NTDT200', '195')

GO

CREATE FUNCTION dbo.ConcatForMe (@SemesterPS int, @Semester varchar(20), @Course varchar(50))
RETURNS varchar(8000) AS BEGIN

    DECLARE @result varchar(8000) = ''
    
    SELECT @result = @result + ', ' + Section
    FROM table1
    WHERE SemesterPS = @SemesterPS AND Semester = @Semester AND Course = @Course
    
    SET @result = SUBSTRING(@result, 3, 8000)
    
    RETURN @result

END

GO

SELECT a.SemesterPS, a.Semester, a.Course,
    dbo.ConcatForMe(a.SemesterPS, a.Semester, a.Course) AS Sections
FROM table1 a
GROUP BY a.SemesterPS, a.Semester, a.Course
ORDER BY a.SemesterPS, a.Semester, a.Course

DROP TABLE table1
DROP FUNCTION dbo.ConcatForMe

Open in new window

0
 

Author Comment

by:scover22
ID: 38802549
Thanks for the heads up on 2000. I'll let the university know to make conversion plans now.

I copied your code exactly and ran it in Query Analyzer.  I get these errors when I check the code.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'FUNCTION'.
Server: Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@result'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@result'.
Server: Msg 137, Level 15, State 1, Line 13
Must declare the variable '@result'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'a'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'FUNCTION'.

Thanks for your continued help.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38802910
I would have expected that to work, but I do not have an instance of SQL Server 2000 I can use to test it.

If no one replies within a few hours, you should click "Request Attention" right under your original question, and ask the Mods for help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803287
The following syntax was introduced with SQL Server 2008:

INSERT INTO table1...
VALUES(...), (...), (...)

And needs to be recreated as multiple INSERT statements or one INSERT statement with a UNIONd SELECT as in:
INSERT INTO table1 (SemesterPS, Semester, Course, Section)
SELECT 2133, '13S', 'ART180', '194'
UNION ALL
SELECT 2133, '13S', 'FASH666', '000'
UNION ALL
SELECT 2133, '13S', 'MEEG818', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '176'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '195'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '194'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '195'
UNION ALL
SELECT 2135, '13J', 'ART180', '194'
UNION ALL
SELECT 2135, '13J', 'ART180', '195'
UNION ALL
SELECT 2135, '13J', 'FASH666', '006'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '194'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '195'

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803294
You also cannot do the following with SQL Server 2000 (also introduced with SQL Server 2008):
    DECLARE @result varchar(8000) = ''

You have to do:
    DECLARE @result varchar(8000)
    Set @result = ''

Or you could simplify it as follows:
CREATE FUNCTION dbo.ConcatForMe(
				 @SemesterPS int,
				 @Semester varchar(20),
				 @Course varchar(50)
				)
RETURNS varchar(8000)

AS 

BEGIN

    DECLARE @result varchar(8000)

    SELECT  @result = ISNULL(@result + ', ', '') + Section
    FROM    table1
    WHERE   SemesterPS = @SemesterPS
            AND Semester = @Semester
            AND Course = @Course

    RETURN @result

END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803319
So to plagiarize Patrick's script (no points please) you could write the whole script as follows:
CREATE TABLE table1 (
     SemesterPS int,
     Semester varchar(20),
     Course varchar(50),
     Section varchar(20))

INSERT INTO table1 (SemesterPS, Semester, Course, Section)
SELECT 2133, '13S', 'ART180', '194'
UNION ALL
SELECT 2133, '13S', 'FASH666', '000'
UNION ALL
SELECT 2133, '13S', 'MEEG818', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '176'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '195'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '194'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '195'
UNION ALL
SELECT 2135, '13J', 'ART180', '194'
UNION ALL
SELECT 2135, '13J', 'ART180', '195'
UNION ALL
SELECT 2135, '13J', 'FASH666', '006'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '194'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '195'

GO

CREATE FUNCTION dbo.ConcatForMe(
	 @SemesterPS int,
	 @Semester varchar(20),
	 @Course varchar(50)
	)
RETURNS varchar(8000)

AS 

BEGIN

    DECLARE @result varchar(8000)

    SELECT  @result = ISNULL(@result + ', ', '') + Section
    FROM    table1
    WHERE   SemesterPS = @SemesterPS
            AND Semester = @Semester
            AND Course = @Course

    RETURN @result

END

GO

SELECT  a.SemesterPS,
        a.Semester,
        a.Course,
        dbo.ConcatForMe(a.SemesterPS, a.Semester, a.Course) AS Sections
FROM    table1 a
GROUP BY 
	a.SemesterPS,
        a.Semester,
        a.Course
ORDER BY
	a.SemesterPS,
        a.Semester,
        a.Course

DROP TABLE table1
DROP FUNCTION dbo.ConcatForMe

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803371
Thanks Anthony!  I'd forgotten that those other two syntactical shortcuts both debuted in SQL Server 2008.

That's what happens, I guess, when the only sandbox I have access to is SQL Server 2008 R2 :)
0
 

Author Comment

by:scover22
ID: 38803575
I'm still getting compile errors.  Here are the errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 16
Must declare the variable '@SemesterPS'.
Server: Msg 178, Level 15, State 1, Line 19
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'a'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'FUNCTION'.

I have create at stored procedure for the code, but I'm still getting the error on line 19.
The table is being created OK, but the creation of the function is not working.  Thanks.

Here is the code.  
ALTER         PROCEDURE dbo.EE_test
AS

IF OBJECT_ID('tempdb..table1') IS NOT NULL
      BEGIN
            DROP TABLE table1
      END
CREATE TABLE table1 (
     SemesterPS int,
     Semester varchar(20),
     Course varchar(50),
     Section varchar(20))

INSERT INTO table1 (SemesterPS, Semester, Course, Section)
SELECT 2133, '13S', 'ART180', '194'
UNION ALL
SELECT 2133, '13S', 'FASH666', '000'
UNION ALL
SELECT 2133, '13S', 'MEEG818', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '176'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '195'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '194'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '195'
UNION ALL
SELECT 2135, '13J', 'ART180', '194'
UNION ALL
SELECT 2135, '13J', 'ART180', '195'
UNION ALL
SELECT 2135, '13J', 'FASH666', '006'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '194'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '195'

GO
CREATE FUNCTION dbo.ConcatForMe(
       @SemesterPS int,
       @Semester varchar(20),
       @Course varchar(50)
      )
RETURNS varchar(8000)

AS

BEGIN

    DECLARE @result varchar(8000)

    SELECT  @result = ISNULL(@result + ', ', '') + Section
    FROM    table1
    WHERE   SemesterPS = @SemesterPS
            AND Course = @Course

    RETURN @result

END

GO
SELECT  a.SemesterPS,
      a.Semester,
        a.Course,
        dbo.ConcatForMe(a.SemesterPS, a.Semester, a.Course) AS Sections
FROM    table1 a
GROUP BY
      a.SemesterPS,
      a.Semester,
        a.Course
ORDER BY
      a.SemesterPS,
      a.Semester,
        a.Course

DROP TABLE table1
DROP FUNCTION dbo.ConcatForMe
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803809
Don't create a Stored Procedure unless you know how to execute it.  Just run the script as I posted previously and let us know what errors you get with SQL Server 2000.
0
 

Author Comment

by:scover22
ID: 38805897
I copied code from acperkins post into query analyzer and ran it.  The table creation and Insert works OK.  I get these messages.

(13 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 17
Must declare the variable '@SemesterPS'.
Server: Msg 178, Level 15, State 1, Line 21
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'a'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'FUNCTION'.

code I ran to get above messages:
CREATE TABLE table1 (
     SemesterPS int,
     Semester varchar(20),
     Course varchar(50),
     Section varchar(20))

INSERT INTO table1 (SemesterPS, Semester, Course, Section)
SELECT 2133, '13S', 'ART180', '194'
UNION ALL
SELECT 2133, '13S', 'FASH666', '000'
UNION ALL
SELECT 2133, '13S', 'MEEG818', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '176'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '194'
UNION ALL
SELECT 2133, '13S', 'NTDT200', '195'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '194'
UNION ALL
SELECT 2133, '13S', 'WOMS414', '195'
UNION ALL
SELECT 2135, '13J', 'ART180', '194'
UNION ALL
SELECT 2135, '13J', 'ART180', '195'
UNION ALL
SELECT 2135, '13J', 'FASH666', '006'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '194'
UNION ALL
SELECT 2135, '13J', 'NTDT200', '195'

GO

CREATE FUNCTION dbo.ConcatForMe(
       @SemesterPS int,
       @Semester varchar(20),
       @Course varchar(50)
      )
RETURNS varchar(8000)

AS

BEGIN

    DECLARE @result varchar(8000)

    SELECT  @result = ISNULL(@result + ', ', '') + Section
    FROM    table1
    WHERE   SemesterPS = @SemesterPS
            AND Semester = @Semester
            AND Course = @Course

    RETURN @result

END

GO

SELECT  a.SemesterPS,
        a.Semester,
        a.Course,
        dbo.ConcatForMe(a.SemesterPS, a.Semester, a.Course) AS Sections
FROM    table1 a
GROUP BY
      a.SemesterPS,
        a.Semester,
        a.Course
ORDER BY
      a.SemesterPS,
        a.Semester,
        a.Course

DROP TABLE table1
DROP FUNCTION dbo.ConcatForMe
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38807407
I am drawing a blank, I tested this with Compatibility level set to 80 (SQL Server2000) without any problem.

As Patrick mentioned here http:#a38802910 you may want to click on Request Attention to get more help.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 38821373
OK, so the create table and populating that table seems to work...

To make sure do a :
select * from table1

Open in new window


Now let's check your default schema by doing :
select TABLE_SCHEMA from information_schema.tables where table_name = 'table1'

Open in new window


If it is not 'dbo' then you will need to use your default schema in place of 'dbo' in the query.

Of course, you could use your actual/real table, you dont have to create table1. Just make sure that where ever you see the use of table1 you change it for your own name

Except the DROP - dont do that if using your real table

And no need to drop the function if it is working - you will need that next time you go to run your query (you will need to fix the table name though).

So, let's take this step by step.
1) create the function (maybe with real table name)
2) do the select
3) DONT do the DROP

If you create your table in the first window, then, open a new window in Query Analyser and do the following (making sure you are in the right database):

CREATE FUNCTION udf_ConcatForMe (@SemesterPS int, @Semester varchar(20), @Course varchar(50)  )

RETURNS varchar(8000)

AS 

BEGIN

    DECLARE @result varchar(8000)

    SELECT  @result = isnull(@result + ', ', '') + Section     -- in case section is an integer in your real table you will need to do :  left(Section,6) 
    FROM    table1     -- or your real table
    WHERE   SemesterPS = @SemesterPS
    AND     Semester = @Semester
    AND     Course = @Course

    RETURN @result

END

GO

Open in new window


Does that work for you, or do you get errors ?

Might be worth having a read of : http://msdn.microsoft.com/en-us/library/aa258261(v=sql.80).aspx to make sure you understand the create function (and have permission to do so).

If that worked, then you can open another new window (checking database again) and run the query part using the owner or schema that we found using the select from information_schema.tables in place of 'dbo'.

SELECT  SemesterPS,
        Semester,
        Course,
        dbo.udf_ConcatForMe(SemesterPS, Semester, Course) AS Sections
FROM    table1                  
GROUP BY 
        SemesterPS,
        Semester,
        Course
ORDER BY
        SemesterPS,
        Semester,
        Course

Open in new window


So, by doing step by individual step, we should be able to indentify the underlying problem.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 100 total points
ID: 38822296
Maybe you guys will find this more elegant :-)

declare @dummy int
select @dummy=1

while @@rowcount>0 begin
	update table2
	set sections=isnull(sections,'') + ',' + table1.section
	from table1 join table2 on table1.semesterps=table2.semesterps
	   and table1.semester=table2.semester
	   and table1.course=table2.course
	   and isnull(table2.sections,'') not like '%' + table1.section + '%'
          -- assuming all courses have same number of digits
end
update table2 set sections = substring(sections,2,9999)

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38822358
Yep, quite elegant, and it also answers the question...

But the underlying theme is one of "do you need another table" or will a select query (or view) be enough.

If table2 has to exist, then yes, the update works very well - well apart from sections like 119, 190, 1900, 219 where the "like" might get a little confused with a section "19" or a "6". But should not be a problem if always a char(3) with leading zeros.

If table2 doesnt have to exist other than to concatenate sections, then best to save the headaches of maintaining and synchronizing the second one (IMO).

Nice :)
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:scover22
ID: 38822948
Thanks for your patience.

I tried the steps suggested by mark_wills.
Table1 exists in my db and the TABLE_SCHEMA is dbo.
When I check or run the Create function code just as suggested by mark, I get these errors.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 13
Must declare the variable '@SemesterPS'.
Server: Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return status can only be used in a stored procedure.

It must be that I don't have permission to create a function. I have access to security and I am owner of the db. What do I need to change to give me permission to create a function?

I tried vadimrappi's code and after a little manipulation it gave me the correct results, but it does take two tables to accomplish it. I believe his code will be useful to me in other code I need to write.

I'd like now to figure out why I can't create a function and then I'll close this question.

Thanks.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38823260
Certainly not because of permissions. More looks like you ran not the whole code.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38823950
Well, it could be permissions.

Was hoping to see more in the error message.

You do need the CREATE FUNCTION permission, or be part of sysadmins... Have a read of : http://msdn.microsoft.com/en-us/library/aa905178(v=sql.80).aspx

If you do have SA privs, then you can quickly check by logging in as SA and seeing if that makes the difference for you.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38823967
And from the earlier link...

Permissions
 
Users should have the CREATE FUNCTION permission to execute the CREATE FUNCTION statement.
 
CREATE FUNCTION permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of sysadmin and db_owner can grant CREATE FUNCTION permissions to other logins by using the GRANT statement.

GRANT CREATE FUNCTION TO your_username;
GO

Open in new window

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38824080
Then the error message would be simply

Server: Msg 262, Level 14, State 1, Procedure udf_ConcatForMe, Line 17
CREATE FUNCTION permission denied in database 'master'.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38824121
Yeah, that's the "more" I was hoping to see from the error message. But it has been a while since playing in Query Analyser.
0
 

Author Comment

by:scover22
ID: 38824307
I'm logged on to Query Analyzer as udonline_rw

Here are the permissions for that user
user permissions
Since udonline_rw should already have permission to create a function, I tried the Grant command on another user.
Still getting errors
grant command
Create Function errors persist
Create Function
Any other ideas?  Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38824368
Well, it certainly looks OK at first glance.

What is the compatability level of your database ? Support for Functions needs to be at least 80 and higher

You can check by :

select cmptlevel from master.dbo.sysdatabases where name = db_name()

or read up on : http://msdn.microsoft.com/en-us/library/aa259649(v=sql.80).aspx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38824417
Support for Functions needs to be at least 80 and higher
Actually there were Functions in SQL Server 7, so perhaps the compatibility is set to 60 or 65.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38824459
Yes, but user defined functions were introduced in SQL2000
0
 

Author Comment

by:scover22
ID: 38824551
compatability level
How can I increase it to 80?  Or should I just move on?

Thanks
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38824567
acperkins is right, I set compatibility level to lower than 80 (including 70), and saw these errors.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38824574
EXEC sp_dbcmptlevel <database-name>, 80
0
 

Author Comment

by:scover22
ID: 38824646
run on production db  
prod cmpt changeI'll ask the University system administrator to change this for me tomorrow.

run on test db
development level changeMy test database doesn't appear to have 80 as an option.  There may be an update I didn't install that was not included with other automatic updates.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38825215
Time to talk to Uni Admins, not having 80 as a choice is not likely to be an update. There is more to it.

And time to move on. Looks like the looping update method for now.

@vadimrapp1, whaddya mean 'acperkins is right' I was the one who pointed out compatability levels and that it needed to be 80 or more. Sheesh.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 38825340
My test database doesn't appear to have 80 as an option.
Then I suspect you are using SQL Server 7 and not SQL Server 2000.  You can confirm that by doing SELECT @@VERSION.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38825341
Yes, but user defined functions were introduced in SQL2000
I stand corrected.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38825372
> whaddya mean 'acperkins is right'

sorry, my mistake.
0
 

Author Comment

by:scover22
ID: 38827086
I ran the version and cmptlevel tsql in production this morning and found that it is SQL Server 2000 and cmptlevel 80 while my test system is SQL Server 7 and cmptlevel 70.  
I ran the script first presented by matthewspatrick in production and the Function created just fine and the code gave me what I needed. I really appreciate all the comments and help to get this resolved. I'm sorry that it was my out of date test db that dragged this out so long. I learned a lot from all of you and I've increased the points so I can divide them among you.
0
 

Author Closing Comment

by:scover22
ID: 38827162
acperkins, you asked for no points, but then contributed more so I think you deserve them. Thanks again for all the help from all of you.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now