I know you can restrict/grant select rights to certain columns in SQL Server table. My question is if in a select query, where you list out the columns (select colum1, column2...) that you have select rights on do you also have to have select rights on columns that apear in where clause or the columns in a join?
For example say I have select rights on column1 and column2 of test table, but not on column3. If I run a select statement like this:
select column1, column2 from test where column3 = 'abc'
Will I get a select persmission denied because the query is using column3 in the where clause.
David
I've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables. But some procedures of above cited, in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables. The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok. You troube the same, some help please :)
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.
I have four tables. Product Line, Item, Property, and Value.
A Product Line has many items and an item can have many property's and a property can have many values.
I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.
Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
how can i retrieve two columns from sqltable with - seperating the results and a common column name someth like this select id ,name from user id name 1 a 2 b 3 c i need the result set to be my_reports 1-a 2-b 3-c is that possible, if so, could anyone tell me how to do?
OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly: SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test' Please note the AND is incorrect. What i need to accomplish: if one record contains 'test' in its skills field then SELECT it. if one record contains 'test' in the title field the SELECT it. I need to make sure that is 'test' is in both skills and title fields then only display once. Thanks, Jake
I am working with several similar tables that for the most part contain the same columns. I need to export these tables to different csv files, and the csv file needs to have all possible column names and blank fields if the column does not exist in the table. Basically a set and subset thing.
for example TABLE AA has three columns A, B, C and TABLE AB has 2 columns B, D
I am working with over 200 tables, and am planning on automating this with c#. I could use the information_schema to create the SQL statement for each tabel but it sems like I should also be able to do this using one select statement.
I want to write a select statement that combines this. SELECT [A], [B], [C], '' AS [D] FROM [AA]
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want
Here is the procedure
Create procedure TopNcolumns (@tableName varchar(100),@n int) as Declare @s varchar(2000) set @s='' If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table') Begin If @n>=0 Begin set rowcount @n Select @s=@s+','+ column_name from information_schema.columns where table_name=@tablename order by ordinal_position Set rowcount 0 Set @s=substring(@s,2,len(@s)-1) Exec('Select '+@s+' from '+@tablename) End else Select 'Negative values are not allowed' as Error End else Select 'Table '+@tableName+' does not exist' as Error
I would appreciate any help with my following problem... lets say i have...
select A.firstname + '' + B.lastname as fullname, 'Their Home is ' + A.City + ' ' + (select top 1 C.State from States C where C.City = A.City) as Location from tableA A, TableB B Where A.id = b.id
This is not the actual statement but follows the same kinda logic... the problem that i get is that some of the rows in both my fullname column and in my location column show up as null... how would i fix it so for instance even if the state is missing it would still show: their home is LA or if just the last name is available it would show the lastname?
I have to insert data from about 30 tables into a single table (Users),to do so i used a cursor and a bit of dynamic sql, this should workfine if the tables have to do the select from had the same number ofcolumns, how ever they don't.I only need the first 5 columns from each the table, in the case wherethey have more than the 5 i need for my 'Users' table i get this error:'An explicit value for the identity column in table 'Users' can only bespecified when a column list is used and IDENTITY_INSERT is ON.'.Is there a way to select only the first five columns from a table, ifnot is there another solution for my problem?My Sql query is the following:DROP TABLE UsersCreate Table Users(ID INT identity PRIMARY KEY,TIPO VARCHAR(255),NOME VARCHAR(255),USERNAME VARCHAR(255),EMAIL VARCHAR(255),GROUPID VARCHAR(255))DECLARE @Table VARCHAR(255)DECLARE @Sql VARCHAR(8000)DECLARE sysCursor CURSORFORSELECT name FROM sysobjects where xtype='U'OPEN sysCursorFETCH NEXT FROM SysCursor into @Tablewhile @@FETCH_STATUS<>-1BEGIN/** INSERE VALORES NA TABELA DE TESTE*/SET @SQL = 'INSERT INTO UsersSELECT * FROM ['+ @Table +']'EXECUTE (@SQL)SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +'WHERE GROUPID IS NULL'EXECUTE (@SQL)print @tableFETCH NEXT FROM SysCursor INTO @TableENDCLOSE sysCursor/** APAGA VALORES INVÁLIDOS DA TABELA DE TESTE*/DELETE FROM Users WHERE TIPO IS NULLDELETE FROM Users WHERE NOME='Nome'DEALLOCATE sysCursorI hope you can give me hand, thank you in advance.
I have a SP having three differnet select statements like,
IF @reporttype ='A'
SELECT Column1, column2 FROM table1
IF @reporttype = 'B'
SELECT Column3, column4 FROM table2
IF @reporttype = 'C'
SELECT column5, Column6 FROM table3
Now I need three different reports for all three reporttypes. If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need. But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6.
Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work!
I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order.
I must be doing something wrong, can anyone help?
SELECTsum(Order_Item.Quantity) as Items, sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total FROM[Order] JOINOrder_Item ON Order_Item.[OrderID] = [Order].[ID] GROUP BY[Order].[ID], [Order].OrderRef, [Order].CustomerID, [Order].ReceivedDate, [Order].Postage, [Order].Discount ORDER BY [Order].ReceivedDate DESC
Items Total ==== ===== 114.9900 113.5920 333.6750 227.1840 567.9600
I have a table with 3 columns - userID, score, dateCompleted
From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date.
To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID.
I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows?
I hope this is clear, but please let me know if I've confused you.
Using DISTINCT with SELECT have effect only for one column. But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows Select DISTINCT a4 From Y ---> " 85 rows
the following criteria. i have the selection all done but am trying to figure out how to do the following: if column4 < 0 then add column4 to column3, move 0 to column4; if column3 < 0 then add column3 to column2, move 0 to column3; if column2 < 0 then add column2 to column1, move 0 to column2; add column3 to column4; move column2 to column3; move column1 to column2; if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
The following returns all base tables within the database of type "varchar":
Code: SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb' AND DATA_TYPE IN('varchar')" AND TABLE_NAME IN( SELECT TABLE_NAME FROM mydb.information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code: select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME] WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.
Code: with cte as ( selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID ,SUM(net_weight) as totalWeight, rate ,(select SUM(net_weight) * rate) as amount
I need to write a stored procedure the passes in a fieldname, retrives next key type int data from that field, increments the field, and returns the NextKey.
Would be nice if something like this worked: DECLARE @iNext INT SELECT @iNext = SELECT @columnName FROM Next_Keys WHERE ID = 1 SELECT @iNext = @iNext +1 UPDATE Next_Keys Set @columnName = @iNext WHERE ID = 1 RETURN @iNext
SQL Server 2000 - Win Server 2003 Editing SP from VS2005 TSQL newbe writing first sp"
Hi I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded, ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode, ResearchStudent.RegistrationDate, StudentExamination.Decision, IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate, ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID) LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID) LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)] And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1)) AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False)) ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
First of all I wants to "Thank You" all of you has been trying to helps me solve some of the problems.
Now, I come across trying to get data records from 3 columns but two columns "SeasonalStartsuppressiondate" & "SeasonalEndSuppressionDate" both contain no year last four digits. How would I select a query have some records that customer is not fall in between vacation time given a particular months Mar/02/ and Nov/04/? I only want record with “Y� fields not in those months. Notice don't have "YEAR" at the end and so, I can't determine it year of 2005 or 2006.
Here's my table and query statement:
Table - Column fields SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate Y ,04/02,11/04 N ,....,....
FROM advodb ----<<<<my sample query>>>>>----- WHERE (seasonalflag='y' AND convert(datetime,seasonalstartsuppdate + '/2007')< getdate() AND convert(datetime,seasonalendsuppdate + '/2007') > getdate())
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).
I have two statements that look like this:
SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
Each statement correctly aggregates the sums, but I need a record that shows me:
CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid
I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).
