This is probably a simple question but i am trying to create a simple function thatfilters sql input. Is the following syntax correct? Public Shared Function SafeSql(ByVal firstName As String, ByVal lastName As String) As String firstName.Replace("'", "''") lastName.Replace("'", "''") End Function many thanks martin
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example: create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example: create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way: create procedure ABCasbegin /* some checks and expressions here ... */ -- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info())) select ... from A inner join B on (A.ID = B.REF_ID) where ... and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables. For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
I need to find a 12-digit number in a column called "SequenceNumTxt", in a VERY long table.I can query the first n lines but need to add something like "WHERE [SequenceNumTxt]="123412341234".The above does not work, gives me a syntax error; what am I doing wrong?
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example: create table A (ID int identity, NAME varchar(100), COMPANYID int) create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example: create procedure ABC as begin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */ end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way: create procedure ABC as begin /* some checks and expressions here ... */ -- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info())) select ... from A inner join B on (A.ID = B.REF_ID) where ... and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */ end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables. For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Some tables i have in a report. A couple of them work, but 2 others dont. I would do it in the query, but then i wouldnt be able to group them in a list.
So my question is, how come i can filter out my BillcustomerCode, but when i want to filter out my subbrand_id (which is an int) or subbran_ Descrition(which is a varchar). how would i filter out these types in the table filter.
Also, how would i do an "Or" instead of an "and" in my filter too.
I have one table where i need to have 3 things not in the table: Bill_Cus_code !=RNPROF Bill_Cus_code !=RNPROC Sub_Brand_Id ! =65 (or Sub_Brand_Discription != "Pro Club")
how do i do this in the expression. It seems to take out "RNPROF and RNPROC" but not the third one. Why is this?
I want to write a SQL statement that join Table1 and Table2 together, T2Field equal to XXX, and list all records from Table1. That's the result columns: T1PK, T1Field, T2PK, T2Field Result records: 1, ABC, 101, XXX 1, ABC, 103, XXX 2, DEF, 104, XXX 3, GHI, NULL, NULL
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011 MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
Table filter operation where I want to filter against any combination from any of the available fields: table a(f1 int, f2 int) 2 indexes - first by field f1, second by field f2 data - many records like (f1=1, f2=2), and many records like (f1=3, f2=4) sql: select * from a where f1=1 and f2=4 result will be - no records, but full table scan is required, indexes are not used question - how to optimize above structure, but do not create compound index by (f1, f2)? if we will have many fields (lets say 10), which can be used in filter in any combination we will need to create 10! Indexes. Any way to avoid or optimize it for a high volume searches?
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) .... I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
I am using VS 2005/SQL 2005 and must use a stored procedure as provided. The stored procedure returns parent rows and child rows (same as parent with additional columns populated, like ChildID). I cannot change the stored procedure.
I have been unable to filter the data (either at the Dataset level or the Table Properties level) to only see the "parent" rows. On my report, I added one column to print the value for my test and a second to use it:
Both columns produce the values I expect. However, if I try a filter using the first expression, it fails! I.E. if I test = 0, it filters out ALL rows and if I test != 0, all rows are displayed?????
I am new to SSRS, so I would not be surprised if this is cockpit error. It seemed like a very simple requirement that I have used in othe report tools for years.
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G
declare tableName table ( uniqueid int identity(1,1), id int, starttime datetime2(0), endtime datetime2(0), parameter int )
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target using ...
and I can't have:
merge tableName as target using [my query] as source on source.id = target.id and source.starttime = target.startime and source.endtime = target.endtime where target.id = 10 ...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
I am working on SQL server 2005 Reports. I have one report, one dataset is assigned to it, and one table which displays it. Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5 5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Ok I am new to Sql and am wonder How I can (when createing a table ) have a field that is auto incremented.
Here is what I have so far
Code:
CREATE TABLE test (`id` INTEGER NOT NULL, `test` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`)
I want the primary key id to be an incremented number.
I am used to mysql and I would just have the field created as an AUTO_INCREMENT.
Do I use the INCREMENT function within the first query or do I have to have another query that will alter the table and make the field an auto incremented number.
Is there a way to join 2 tables from different databases on the same instance? If so, what is the syntax? For example I have an instance called TEST with 2 databases (DB1 and DB2). Each database have a table (DB1.TABLE & DB2.TABLE). Both tables have a common column called ID. Can both tables be joined in a query?
hi.. i want to know how to create table while specifying the database name.. the syntax given in the help is this
CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name but i cant seem to do it.. it always generates an error whenever i give my database name..
I have a flatfile contain several SQL syntax calling a stored procedure (execute usp_table 'param1', 'param2' out) and have been imported into a temporer table. Inside this table I have a few column (identity column, syntax column and status column). Status column used for identify whether the execution succes. My plan is to read this table and execute the syntax row by row, the stored procedure will return a success or failure status from param2. And I have to read this return to update the Status column inside the table. I tried Execute SQL Task and throw the return value of stored procedure to a result set. But I didn't know how to treat the result set to update the table. So what task should I use ? Thanks in advance.
Im trying to write a generic data access layer that supports SQL CE and Im wondering if any type of schema qualifier can be placed in front of a table name when executing a sql statement.
I've tried soemthing like this
select * from dbo.Account
I get this error,
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = account ]
It doesnt make really make sense to include a qualifier for sql ce but I just wanted to make sure that there wasnt some other syntax that I wasnt aware of.
Hello I am trying to join 2 tables listing product names but I can’t get the syntax quite right.
I have managed to get the 2 tables to display separately.
Table #1: Select ProductName as [Product Name] From Product_CatA Where Auto = 'yes' Order By ProductName ASC
Table #2
SELECT ProductName, Price, '<a href="' + url + '">' + Title + '</a>' as Link FROM ProductNames WHERE Extn = '0151' And url like 'http:%' ORDER BY ProductName ASC
I want to display all the data values taken from Table #1 as above.
I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tableName]( [id] [int] IDENTITY(1,1) NOT NULL, [ArticleID] [int] NULL, [Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WrittenDate] [datetime] NULL, CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ) )
What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.
Hi, I wish to export a MS SQL table into a text file. I know that I can do this under the DTS wizard but I need the syntax so as to run in on my script. Can anyone help? Thanks!
