[dba-SQLServer] Thanks for the help

Just replace your SELECT * part of the statement with an explicit list of
the columns you want. Leave out the Row_Number() one and include all the
others.

hth,
Arthur


On 5/14/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> James,
>
> >For your question on pulling records from SQL Server, if you are using
> SQL
> Server 2005, you can use:
>
> With [tblInf] as (select ROW_NUMBER() over (Order By [PKID]) as
> 'ROWNUMBER'
>          ,[PKID]
>      ,[FName]
>      ,[MName]
>      ,[LName]
>      ,[Address]
>      ,[Address2]
>      ,[City]
>      ,[State]
>      ,[Zip]
>      ,[Zip4] from [tblInfutor])
> Select *
> from [tblInf]
> where ROWNUMBER between 10000 and 12000
>
> ***
>
> I used this which works as advertised.  The only issue is that it displays
> the RowNumber column which I need to suppress if at all possible.  These
> are
> being exported out to my address vaildation package and it doesn't want
> the
> line number.
>
> Is it possible to suppress displaying the RowNumber field?
>
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of James
> Barash
> Sent: Monday, May 07, 2007 10:57 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Thanks for the help
>
> John:
>
> Here is a basic call to a stored procedure from VBA with two parameter:
>
> Public Sub SetOrderStatus(ID as Long, Status as Long) Dim conn As
> ADODB.Connection Dim cmd As ADODB.command Set conn = New ADODB.Connection
> conn.ConnectionString = "Insert connection string here"
> conn.Open
> Set cmd = New ADODB.command
> With cmd
>    Set .ActiveConnection = conn
>    .CommandType = adCmdStoredProc
>    .CommandText = "sp_SetOrderStatus"
>    .Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamInput,
> ,
> ID)
>    .Parameters.Append cmd.CreateParameter("Status", adInteger,
> adParamInput, , Status)
>    .Execute
> End With
> Set cmd = Nothing
> conn.Close
> Set conn = Nothing
> End Sub
>
> For your question on pulling records from SQL Server, if you are using SQL
> Server 2005, you can use:
>
> With OrdersA as (select [Order Number], ROW_NUMBER() over (Order By [Order
> ID]) as 'ROWNUMBER' from Orders)
> Select * from OrdersA where ROWNUMBER between 100 and 200
>
> Hope that helps.
>
> James Barash
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>