Post reply

Variable Issue

  • 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

  • July 28, 2003 at 8:14 am

    #467285

    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]

  • July 28, 2003 at 8:17 am

    #467286

    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

  • July 28, 2003 at 8:26 am

    #467288

    Ex: suppose the number found is 15

    so I need it understand 15.S

  • July 28, 2003 at 8:35 am

    #467289

    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]

  • July 28, 2003 at 8:51 am

    #467294

    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)

  • July 28, 2003 at 9:55 am

    #467313

    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.

  • July 28, 2003 at 11:36 pm

    #467393

    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]

  • July 29, 2003 at 9:08 am

    #467514

    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?

  • July 29, 2003 at 9:16 am

    #467517

    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]

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

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