Showing posts with label scalar. Show all posts
Showing posts with label scalar. Show all posts

Monday, March 19, 2012

BULK INSERT into a table variable

Bulk Insert to an existing table works fine, but substituting a table variable causes parsing error:

Msg 137, Must declare the scalar variable "@.tblInput". or

Msg 207, Invalid column name '@.tblInput'.

To repro:

CREATE PROC X AS

BEGIN

DECLARE @.strSQL char(99)

DECLARE @.BulkFile varchar(60)

SET @.BulkFile = 'c:\LookingGlass\BulkCopy1.tmp'

DECLARE @.tblInput TABLE (

[Word] [varchar] (50),

[UseCount] [int] )

--works ok with temp table:

SET @.strSQL = 'BULK INSERT temp_tbl From ''' + @.BulkFile + ''''

--but not with table variable

SET @.strSQL = 'BULK INSERT ''' + @.tblInput + ''' From ''' + @.BulkFile + ''''

EXEC(@.strSQL)

END

These don't work either:

SET @.strSQL = 'BULK INSERT @.tblInput From ''' + @.BulkFile + ''''

SET @.strSQL = 'BULK INSERT ' + @.tblInput + ' From ''' + @.BulkFile + ''''

version: Microsoft SQL Server Management Studio Express 9.00.2047.00


You can't do that what you could do is use OPENROWSET with a txt or csv file

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Ignore!!!

|||

The op has tried that already, look at his last line of code

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks, Denis, for the very quick reply. I had searched extensively, and the documentation neglects to mention that Table Variables can't be used with Bulk Insert.

Ironically, the name of the Table Variable is fixed, but because it's a "variable", it can't be used to import.

Importing with a variable filename is a requirement.

But according to BOL, OPENROWSET, down in the Remarks,

"OPENROWSET does not accept variables for its arguments. "

Doesn't that mean hardcoded input filenames? ( OPENROWSET can use a Linked Server, but that's overkill for a text file.)

I wanted the performance benefits that a Table Variable provides, but it appears I'll have to make do with a temp table.

|||

Denis, stupidity on my part ... Read the post and thought he was trying to pass a table name in the variable. He is trying to pass a table variable... You're post is correct, you can't use table variables as part of a dynamic SQL string (would be nice if you could cuz it would same me from using #temp tables...

|||

you will have to use dynamic SQL

example

declare @.v varchar(500)
declare @.s varchar(500)
select @.v ='select top 6 * from ' + 'TestTextFileImport.txt'

select @.s ='
select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;'', ''' +@.v +' '')'

print @.s
exec (@.s)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Denis, that works great, just open the text file as a rowset! No need for a table at all.

Unfortunately, this raises a security issue. Running the sproc produces this error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Here are excerpts from searching BOL (underlines are mine):

If a nonzero value is set, SQL Server does not allow for ad hoc access

through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider.

When this option is not set, SQL Server also does not allow for ad hoc access.

This option controls the ability of non-administrators to run ad hoc queries. Administrators are not affected by this option.

By default, SQL Server does not allow ad hoc distributed queries

using OPENROWSET and OPENDATASOURCE against providers other than the SQL Native Client OLE DB Provider.

When this option is set to 0, SQL Server allows ad hoc access against other providers.

When this option is not set or is set to 1, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions

to connect to remote data sources that use OLE DB.

OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently.

For any data sources that will be accessed more than several times, define a linked server.

'Ad Hoc Distributed Queries is an Advanced option, which should be changed only by an experienced database administrator or a certified SQL Server technician,

and which require setting show advanced options to 1.

And here is how to do it:

sp_configure 'show advanced options',1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries',1;

GO

RECONFIGURE;

GO

or with version 2.0 of the Microsoft .NET Framework, set the OleDbProviderSettings.DisallowAdHocAccess Property

All that just to read local text files? The server can't differentiate between local and remote files?

Why is a linked server recommended for files that will be read frequently? (thousands per hour, hopefully)

And I thought this was a pretty basic task. Thanks for answering my question.

Tuesday, February 14, 2012

Builtin alternatives to UDF?

I'm using some simple scalar UDFs in expressions that update sets of
around 500,000 rows, but have some worries about possible performance
hits. Question is, are there relativly simple ways of accomplishing the
same inline using the builtin functions?
create function dbo.f_MaxOf(@.x1 float, @.x2 float) returns float
AS
begin
return(case when @.x1 is null then @.x2
when @.x2 is null then @.x1
when @.x1 < @.x2 then @.x2
else @.x1
end)
end
go
----
--
create function dbo.f_IfZero(@.x float, @.minValue float) returns float
AS
begin
return(case when ABS(@.x) < @.minValue then @.minValue
else @.x
end)
end
(simple) Example usage:
UPDATE T set colA = dbo.f_MaxOf(colB, colE) / dbo.f_IfZero(colC + colD,
0.001)
The "real life" code is somewhat more complex ... :-)
An obvious alternative to the 'f_IfZero' function would be something
like
(case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
end)
but this quickly gets ugly when the expression being evaluated is more
complex, and the update has multiple occurences of the same function
that need to be replaced ...
Ideas anyone?
/Erikhave you ever heard of COALESCE function? It might simplify some of you
logic. Lookup in SQL Books on Line. Also, it seems that all of what you are
doing can be done as one update with no functions.
<eox_conceptos@.despammed.com> wrote in message
news:1144239792.905239.41070@.i40g2000cwc.googlegroups.com...
> I'm using some simple scalar UDFs in expressions that update sets of
> around 500,000 rows, but have some worries about possible performance
> hits. Question is, are there relativly simple ways of accomplishing the
> same inline using the builtin functions?
> create function dbo.f_MaxOf(@.x1 float, @.x2 float) returns float
> AS
> begin
> return(case when @.x1 is null then @.x2
> when @.x2 is null then @.x1
> when @.x1 < @.x2 then @.x2
> else @.x1
> end)
> end
> go
> ----
--
> create function dbo.f_IfZero(@.x float, @.minValue float) returns float
> AS
> begin
> return(case when ABS(@.x) < @.minValue then @.minValue
> else @.x
> end)
> end
> (simple) Example usage:
> UPDATE T set colA = dbo.f_MaxOf(colB, colE) / dbo.f_IfZero(colC + colD,
> 0.001)
> The "real life" code is somewhat more complex ... :-)
> An obvious alternative to the 'f_IfZero' function would be something
> like
> (case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
> end)
> but this quickly gets ugly when the expression being evaluated is more
> complex, and the update has multiple occurences of the same function
> that need to be replaced ...
> Ideas anyone?
> /Erik
>|||Farmer wrote:
> have you ever heard of COALESCE function? It might simplify some of you
> logic. Lookup in SQL Books on Line. Also, it seems that all of what you ar
e
> doing can be done as one update with no functions.
>
I'm familiar with coalesce. Of course, the example update could have
been written as:
UPDATE T set colA =
(case when colB is null then colE
when colE is null then colB
when colB < colE then colE
else colB
end) / (case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
end)
but this gets messy and obscures the underlying logic.
Could you clarify what you mean by "can be done as one update with no
functions"? This *is* one update ...|||I guess the following could work instead of "f_IsZero":
COALESCE(NULLIF(ROUND( expression , 3, 1 ), 0.0), 0.001)
... and that just leaves finding a suitable replacement for
"dbo.f_MaxOf"
:-)