Variable Issue

Question

Variable Issue

NelsonVere

Ten Centuries

Points: 1280
More actions
July 28, 2003 at 7:19 am

#176120

I have this command line and I need said that '@@' can be any number between 01 and 99
how I do that ?
thanks
this is the command i'm trying.
select @dateS = substring(datex,1,2)+substring(datex,3,2)+substring(datex,5,2)+'00'+'.'+@@+'S' from dates

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Nelson,

quote:
I have this command line and I need said that '@@' can be any number between 01 and 99
how I do that ?
thanks
this is the command i'm trying.
select @dateS = substring(datex,1,2)+substring(datex,3,2)+substring(datex,5,2)+'00'+'.'+@@+'S' from dates

are you looking for a way to assign a random number to @@ ?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NelsonVere Ten Centuries Points: 1280 More actions · Answer 2

nao, nao e' randon number

the number can be something between 01 and 99

how I do that, how I change that @@ to make sql understand that can be any number between 01 and 99.

thanks

NelsonVere Ten Centuries Points: 1280 More actions · Answer 3

Ex: suppose the number found is 15

so I need it understand 15.S

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
Ex: suppose the number found is 15
so I need it understand 15.S

so, you have declared @@? As what? I guess SQL Server will interpret as int unless otherwise forced to.

But what are you doing?

You're stripping days, months, and years from a date value?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NelsonVere Ten Centuries Points: 1280 More actions · Answer 5

What I'm doing is.

other server creates a TXT file from all records and save it as ex: file name 07300300.15S, so what I need to do is look for that file and upload it into SQL server

but that '15S' can be 25S 35S wherever number between 01 and 99.

declare @dateS char(24)

declare @@ char(2)

select @dateS = substring(datex,1,2)+substring(datex,3,2)+substring(datex,5,2)+'00'+'.'+@@+'S' from dates

declare @file1 char(20)

select @dateS = 'e:\smdr\'+@dateS

select @file1 = @dateS

declare @sql varchar(100)

set @sql ='BULK INSERT MIR FROM ''' + @file1 + ''' with (CODEPAGE = ''RAW'',ROWTERMINATOR = ''\n'',TABLOCK)'

exec (@SQL)

NPeeters SSChampion Points: 12536 More actions · Answer 6

You want something like a wildcard to match filenames. Don't think that is possible with BULK INSERT.

The only way I can think of is using xp_cmdshell, checking if a file exists and returning a value accordingly.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi Nelson,

quote:
What I'm doing is.
other server creates a TXT file from all records and save it as ex: file name 07300300.15S, so what I need to do is look for that file and upload it into SQL server
but that '15S' can be 25S 35S wherever number between 01 and 99.
declare @dateS char(24)
declare @@ char(2)
select @dateS = substring(datex,1,2)+substring(datex,3,2)+substring(datex,5,2)+'00'+'.'+@@+'S' from dates
declare @file1 char(20)
select @dateS = 'e:\smdr\'+@dateS
select @file1 = @dateS
declare @sql varchar(100)
set @sql ='BULK INSERT MIR FROM ''' + @file1 + ''' with (CODEPAGE = ''RAW'',ROWTERMINATOR = ''\n'',TABLOCK)'
exec (@SQL)

I agree with Noel.

You should utilize xp_cmdshell to look for the file, and if it exists load it

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gregj Old Hand Points: 346 More actions · Answer 8

Here is some code that will create a temporary table with the filenames that match a filespec. (Note that the filename do NOT contain the path.) You can use any of several methods (cursor, loop, etc.) to process each of the files in the table.

set nocount on

create table #tblFile ( sFilename varchar(255) )

insert into #tblFile exec master..xp_cmdshell 'dir /b c:\*.*'

select * from #tblFile

drop table #tblFile

set nocount off

On another subject...can someone tell me a good reason for not being able to EXEC records into the new "table" type variable?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hi there,

today I've stumbled over an (undocumented?) xp. XP_DIRTREE and some sample code



CREATE TABLE #dir(datei_dir varchar(255), tiefe int, ist_datei int)  

 INSERT INTO #dir  

  EXEC master..xp_dirtree 'C:\winnt', 1, 1  

 SELECT datei_dir As Exe_Datei FROM #dir   

  WHERE ist_datei = 1  

  AND   RIGHT(datei_dir, 3) = 'exe'  

  ORDER BY datei_dir  

 DROP TABLE #dir

Maybe this one helps

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]