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. Jamfool

    Creating multiple XML elements from related table

    here is a quick attempt. /* DROP TABLE person DROP TABLE phone CREATE TABLE person (id int,[last] VARCHAR(50),[first] VARCHAR(50)) CREATE TABLE phone (id int,personid INT,phonetype INT,phonenumber VARCHAR(50)) INSERT INTO person VALUES ( 1, 'ZZZ', 'AAA' ) INSERT INTO person VALUES ( 2...
  2. Jamfool

    create user logins using script

    There should be a login to connect to the database instance (via nt or sql authentication). In order to access a particular database that login is then mapped to a database user. The N merely lets you/sqlserver now that the string is a unicode type. USE [master] GO --First Create the login...
  3. Jamfool

    User Logins Report

    I am not sure there is anything that will allow you to do this out of the box. I have set up a server wide ddl trigger to capture developer changes ;p SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO USE [xxxx] --replace XXXX for insert also ;p CREATE TABLE [dbo].[DDL_Server_Log](...
  4. Jamfool

    User Logins Report

    ...(order by obj_name,object_type ) )%2 as l1 , (dense_rank() over (order by obj_name,object_type,start_time ))%2 as l2 , * from @temp_trace where object_type not in (21587) -- don't bother with auto-statistics as it generates too much noise order by...
  5. Jamfool

    Block by application or log by application name

    Another option would be to lock the box down via a firewall change e.g only connections from ThisAppBox can get to ThisDatabaseBox. Again if your users can get on the AppBox you have problems ;p
  6. Jamfool

    Help with Date Conversion error

    no blank dates wont cause the issue. do you still get an error if you run: SELECT CONVERT(datetime, invo.[grn posted], 103), CONVERT(datetime, invo.[statdeldte], 103) FROM dbo.ZSPO AS invo
  7. Jamfool

    Help with Date Conversion error

    it looks like you have at least 2 rows of bad dates, it looks like we dont have the bits we need tho as we should have four columns per row. need to change it too ISDATE([GRN Posted]) AS Expr1, [GRN Posted], ISDATE(StatDelDte) AS Expr2 [StatDelDte], as it looks from the 0 that in the 3rd...
  8. Jamfool

    Help with Date Conversion error

    copy and paste getting the better of me there in my last query. couple of duplicate lines ;p double check that there are only valid dates in your table, even tho they may look valid: SELECT ISDATE(invo.[grn posted]) , invo.[grn posted], ISDATE(invo.[statdeldte]) invo.[statdeldte], FROM...
  9. Jamfool

    Help with Date Conversion error

    wow, thats some nasty code. assuming all the comparisons are correct, and it is structured the way it should be ( bit hard to read ;p ), it will probably be some rogue data. try the following and see if it errors, at least it will guide you. SELECT...
  10. Jamfool

    SQL Between Range

    if you take off the quotes,sql will ask you what it is, and most likely tell you that the column isnt there: SELECT 'A10730' SELECT A10730 ... In my initial post I have stripped off the 'A' (although this will all be dependant on your data, as to what you need to do) I am then comparing the...
  11. Jamfool

    SQL Between Range

    ...left etc. DECLARE @tbl TABLE (items VARCHAR(50)) INSERT INTO @tbl VALUES ('A10710') INSERT INTO @tbl VALUES ('A10711') INSERT INTO @tbl VALUES ('A10730') INSERT INTO @tbl VALUES ('A1072') INSERT INTO @tbl VALUES ('A1073') SELECT * FROM @tbl WHERE REPLACE(items,'A','') BETWEEN 10710 AND 10730
  12. Jamfool

    Date Range Choosing Minimum Value

    if you want to go back 13wks: [code] WHERE dt BETWEEN DATEADD(wk,-13,@varDT) AND @varDT [code] if you do not want to go into the previous year, then: [code] AND YEAR(dt) >= YEAR(@varDT) [code] The other thing you will need to tweak will be when you consider to be the start of your week.
  13. Jamfool

    Need a count statement to include 0

    glad you got it sorted :)
  14. Jamfool

    Need a count statement to include 0

    sorry havent used it for a long time. its looks quite a generic error. have you tried creating a new report and trying to pull back the data? just to check its not a binding issue... at least if that works you can narrow it down ;p
  15. Jamfool

    Need a count statement to include 0

    ...be able to remove this depending on speed. try below, and also see what type of duplication you getting. SELECT DISTINCT ( SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN...
  16. Jamfool

    Need a count statement to include 0

    ...otherwise it will group based on the date time part as well. SELECT DISTINCT ( SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN dbo.History_Entry AS HE ON...
  17. Jamfool

    Need a count statement to include 0

    ...Resolved') INSERT INTO History_Entry VALUES (GETDATE()+1,1,'BLAH') INSERT INTO tech VALUES (5,'Joe','Bloggs') SELECT DISTINCT ( SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN dbo.History_Entry AS HE ON...
  18. Jamfool

    Need a count statement to include 0

    ...INSERT INTO History_Entry VALUES (GETDATE(),1,'to Resolved') INSERT INTO History_Entry VALUES (GETDATE()+1,1,'BLAH') SELECT COUNT(*) AS Total_Resolved, CONVERT(varchar(6),HE.Entry_date,112) AS Yr_wk, C.first_name + ' ' + C.Last_name AS Tech FROM dbo.tech AS C INNER JOIN...
  19. Jamfool

    SQL Server Reporting Service

    A stored procedure can provide source data for a report. It could also repopulate data that the report was using. It can not however start, or display ssrs reports.
  20. Jamfool

    Formatting Date and Time

    you picked a strange format :P There are several ways to achieve your end goal concatenating the date parts and adding leading zeros etc. I would suggest if it is something that you will use in several places that you create it as a function. Here is one way: DECLARE @dt datetime set @dt...

Part and Inventory Search

Back
Top