Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
Hi ,I am looking for meta-information about the return recordset of astored-procedure. The procedure returns a resultset that contains columns ofmore tables joined together. In all tables, I use, there is aRecord-Creation-Timestamp-Attribute. When joining two or more tables theseattribute-names appear in ther resultset buti found no way to distinguish them.I there a way to retrieve meta-information about the result-recordset ofsuch a stored-procedure?here some details:the tables=======CREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[FK_Tab2ID] [int] NULL ,[CreatedAt] [datetime] NULL )CREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (35) NULL ,[CreatedAt] [datetime] NULL)the stored-procedure:===============CREATE PROCEDURE dbo.sp_Test_RetrieveData@ID intASSET NOCOUNT ONselect * from table1 inner join table2 on (FK_Tab2ID = Table2ID)where table1.ID = @IDGOthe resultset:==========Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt(the attribute CreatedAt appears twice.)-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations) FROM tblColumnNames
SELECT t1.title,t1.record_id, (SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1, (SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2, (SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3 FROM table1 t1
Hi all.. i have a question about TSQL, how it is possible to write this querry correctly:
Code SnippetSELECT dtEvent.EventNo as "Event ID:", FROM LIKE '%dtEvent%' AS dtEvent,WHERE dtMachine.id = dtEvent.agentmachineid
in the "FROM" part is a problem....LIKE '%%' isn't correct, but i don't know how can i write this query correctly... by the way: for example just "dtEvent" is known ...but not the fullname like "dtEvent_XXXXXXXX"
I have a table with a DateTime column called 'cutoff'. Is it possible to grab the TOP 1 record ordered by the cutoff column based on the current date?SELECT TOP 1 * FROM mytable WHERE cutoff >= GetDate() ORDER BY cutoff...works only if there is a cutoff < the current date. If GetDate() is greater then latest cutoff date it returns nothing. In this case, is it possible to just return the latest record? I'd like to keep this to 1 query if possible? :-)
I was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData) { bool first = true; dsStrongTypeDataSet data = new dsStrongTypeDataSet (); string selectStatement = "Select * from tblMyTable where "; //There is one of these if statements for each parameter if ((inData.Paramater1 != null)) { if (!first)//not as important in this section of code, //but there are areas where there are up to 30 parameters selectStatement += " and "; if (inData.SrchParameter1 == SearchType.Fuzzy) selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') "; else if (inData.SrchParameter1 == SearchType.Literal) selectStatement += "Column1 = '" + inData.Parameter1 + "'"; first = false; } //More if statements like above for EVERY parameter possible SqlCommand selectCommand = new SqlCommand(selectStatement, CorrectSqlConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand); dataAdapter.Fill(data, "tblMyTable"); return data; }
Hi, I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey BETWEEN 1 AND 90000000 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550 AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where. Here is my stored proc: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author:Name -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName] @VisitID int, @OfficerName varchar(100) output AS BEGIN -- Variables Declare @OfficerID int --Declare @OfficerName varchar(100) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Select @OfficerID = (Select top 1 OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)
IF (@OfficerID Is Null) BEGIN -- Get the None Registered Officer Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID) print 'Got unregistered Officer ' + @OfficerName END ELSE BEGIN -- Get the Registered Officer Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID) print 'Got Registered Officer ' + @OfficerName END END
And here is the code I am using to access this proceedure:1 Dim blah As String 2 Dim conn2 As New SqlConnection() 3 Dim cmd2 As New SqlCommand() 4 conn2.ConnectionString = _ConnString 5 cmd2.Connection = conn2 6 cmd2.CommandType = CommandType.StoredProcedure 7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName" 8 9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID"))) 10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100) 11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output 12 Try 13 conn.Open() 14 cmd.ExecuteNonQuery() 15 blah = cmd.Parameters("@OfficerName").Value.ToString() 16 17 Catch ex As Exception 18 Throw ex 19 Finally 20 conn.Close() 21 End Try 22
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.
I have 2 views (ITMISSUES,USR_VIEW_RPTS) I want to retreive (Count of Issues Alis name)-noofissues,total issqty, total (sum of Total Receipt Qty Alis name)- rctqty between a range of items and between a range of dates.How can i do it.
SELECT COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY, MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY) AS RCQTY from ITMISSUES INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR WHERE ITMISSUES.ITEMNMBR BETWEEN 'xyz' AND 'xyz' AND ITMISSUES.DOCDATE BETWEEN '2004-08-01' AND '2004-08-31'
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode 1 999 Barry White BW13 8GS 2 999 <null> <null> BW13 9GS 3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode 999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
Hello All,Could someone help provide a query that I can run todetermine the primary key and unique columns for anygiven table ?Thanks in Advance,Akintoye
Hi,I was just helping a coworker optimize a query. He had two versions:one which used UNION for each value for which he was tallying resultsand another query which used GROUP BY. Here is an aproximation of whatthey were:Query #1:---------SELECT 12 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 5UNIONSELECT 13 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 6Query #2:---------SELECT R.row_num AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1INNER JOIN Report_Rows R ON R.col2 = T.col2WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateGROUP BY ALL R.row_numORDER BY R.row_numThe Report_Rows table in this case would have had two rows mapping row12 to a column value of 5 and row 13 to a column value of 6. Thesecond query was performing horribly until I noticed the ALL keywordin the GROUP BY, which I didn't think was necessary. When I removedthat it performed more like I expected it to perform.Before I had noticed that I was scouring over the query plans andcouldn't figure out why in one instance the query optimizer chose tojoin My_Table and Another_Table, yet when the ALL keyword was there itchose to return all of the records from Another_Table (a rather largetable) and join it to the Report_Rows table before then joining toMy_Table, which had the date criteria in the WHERE clause.So, if you've read this far without giving up...1. Why would the ALL keyword cause this? I understand thefunctionality of ALL, but I still don't see why that caused thereordering of the joins.2. (more importantly) Are there any good resources that you know ofthat explain how the query optimizer choices its query paths? Do the"Inside SQL Server" books go into that much detail? Any good onlineresources?Thanks!-Tom.
HI, i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank youand my code like this use todayselect CatID from dbo.tbl_Categories where CatName like '%اتصالات%'
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName, params.name as ParameterName, types.name as ParamType, params.max_length, params.precision, params.scale, params.is_output FROM sys.procedures procs LEFT OUTER JOIN sys.all_parameters params ON procs.object_id = params.object_id LEFT OUTER JOIN sys.types types ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id AND procs.is_ms_shipped = 0 ORDER BY procname, params.parameter_id
I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
I have a query where I am trying to display the most recent information about travelling method of customers using "t.collection_date". I am struggling to get MAX function working in the query.
SELECT P.id, P.forename, P.surname, P.dob, c.postcode, l.code_des, MAX(t.collection_date) FROM People p, MOT_HIST t, lk_mode_of_travel l, corresp_address c
WHERE p.ID = t.id (+) AND t.mode_of_travel = l.int_code (+) AND p.id = c.entity_id (+)
I am trying to write a query that will be able to select different segments of data based on spelling of the last name.
For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'
then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...
I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?
Hi Guys, I have a DataBase in which I have several Tables. What I want is an SP or Query which takes as its parameter the "tablename". The Output Should be a having three fields only. Field name, DataType Of the Field, Length of the DataType. For Example Suppose the StoredProcedure Name is "SP_GetTables" if i have a table named "tbl_Users" with fields UserName varchar(50) UserPass varchar(20) UserAge int UserStatus bit In my program side if I pass the parameter as "tbl_Users" to the StoredProcedure SP_Users, I should get the O/P as Field Name DataType Length UserName varchar 50 UserPass varchar 20 UserAge int UserStatus bit Regards, Naveen.
Hello all, I use Asp.net 1.1 vb script and MS SQL server 2000. I'm wondering if there's a way to put a database field into the title meta tag. For example if I have a detail page for cars which pulls the text info from the SQL data base and I want the field "name" in the title tag details.aspx?name=Hummer would have the Title meta tag as Cars..."Hummer" and details.aspx?name=Corvette would have the Title meta tag as Cars..."Corvette" so that for each car showing in details.aspx page it wouldn't have to have the same title tag for search engines
Hi,I would like to prepare a data dictionary for my database (northwind).I have framed the below SQLSELECT'NAME ' = a.name,'DESCRIPTION' = b.value,'Type ' = type_name(a.xusertype),' ' AS 'Values','NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,' ' AS 'PK',' ' AS 'FK'FROMsyscolumns a,sysproperties bWHEREa.id = 2073058421 AND --- Customers Tablea.number = 0 ANDb.id = a.id ANDb.smallid = a.colidORDER BY a.colidand the output would be:NAME DESCRIPTION Type Values NULL PK FK-------------- --------------- ---------- ------ ----- ---- ----CustomerID Customer ID ncharCompanyName Company Name nvarcharContactName Contact Name nvarchar XContactTitle Contact Title nvarchar XAddress Address nvarchar XCity City nvarchar XRegion Region nvarchar XPostalCode Postal Code nvarchar XCountry Country nvarchar XPhone Phone # nvarchar XFax Fax # nvarchar XPK and FK is where I need to print whether the column is Primary Key orForeign Key.If CustomerId is defined as Primary Key then PK should have X printed.Thats the objective.How will I accomplish this ?Thanks in advance,Anu
I am wanting to set up some kind of metadata catalogue to managemetadata records of the data we collect and create for my companiesclients.I am thinking I want to do this using XML and SQL Server and have sometype of web-based browser to search for records.Any suggestions on existing applications or resources that I could use?Thanks
