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 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)
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL as select UserName , UserPassword , UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
where as I prefer this format:
create procedure getUser ( @UserId int = NULL ) as select UserName, UserPassword, UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Hello - Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size. Thanks Adam
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible? SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV I tried: SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV But this does not format it as currency. Any input would be helpful.
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005. How could I write a sql statment to put make this show up as 07/29/05?
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible) Using this SQL statement: select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc the resulting output displays this in the email body "Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 " but what I want is the text to look more like this: Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092 Is there a way to do this in the SQL statement (without HTML tags)?
I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as: 10001 10003 10009 54321 89043 etc Let's say I have a couple of hundreds of them. I need to be able to see them as -
I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this
select distinct(companyname),max(calldate) from tablename
what I want to see is the latest call made to a customer for each company but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?
I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:
col_1 col_2 col_3 col_4 col_5 col_6 col_7 PK #1 John Doe 123 Elm Street Anywhere NM 55660 #2 #3 etc..
...and create this on a different table (noting col_2 is of datatype "text": col_1 col_2 PK #1 John Doe 123 Elm Street Anywhere NM 55660 #2 #3 etc..
Can this type of formatting be done? I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??
Requirement: On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.
Question: What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.
e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??
I'm currently getting a date in this format 2001-10-08 10:35:45 (yyyy-mm-dd hh:mm:ss) how would I convert the date to this format 2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.
The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?
Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results.. Here is my script to show the results. ---------- SET NOCOUNT ON DECLARE @errorCount INT SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5 IF @errorCount = 0 BEGIN INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' ) END INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) ) GO SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0 SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0 SELECT reason FROM error_report WHERE id = - 1 SELECT '' SELECT reason FROM error_report WHERE id = - 2 SET NOCOUNT OFF GO ------------------------- The Results in the bottom pane looks like this below ------------------ NODETABLE table has bad data (There are 2 duplicate subclass) Propertytable table has bad data (at Parentid = 2000000859 and p) Propertytable table has bad data (at Parentid = 10122 and proper) ------------------- But, when I do a select, they are like this below ----
NODETABLE0There are 2 duplicate subclass name: Specification Propertytable0at Parentid = 2000000859 and propertyid = 721 Propertytable0at Parentid = 10122 and propertyid = 9
I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?
DECLARE @FileName varchar(50), @FileName1 varchar(50), @bcpCommand varchar(2000) SET @FileName = 'E:GPPD_db_stats.XLS' SET @FileName1 = 'E:GPPD_file_stats.XLS' print @FileName SET @bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @FileName + ' -Samex-srv-gppdb -T -c' print @bcpCommand EXEC master..xp_cmdshell @bcpCommand
SET @bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @FileName1 + ' -Samex-srv-gppdb -T -c' print @bcpCommand EXEC master..xp_cmdshell @bcpCommand exec master.dbo.xp_stopmail set @bcpCommand = ' ' + @FileName + '; ' + @FileName1 + '' DECLARE @body VARCHAR(1024) SET @body = 'Please find enclosed files with the database status reports as of '+ CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' + 'with data files as this email address is not monitored for incoming emails. However, if you have any ' + 'questions/concerns please contact ....'
Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format: 2004-01-19 00:00:00.000 but I want it to be like this: 1/19/04 or 1/19/2004 Is there a way to do this? Thanks.
I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:
Piece of cake....however I am struggling with this in MSSQL.
Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).
I have gotten this far....
select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2)) + '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1 From tPrices
I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)
I looked through a lot of the T-SQL docs but have come up dry.
At first I'd like to justify myself - I'm rather a greenhorn in MS SQL ;-) What I'd like to do is to create a very simple spool file from my database. I'd like to do this using OSQL tool and it's very important for me to have the spool file in the same format as it was earlier when I used Oracle and simple spool command. And it's almost the same, but white spaces... There's always one white space at the begining of each line and at least one at the and of each line. I've been trying many OSQL switches and ltrim(..) / rtrim(..) functions but the problem still exists... The spool file is rather big - around 300MB so it's not good idea to use SED (for example) to remove white spaces. So my question: IS IT POSSIBLE TO REMOVE WHITE SPACES COMPLETELY USING OSQL SPOOL?
hey there I have a series of finacial reports. the girls that work on them want color and the boss wants them to print in black and white. the color back grounds print grayscale so I would like to add a link of some sort that changed the background to transparent when they want to print a report.
I'd like to know if there is a way to replace everything between these two integers (well, actually we can say two words as they are in the string format) with a single comma (i.e ,) and remove everthing leading and trialing to these two numbers please ?
In short , I would like to see the above line after string manipulation as follows :
I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5. Ex: field in DB = aaa I need to format it as: 2 spaces + aaa
But the length of the value will be varying from record to record.