Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. grahamm

    Erroor with execute of insert sp

    Stored procedures can be multistep. I noticed that you have a rollback transaction, but no begin transaction. Assuming that the sp is a self contained transaction you should alter your sp to include a begin transaction create procedure ...... as begin transaction insert ... if ...
  2. grahamm

    Importing Text File To Sql Server

    I suspect that somewhere in your 31,000 records column 32 has a character string that can not be parsed as an integer number. When you let DTS create the table I would guess that it has built the table with a type of char (or varchar) for col 32. Given that you can load a portion of the file...
  3. grahamm

    subquery null problem

    I'm glad to help... :-)
  4. grahamm

    subquery null problem

    ...The workaround would be insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits) select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate and (siteid=#TempItems.SiteID or...
  5. grahamm

    subquery null problem

    I'm not sure I understand your expectation:- With set ansii_nulls off are you expecting: siteid=#TempItems.SiteID to evaluate to true if siteid is null and #TempItems.SiteID is not null?
  6. grahamm

    Transaction Logs full--Need to Shrink ASAP

    1) Start query analyser. You should find it under: Start-> Programs -> MS SQL Server 7.0 -> Query Analyzer 2) Connect to your SQL Server, 3) If your database is called foo type:- dbcc shrinkdatabase (foo, notruncate) However if you need SQL Server to release the space back to the file...
  7. grahamm

    subquery null problem

    How about trying:- insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits) select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate and (siteid=#TempItems.SiteID or siteid is NULL)...
  8. grahamm

    IF STATEMENT ON A SELECT?

    If you're talking about T-SQL in a stored procedure thenyou can use the builtin @@rowcount variable: select ... from ... where if @@rowcount=0 begin print "No Records Matched" return end
  9. grahamm

    SQL Server Data type problem. Syntax error...

    In this situation implicit conversion is not performed. In other circumstances such as passing a smallint variable to a stored procedure integer parameter, implicit conversion is performed. Take a look at "Data Type Conversion" in the BOL. Also you can use either CONVERT or CAST.
  10. grahamm

    Currency handling..?

    ...another table with one row (can be a temp table) then use a union + non-equi join. If sek is this one row table then: select a.d, a.code,b.code, a.rate/b.rate from (select * from exchange union select * from sek) a , (select * from exchange union select * from sek) b where a.code <> b.code
  11. grahamm

    SQL Server Data type problem. Syntax error...

    Looks like @PaymentTypeCode is the problem. Change: SET @Payment_Types_XML = @Payment_Types_XML + &quot;<PMT_TYP_CD>&quot; + @PaymentTypeCode + &quot;</PMT_TYP_CD>&quot; + &quot;<PMT_TYP_DESC>&quot; + @PaymentTypeDescription + &quot;</PMT_TYP_DESC>&quot; to: SET @Payment_Types_XML =...
  12. grahamm

    Why @@servername is null

    Does this server participate in a replicated environment? We had a similar problem in our replicated environment. If I remember correctly, the replication set up sp's manipulate the sysservers table, and this caused problems... Sorry I couldn't be more specific, but it was about 1 yr ago.
  13. grahamm

    Customer locality..?

    ...from geo z, geo a , geo b, geo c where a.Locality = 'A' and b.Locality = 'B' and c.Locality = 'C' and z.custname *= a.custname and z.custname *= b.custname and z.custname *= c.custname or if you prefer the explicit outer join syntax (SQL-92) then...
  14. grahamm

    SQL Server 2000

    You request is not clear. Could you elaborate. To project out the balance column, the SQL is for example: select Balance from Ledger However I expect this is not what you mean.
  15. grahamm

    Tables Error - Opening tables SQL 7.0

    It may be a bug. You don't say which tool you're using (Enterprise mgr?) however, take a look at these MS articles: http://support.microsoft.com/support/kb/articles/Q273/4/72.ASP http://support.microsoft.com/support/kb/articles/Q193/3/39.ASP
  16. grahamm

    Retrieving max row

    I agree with balves. This should do it, according to the requirements in your post: select CustId,OrderId,max([datetimestamp]) from [order] group by CustId, OrderId It returns the latest entry for each CustId, OrderId combo.
  17. grahamm

    Error Record Deletion Error

    This is a known problem with the Enterprise manager. See artcle Q268505 at MS support http://support.microsoft.com/support/kb/articles/Q268/5/05.ASP The workaround is to use a delete stmt in the query analyzer e.g delete from foo where ...
  18. grahamm

    Disable Indentity Field

    1)You can enable inserts to an identity column via set. i.e. SET IDENTITY_INSERT [database.[owner.]]{table} ON The limitation is a session can only have one table with identity_insert activated at any one time. So your script will have to bracket insert stmts with SET ON and OFF commands. OR...
  19. grahamm

    Sql query into a Procedure with Union

    Before I answer, a question: Are you sure the select will only return one row? Ie if district_id is NULL then state_id is NOT NULL. I ask because your logic appears to be assuming this.
  20. grahamm

    truncate the transaction log

    Using TSQL against a database called foo: exec sp_dboption foo, 'trunc. log on chkpt', 'true' checkpoint This will truncate the inactive part of the transaction log once the checkpoint completes. However understand what you're doing before altering dboptions -- look in BOL under sp_dboption...

Part and Inventory Search

Back
Top