I have a database whose stored procedures need to be used by two different applications. The first one is an ASP.NET MVC application we developed inhouse, which recognizes the DATETIMEOFFSET datatype (which we chose because we have operations in more than one time zone). The second one is a Java-based application, which does not. (It appears there exists a JDBC which supports DATETIMEOFFSET, but this application doesn't use it.) It is simple enough to turn a DATETIMEOFFSET into a DATETIME in UTC and let the application convert it to the local timezone. But I have been racking my brain trying to figure out a way to let each application get the data in the format it requires without having to duplicate my stored procedures.
The first thing I tried to do was use an additional parameter in the stored procedures to specify which application was making the call, and then use a CASE statement to determine whether to return the DATETIMEOFFSET, or the DATETIME. That's when I learned how CASE statements handle differing datatypes. So no good there.
The next thing I tried was to create a "wrapper" stored procedure which would
- call the desired stored procedure
- put the results into a temp table
- find the DATETIMEOFFSET columns
- make a temporary DATETIME column for each
- write the original value into the new column, after converting to UTC
- drop the original column
- recreate it as a DATETIME
- put the value from the temporary column back into the new one
- and finally, drop the temporary column
Just writing that out hammers home how convoluted this is. To make it worse, I had to use OPENROWSET, and to get that to accept a variable as the name of the stored procedure, it had to be called EXEC(@sql). The code snippet is below.
But this all failed when I called it from my application with this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Access to the remote server is denied because no login-mapping exists.
At this point I'm almost ready to throw my hands up in defeat and maintain two separate sets of stored procedures, identical except for how they handle time. But I'd really rather not, because it sounds like a maintenance nightmare. Another solution would be to refactor the whole database to use DATETIMEs in UTC, but that is a daunting task, and will also require retesting and/or fixing the .NET application.
Can anyone think of another way to conditionally return different datatypes from the same stored procedure? Or can anyone help me figure out why using OPENROWSET with dynamic SQL would cause the remote server access error that I'm getting with my "wrapper" stored procedure?
SET @sql = N' SELECT * INTO ' + @globalTempTable + N' FROM OPENROWSET(''SQLNCLI'' , ''Server=' + @@SERVERNAME + ';Trusted_Connection=yes;'' , ''' + @fullStatement + N''' ); ' ; EXEC (@sql); DECLARE @col NVARCHAR(128); DECLARE @newCol NVARCHAR(128); CREATE TABLE #cols (col sysname); INSERT INTO #cols (col) SELECT cols.name FROM tempdb.sys.columns cols JOIN sys.types ty ON cols.user_type_id = ty.user_type_id WHERE cols.object_id = OBJECT_ID('tempdb..' + @globalTempTable) AND ty.name = 'datetimeoffset'; WHILE EXISTS (SELECT 1 FROM #cols) BEGIN SELECT TOP 1 @col = col FROM #cols ORDER BY col; SELECT @newCol = N'__' + @col + N'__'; SET @sql = N'ALTER TABLE ' + @globalTempTable + N' ADD ' + @newCol + N' DATETIME'; EXEC (@sql); SET @sql = N'UPDATE ' + @globalTempTable + N' SET ' + @newCol + N' = CAST (' + @col + N' AT TIME ZONE ''UTC'' AS DATETIME)'; EXEC (@sql); SET @sql = N'ALTER TABLE ' + @globalTempTable + N' ALTER COLUMN ' + @col + N' DATETIME'; EXEC (@sql); SET @sql = N'UPDATE ' + @globalTempTable + N' SET ' + @col + N' = ' + @newCol; EXEC (@sql); SET @sql = N'ALTER TABLE ' + @globalTempTable + N' DROP COLUMN ' + @newCol; EXEC (@sql); DELETE #cols WHERE col = @col; END; DROP TABLE #cols; SET @sql = N'SELECT * FROM ' + @globalTempTable; EXEC (@sql); SET @sql = N'DROP TABLE ' + @globalTempTable; EXEC (@sql);