Search Results

Search found 33316 results on 1333 pages for 'sql team'.

Page 132/1333 | < Previous Page | 128 129 130 131 132 133 134 135 136 137 138 139  | Next Page >

  • Best way to randomly select rows *per* column in SQL Server

    - by LesterDove
    A search of SO yields many results describing how to select random rows of data from a database table. My requirement is a bit different, though, in that I'd like to select individual columns from across random rows in the most efficient/random/interesting way possible. To better illustrate: I have a large Customers table, and from that I'd like to generate a bunch of fictitious demo Customer records that aren't real people. I'm thinking of just querying randomly from the Customers table, and then randomly pairing FirstNames with LastNames, Address, City, State, etc. So if this is my real Customer data (simplified): FirstName LastName State ========================== Sally Simpson SD Will Warren WI Mike Malone MN Kelly Kline KS Then I'd generate several records that look like this: FirstName LastName State ========================== Sally Warren MN Kelly Malone SD Etc. My initial approach works, but it lacks the elegance that I'm hoping the final answer will provide. (I'm particularly unhappy with the repetitiveness of the subqueries, and the fact that this solution requires a known/fixed number of fields and therefore isn't reusable.) SELECT FirstName = (SELECT TOP 1 FirstName FROM Customer ORDER BY newid()), LastName= (SELECT TOP 1 LastNameFROM Customer ORDER BY newid()), State = (SELECT TOP 1 State FROM Customer ORDER BY newid()) Thanks!

    Read the article

  • not including null values in sql join

    - by Ashanti
    Hi, I have two tables CustomerAddress(CustomerId, City, Country) and CustomerTransactions(TransactionId, CustomerId, CustomerContact). Here are the values in the tables: For CustomerAddress: 1001, El Paso, USA 1002, Paris, France 1003, Essen, Germany For CustomerTransactions: 98, 1001, Phillip 99, 1001, NULL 100, 1001, NULL 101, 1003, Carmen 102, 1003, Carmen 103, 1003, Lola 104, 1003, NULL 105, 1002, NULL I'm trying to join both tables and have the following result set: 1001, El Paso, USA, Phillip 1002, Paris, France, (empty string) 1003, Essen, Germany, Carmen 1003, Essen, Germany, Lola It seems like a simple join but I'm having trouble coming up with the above result set. Please help. Thanks.

    Read the article

  • SQL Query with multiple values in one column

    - by lp1
    I've been beating my head on the desk tring to figure this one out. I have a table that stores job information, and reasons for a job not being completed. The reasons are numeric,01,02,03,etc. You can have two reason for a pending job. If you select two reasons, they are stored in the same column, seperated by a comma. This is anExample from the JOBID table: Job_Number User_Assigned PendingInfo 1 user1 01,02 Now, there is another table named Pending, that stores what those values actually represent. 01=Not enough info, 02=Not enough time, 03=Waiting Review. Example: Pending_Num PendingWord 01 Not Enough Info 02 Not Enough Time What I'm trying to do is query the database to give me all the job numbers, users, pendinginfo, and pending reason. I can break out the first value, but can't figure out how to do the second. What my limited skills have so far: *select Job_number,user_assigned,SUBSTRING(pendinginfo,0,3),pendingword from jobid,pending where SUBSTRING(pendinginfo,0,3)=pending.pending_num and pendinginfo!='00,00' and pendinginfo!='NULL'* What I would like to see for this example would be: Job_Number User_Assigned PendingInfo PendingWord PendingInfo PendingWord 1 User1 01 Not Enough Info 02 Not Enough Time Thanks in advance

    Read the article

  • Returning partial address matches and mismatch position using L2S or SQL

    - by peter3
    I need to implement a method that takes an address split up into individual parts and returns any matching items from an address table. If no matches are found, I want to be able to return a value indicating where it failed. Each input param has a corresponding field in the table. The signature would look something like this: List<Address> MatchAddress(string zipCode, string streetName, string houseNumber, string houseLetter, string floor, string appartmentNo, out int mismatchPosition) { // return matching addresses // if none found, return the position where it stopped matching // zipCode is position 0, appartmentNo is position 5 // // an empty param value indicates "don't check" } I know I can construct the method such that I start with all the parameters, execute the query and then remove param by param (from the right side) until either a match is found or I run out of parameters, but can I construct a query that is more effective than that, i.e minimizing the number of calls to the db, maybe even as a single call?

    Read the article

  • SQL Get UID when Group by

    - by Quandary
    I do a select from table [V_RPT_BelegungKostenstelleDetail] WHERE SO_UID = '7C7035C8-56DD-4A44-93CC-F16FD66280A3' AND GB_UID = '4FF1B0EE-A5DD-4699-94B7-760922666CE2' AND GS_UID = '1188759A-54E1-4323-8BF2-85E71B3C796E' AND RM_UID = '088C3559-6E6E-468A-9554-6740840FCBA1' AND NA_UID= '96A2A8DB-8C83-4C60-9060-F0F55719AF5C' GROUP BY KST_UID How can I get SO_UID? It is the same everywhere, but I get an error when I try to get SO_UID with the return values... SO_UID is not necessarely given like '7C7035C8-56DD-4A44-93CC-F16FD66280A3' here, so I can't just add it manually as string.

    Read the article

  • FTS: Searching across multiple fields 'intelligently'

    - by Wild Thing
    Hi, I have a SP using FTS (Full Text Search). I want searches across multiple fields, 'intelligently' ranking results based on the weights I assign. Consider a search on a view fetching data from tables: Book, Author and Genre. Now, I want the searcher to be able to do: "Ludlum Fiction", "Robert Ludlum Bourne", "Bourne Ludlum", etc. Unfortunately, the only way I have been able to do that at present is this: http://pastebin.com/fdce11ff This is pretty bad, because I am manually breaking up the search string. I know I am doing this completely the wrong way, but can't figure out the right way to search across multiple fields in FTS. Can somebody help please?

    Read the article

  • SQl to list rows if not in another table

    - by SmartestVEGA
    I have the following query which have 1000 rows select staffdiscountstartdate,datediff(day,groupstartdate,staffdiscountstartdate), EmployeeID from tblEmployees where GroupStartDate < '20100301' and StaffDiscountStartDate > '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)>1 order by staffdiscountstartdate desc i have the following query which have 400 rows: ie the employees in tblemployees and in tblcards select a.employeeid,b.employeeid from tblEmployees a,tblCards b where GroupStartDate < '20100301' and StaffDiscountStartDate > '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)>1 and a.employeeid=b.employeeid How to list the employees which is there in tblemployees and not in tblcards? ie is 1000-400 = 600 rows ???

    Read the article

  • sql exception arithmetic overflow?

    - by MyHeadHurts
    In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird [ SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.HasMoreRows() +157 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282 System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19 System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222 System.Data.DataTable.Load(IDataReader reader) +14 ( @YearToGet int, @current datetime, @y int, @search datetime ) AS SET @YearToGet = 2006; WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(InsAmount) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search)) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ), q_01 as ( select DIVISION , DYYYY , sum(PARTY) as YEPAX , sum(InsAmount) as YESales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year group by DIVISION, DYYYY , years.year having DYYYY = years.year ), q_02 as ( select DIVISION , DYYYY , sum(PARTY) as CurrentPAX , sum(InsAmount) as CurrentSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,@current) and DYYYY = (year( getdate() )) group by DIVISION, DYYYY ) select a.DIVISION , a.DYYYY , asofPAX , asofSales , YEPAX , YESales , CurrentPAX , CurrentSales ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales, CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION) JOIN Years as d on (b.dyyyy = d.year) where A.DYYYY <> (year( getdate() )) order by a.DIVISION, a.DYYYY ;

    Read the article

  • filling in the holes in the result of a query

    - by ????? ????????
    my query is returning: +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla | +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 13 | | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 | | 0 | 0 | 0 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 8 | 37 | 29 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 374 | 30 | | 0 | 0 | 1 | 0 | 78 | 2 | 4 | 8 | 57 | 169 | 116 | 602 | 31 | | 156 | 255 | 79 | 75 | 684 | 325 | 289 | 194 | 407 | 171 | 584 | 443 | 32 | | 1561 | 2852 | 2056 | 796 | 2004 | 1755 | 879 | 1052 | 1490 | 1683 | 2532 | 2381 | 33 | | 4167 | 3841 | 4798 | 3399 | 4132 | 5849 | 3157 | 4381 | 4424 | 4487 | 4178 | 5343 | 34 | | 5472 | 5939 | 5768 | 4150 | 7483 | 6836 | 6346 | 6288 | 6850 | 7155 | 5706 | 5231 | 35 | | 5749 | 4741 | 5264 | 4045 | 6544 | 7405 | 7524 | 6625 | 6344 | 5508 | 6513 | 3854 | 36 | | 5464 | 6323 | 7074 | 4861 | 7244 | 6768 | 6632 | 7389 | 8077 | 8745 | 6738 | 5039 | 37 | | 5731 | 7205 | 7476 | 5734 | 9103 | 9244 | 7339 | 8970 | 9726 | 9089 | 6328 | 5512 | 38 | | 7262 | 6149 | 8231 | 6654 | 9886 | 9834 | 9306 | 10065 | 9983 | 9984 | 6738 | 5806 | 39 | | 5886 | 6934 | 7137 | 6978 | 9034 | 9155 | 7389 | 9437 | 9711 | 8665 | 6593 | 5337 | 40 | +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ as you can see the BLA column starts from 13. i want it to start from 1, then 2, then 3 etc......I do not want any gaps in the data. The reason there are gaps is because all of the months are 0 for that specific bla how do i get the result set to include ALL values for BLA, even ones that will yield 0 for the months? here are the desired results: +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla | +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | | … | … | … | … | … | … | … | … | … | … | … | … | … | +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ here's my query: WITH CTE AS ( select sum(case when datepart(month,[datetime entered]) = 1 then 1 end) as Jan, sum(case when datepart(month,[datetime entered]) = 2 then 1 end) as Feb, sum(case when datepart(month,[datetime entered]) = 3 then 1 end) as Mar, sum(case when datepart(month,[datetime entered]) = 4 then 1 end) as Apr, sum(case when datepart(month,[datetime entered]) = 5 then 1 end) as May, sum(case when datepart(month,[datetime entered]) = 6 then 1 end) as Jun, sum(case when datepart(month,[datetime entered]) = 7 then 1 end) as Jul, sum(case when datepart(month,[datetime entered]) = 8 then 1 end) as Aug, sum(case when datepart(month,[datetime entered]) = 9 then 1 end) as Sep, sum(case when datepart(month,[datetime entered]) = 10 then 1 end) as Oct, sum(case when datepart(month,[datetime entered]) = 11 then 1 end) as Nov, sum(case when datepart(month,[datetime entered]) = 12 then 1 end) as Dec, DATEPART(yyyy,[datetime entered]) as [Year], bla= CASE WHEN datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108) >191 THEN 192 ELSE datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108) END --,datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE)) AS Sort_Days, --DATEPART(hour, [datetime completed] ) AS Sort_Hours from [TurnAround] group by datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108), DATEPART(yyyy,[datetime entered]) , [datetime entered] --[DateTime Completed] ) SELECT ISNULL(SUM(Jan),0) Jan, ISNULL(SUM(Feb),0) Feb, ISNULL(SUM(Mar),0) Mar, ISNULL(SUM(Apr),0) Apr, ISNULL(SUM(May),0) May, ISNULL(SUM(Jun),0) Jun, ISNULL(SUM(Jul),0) Jul, ISNULL(SUM(Aug),0) Aug, ISNULL(SUM(Sep),0) Sep, ISNULL(SUM(Oct),0) Oct, ISNULL(SUM(Nov),0) Nov, ISNULL(SUM(Dec),0) Dec, [year], --,Sort_Hours, --Sort_Days, A.RN Bla FROM ( SELECT *, RN=ROW_NUMBER() OVER(ORDER BY object_id) FROM sys.all_objects) A LEFT JOIN CTE B ON A.RN = CASE WHEN B.Bla > 191 THEN 192 ELSE B.Bla END WHERE A.RN BETWEEN 1 AND 192 GROUP BY A.RN,[year]

    Read the article

  • Credentials work for SSMS but not (ODBC) LogParser script

    - by justSteve
    Via SSMS I'm able to connect and navigate the server/db in question. but trying to connect via a logparser script the same credentials fail. I'm trying to execute this from the same box on which the server's running. the username is owner/dbo of the db. The db has mixed mode authentication. [linebreaks for clarity] C:\TTS\tools\LogParserc:\tts\tools\logparser\logparser file:c:\tts\tools\logparser\errors2SQL.sql?source="C:\inetpub\logs\LogFiles\W3SVC8\u_ex100521.log" -i:IISW3C -o:SQL -createTable:ON -oConnString:"Driver={SQL Server Native Client 10.0};Server=servername\SQLEXPRESS;db=Tter;uid=logger2;pwd=foo" -stats:OFF Task aborted. Error connecting to ODBC Server SQL State: 28000 Native Error: 18456 Error Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'logger2'. C:\TTS\tools\LogParser

    Read the article

  • SQL Server IS_NULLABLE

    - by J Harley
    Good Morning, Just a quick question what this field actually means? I am trying to create an export script which follows this standard: lname varchar(30) **NOT NULL**, So if last name is_nullable=yes then would I put NULL rather than NOT NULL at the *'d code. Many Thanks, Joel

    Read the article

  • Simple regex question (C#, MS SQL)

    - by Vytas999
    Hello, I have some Regex, it looks like this: string regexForDrop = @"^((%27)|'|(\-\-))\s*(d|%64|%44)(r|%72|%52)(o|%6F|%4F)(p|%70|%50)$"; It works fine, when i write to the input "--drop", but it does not works, when i write "drop table users" or something like that. I need that it would be working, no matter what comes after "--drop". How i can implement that? Thanks

    Read the article

  • SQL Server 2008 FTS CONTAINSTABLE Not Returning More Than Five Rows

    - by Elijah Glover
    I have a single table called "Indexes", it contains one nvarchar and three ntext columns (all Full Text Indexes). Index is up to date. CONTAINSTABLE(Indexes, *), 'test', 5) //5 results No matter what I change the above keyword too, it only returns the first 3-5 results. It should roughly return 90-120 results, for the above query. SELECT count(*) FROM Indexes WHERE [Description] like '%test%' //122 results How would I start to troubleshoot this problem?

    Read the article

  • Weird SQL Server 2005 Collation difference between varchar() and nvarchar()

    - by richardtallent
    Can someone please explain this: SELECT CASE WHEN CAST('iX' AS nvarchar(20)) > CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END, CASE WHEN CAST('iX' AS varchar(20)) > CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END Results: 0 1 SELECT CASE WHEN CAST('i' AS nvarchar(20)) > CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END, CASE WHEN CAST('i' AS varchar(20)) > CAST('-' AS varchar(20)) THEN 1 ELSE 0 END Results: 1 1 On the first query, the nvarchar() result is not what I'm expecting, and yet removing the X make the nvarchar() sort happen as expected. (My original queries used the '' and N'' literal syntax to distinguish varchar() and nvarchar() rather than CAST() and got the same result.) Collation setting for the database is SQL_Latin1_General_CP1_CI_AS.

    Read the article

  • T-SQL query with date range

    - by Moo
    Hi, I have a fairly weird 'bug' with a simple query, and I vaguely remember reading the reason for it somewhere a long time ago but would love someone to refresh my memory. The table is a basic ID, Datetime table. The query is: select ID, Datetime from Table where Datetime <= '2010-03-31 23:59:59' The problem is that the query results include results where the Datetime is '2010-04-01 00:00:00'. The next day. Which it shouldn't. Anyone? Cheers Moo

    Read the article

  • Addresses stored in SQL server have many small variations(errors)

    - by MAW74656
    I have a table in my database which stores packing slips and their information. I'm trying to query that table and get each unique address. I've come close, but I still have many near misses and I'm looking for a way to exclude these near duplicates from my select. Sample Data CompanyCode CompanyName Addr1 City State Zip 10033 UNITED DIE CUTTING & FINISHIN 3610 HAMILTON AVE CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE. CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVENUE CLEVELAND Ohio 44114 10033 UNITED DIECUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44144 10033 UNITED FINISHING 3610 HAMILTON AVE CLEVLAND Ohio 44114 10033 UNITED FINISHING & DIE CUTTING 3610 HAMILTON AVE CLEVELAND Ohio 44114 And all I want is 1 record. Is there some way I can get the "Average" record? Meaning, if most of the records say CLEVELAND instead of CLEVLAND, I want my 1 record to say CLEVELAND. Is there any way to par this data down to what I'm looking for? Desired Output CompanyCode CompanyName Addr1 City State Zip 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44114

    Read the article

  • SQL Distinct keyword in assignment statement

    - by Brandi
    I have a query that works: DECLARE @ProductID int SET @ProductID = '1234' SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID) AND a.ProductID != @ProductID It returns a list of 12 product numbers, all unique. I need to store these results in a variable, comma separated, because that's what 3rd party stored procedure needs. So I have this: DECLARE @ProductID int DECLARE @relatedprods varchar(8000) SET @ProductID = '1234' SET @relatedprods = '' SELECT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', ' FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID) AND a.ProductID != @ProductID SELECT @relatedprods Now, none of these are distinct, but it is returning 12 rows. Now I add the 'distinct' back in, like in the first query: DECLARE @ProductID int DECLARE @relatedprods varchar(8000) SET @ProductID = '1234' SET @relatedprods = '' SELECT DISTINCT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', ' FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID) AND a.ProductID != @ProductID SELECT @relatedprods Only one product is returned in the comma separated list! Does 'distinct' not work in assignment statements? What did I do wrong? Or is there a way to get around this? Thanks in advance!

    Read the article

  • help optimize sql query

    - by msony
    I have tracking table tbl_track with id, session_id, created_date fields I need count unique session_id for one day here what i got: select count(0) from ( select distinct session_id from tbl_track where created_date between getdate()-1 and getdate() group by session_id )tbl im feeling that it could be better solution for it

    Read the article

  • How do I add values from two seperate querys in SQL

    - by fishhead
    Below is my attempt at addinf two values from seperate select statments...it's not working...I can't see why. Looking for some direction thanks select (v1.Value + v2.Value) as total from ( (Select Max(Value) as [Value] from History WHERE Datetime>='Apr 11 2010 6:05AM' and Datetime<='Apr 11 2010 6:05PM' and Tagname ='RWQ272017DTD' ) as v1 (Select Max(Value) as [Value] from History WHERE Datetime>='Apr 11 2010 6:05AM' and Datetime<='Apr 11 2010 6:05PM' and Tagname ='RU282001DTD' ) as v2 ) boy do I feel foolish...I asked the same question a few days ago...now I can't delete this.

    Read the article

  • Passing Binary Data to a Stored Procedure in SQL Server 2008

    - by Joe Majewski
    I'm trying to figure out a way to store files in a database. I know it's recommended to store files on the file system rather than the database, but the job I'm working on would highly prefer using the database to store these images (files). There are also some constraints. I'm not an admin user, and I have to make stored procedures to execute all the commands. This hasn't been of much difficulty so far, but I cannot for the life of me establish a way to store a file (image) in the database. When I try to use the BULK command, I get an error saying "You do not have permission to use the bulk load statement." The bulk utility seemed like the easy way to upload files to the database, but without permissions I have to figure a work-a-round. I decided to use an HTML form with a file upload input type and handle it with PHP. The PHP calls the stored procedure and passes in the contents of the file. The problem is that now it's saying that the max length of a parameter can only be 128 characters. Now I'm completely stuck. I don't have permissions to use the bulk command and it appears that the max length of a parameter that I can pass to the SP is 128 characters. I expected to run into problems because binary characters and ascii characters don't mix well together, but I'm at a dead end... Thanks

    Read the article

  • How are SQL Server CALs counted?

    - by Sam
    Running a SQL Server, as far as I understand it, you need one CAL for every user who connects to the database server. But what happens if the only computer which is accessing the SQL Server is the server running your business layer? If, for example, you got 1 SQL Server and 1 Business logic server, and 100 Clients who all just query and use the business logic server. No client is using the SQL Server directly, no one is even allowed to contact it. So, since there is only one computer using the SQL server, do I need only 1 CAL??? I somehow can't believe this would count as only 1 CAL needed for the SQL Server, but I would like to know why not.

    Read the article

  • Exec problem in SQL Server 2005

    - by IordanTanev
    Hi, I have the situation where i have two databases with same structure. The first have some data in its data tables. I need to create a script that will transfer the data from the first database to the second. I have created this script. DECLARE @table_name nvarchar(MAX), @query nvarchar(MAX) DECLARE @table_cursor CURSOR SET @table_cursor = CURSOR FAST_FORWARD FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES OPEN @table_cursor FETCH NEXT FROM @table_cursor INTO @table_name WHILE @@FETCH_STATUS = 0 BEGIN SET @query = 'INSERT INTO ' + @table_name + ' SELECT * FROM MyDataBase.dbo.' + @table_name print @query exec @query FETCH NEXT FROM @table_cursor INTO @table_name END CLOSE @table_cursor DEALLOCATE @table_cursor The problem is that when I run the script the "print @query" statement prints statement like this INSERT INTO table SELECT * FROM MyDataBase.dbo.table When I copy this and run it from Management studio it works fine. But when the script tries to run it with exec I get this error Msg 911, Level 16, State 1, Line 21 Could not locate entry in sysdatabases for database 'INSERT INTO table SELECT * FROM MPDEV090314'. No entry found with that name. Make sure that the name is entered correctly. Hope someone can tell me whot is wront with this. Best Regards, Iordan Tanev

    Read the article

< Previous Page | 128 129 130 131 132 133 134 135 136 137 138 139  | Next Page >