SQL Server 2014 :: Transform Variable - Add Operator OR
May 2, 2015
I have a text box on a web application that allows the user to type what words they want to look. In the first version of the web application the user wrote the words he wanted and was wanted on the table by CONTAINS various words with the OR operator . What I wanted now was to allow the user to write several words, but with a particularity . The words that were inside '' was like a word.
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
What I meant was that even using the full-text functionality. I'm using CONTAINS . But before the CONTAISN was :
SELECT Description FROM Production.ProductDescription WHERE CONTAINS ( Description, PRODUCT )
Since PRODUCT and was researched by words such as ORANGE LEMON OR OR OR BANANA APPLE OR PEAR OR PEACH .
What is wanted now the words that come from the WEB application within '' stay as if it were the AND operator then was :
Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.
Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?
I'm passing name of this table (dbO.TD_EmployeeProfile_FinalV2) to get the counts from function FN_COUNT_ROWS (count is 3 as you can see above) so this while loop should run 3 times for all three employees. I'm having issues with statement "SELECT @EMP_ID" and "SELECT @EMAIL"
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100) ,@EMP_ID INT
[Code] .....
---ERRORS i'm getting Msg 102, Level 15, State 1, Line 23 Incorrect syntax near ')'. Msg 102, Level 15, State 1, Line 24 Incorrect syntax near ')'
Hi, i'm trying to port a pivot query from access to sqlserver. I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS [Department-3], [4] AS [Department-4] FROM (SELECT IDMerce, Pezzi, IDMagazzino FROM Disponibilita) p PIVOT (sum(Pezzi) FOR IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations i need, so there is a solution? Thanks
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100) RETURNS int AS BEGIN DECLARE @emp_count AS int declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3 Incorrect syntax near 'RETURNS'. Msg 102, Level 15, State 1, Procedure count_rows, Line 10 Incorrect syntax near '@declaration'. Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
In my current project i have a requirement to assign value of an aggregate transform to a variable. But i need to accomplish it without using a script task.
I am migrating a classic ASP application that ran on Access to ASP.NET with SQL Server 2005 DB. I am having some trouble getting a query to work. It runs part of our courses section of the web site. SELECT CREDIT_DATA_LOCCODE.Loc_Code, CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Fiscal, CREDIT_DATA_COURSE.Term, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Status_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Course_Number2, CREDIT_DATA_COURSE.Section_Number, CREDIT_DATA_COURSE.Course_Title, CREDIT_DATA_COURSE.Credits, CREDIT_DATA_COURSE.Credits_Other, CREDIT_DATA_COURSE.Web_Website_link, CREDIT_DATA_COURSE.Web_Description, CREDIT_DATA_COURSE.Web_Instructor_1, CREDIT_DATA_COURSE.Web_Instructor_2, CREDIT_DATA_COURSE.Web_Location, CREDIT_DATA_COURSE.Web_LocCode, CREDIT_DATA_COURSE.Web_Dates, CREDIT_DATA_COURSE.Web_ETV_Dates_Times, CREDIT_DATA_COURSE.Web_ETN_Dates_Times, CREDIT_DATA_COURSE.Web_Times, CREDIT_DATA_COURSE.Enrollment_Limit, CREDIT_DATA_COURSE.Web_Icon_1, CREDIT_DATA_COURSE.Web_Icon_2, CREDIT_DATA_COURSE.Web_Note, CREDIT_DATA_COURSE.Web_Prereq, CREDIT_DATA_COURSE.Web_Special_Message, CREDIT_DATA_COURSE.Web_Alt_Major_Code, CREDIT_DATA_COURSE.Web_Alt_Course_Number1, CREDIT_DATA_COURSE.Web_Alt_Course_Number2, CREDIT_DATA_COURSE.Web_Alt_Section_Number, CREDIT_DATA_COURSE.Deleted, CREDIT_DATA_COURSE.Update_DateFROM CREDIT_DATA_LOCCODE INNER JOIN CREDIT_DATA_COURSE ON CREDIT_DATA_LOCCODE.Loc_Code = CREDIT_DATA_COURSE.Web_LocCode WHERE (CREDIT_DATA_COURSE.Fiscal = N'078') AND (CREDIT_DATA_COURSE.Term = N'10') AND (CREDIT_DATA_COURSE.Status_Code <> 5) ORDER BY CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Section_Number But when I run this query with the ORDER BY Clause in place against the SQL 5 tables Sheila dumps into I get this Error:
I'm trying to get a tree structure from a table. Ex : Security table. How can I accomplish that task in SQL Server?? I will do it using Level and connect by operators in Oracle to accomplish the task. Your reply in this regard is highly appreciated. Thanks, Navy
how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type', character_maximum_length as 'Max Length' FROM information_schema.columns WHERE table_name = 'tblUsers'
I have a problem, i am upgrading a system that uses Oracle databases for using SQL Server 2005 databases. The thing is that the operator (+) (involved in many queries) is from Oracle and operators(*= and =*) are not supported in SQL Server 2005 (and not comply with the Ansi standard) i need help translating these queries. An example would be like this:
SELECT ...
FROM IBA_MPACCIONESXREQ, IBA_MPPERMISOS, IBA_MPENTIDADES, IBA_MPREQUERIMIENTOS, IBA_MPPROCEDENCIAS, IBA_LOCALIZACIONES, IBA_MPPROCEDXLOC, IBA_USUARIOS
WHERE IBA_LOCALIZACIONES.ID_LOCACLIZACION = IBA_MPPROCEDXLOC.ID_LOCACLIZACION AND IBA_MPPROCEDENCIAS.IDPROCEDENCIA = IBA_MPPROCEDXLOC.IDPROCEDENCIA AND IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION = IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION AND IBA_MPENTIDADES.IDENTIDAD (+) = IBA_MPPROCEDXLOC.IDENTIDAD
AND IBA_MPPERMISOS.IDPERMISO (+) = IBA_MPPROCEDXLOC.IDPERMISO AND IBA_MPACCIONESXREQ.IDREQUERIMIENTO = IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
ORDER BY ...
I didnt include the SELECT and ORDER BY text because i dont think its important. Also i know that you have to move the outer joins to the FROM clause but i didnt manag to get it. Could you help me? thank you in advance.
I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.
SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called, Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds FROM CallRecords WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')
When I preview in the Transform Data Task, I get: Package Error Error Source: Microsoft JET Database Engine Error Description: No value given for one or more required parameters.
When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)
When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.
I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.
1) I created an operator - me.
2) I created a job which runs a query.
INSERT INTO Sessions_alert SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate() FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id GROUP BY host_name,program_name,login_name ORDER BY 4 DESC
3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.
4) I've set the alert to email and delay is between responses 2 minutes.
It history tables says number of occurences is 18964. However, I don't receive an email.
Shouldn't the alert send an email? Do I need to include email code in the job?
I've been working with Database Mail for some time but I haven't seen this one before. I have a maintenance plan that does the following:
1. Check database integrity On Success: 2. Perform backups On Success: 3. Perform a maintenance cleanup On Success: 4. Notify operator of success
Steps 1 - 3 are also linked (via On Failure arrows) to a singular Notify Operator of Failure task.
The maintenance plan does exactly what I want it to, and if everything goes correctly, it successfully sends an email to an operator.
If steps 1 - 3 fail, the job ends in an error state, but does not trigger the Notify Operator of Failure task.
I was able to recreate the problem by creating another maintenance plan on the same instance with the same steps. Its "Notify Operator" on failure task also doesn't work.
It occurs to me that maybe I'm missing something, so here's some of the details of my SQL server:
SQL 2008 + SP1 (10.0.2531) x64 on Windows 2008 R2 DB Mail profile is public and default
I do have a slightly unusual profile, in that it uses two accounts:
1. A connection to a SharePoint SMTP service (where it catches emails directed at document libraries)
2. A connection to a UNIX-based smtp server (which routes mail to regular mailboxes and my SQL DBA mailing list)
The Profile will attempt to send to the SharePoint server first. The SharePoint server does not relay. If the document library email address doesn't exist, SQL will raise a warning (in the Database Mail log) and the profile will use the second account on the list, which is a real mail server and can relay the message to any mailbox.
It works really well, actually. When the maintenance plan completes successfully, the message is sent to the drop folder on the SharePoint server, and SharePoint routes the email to the correct library, and we have a central archive of all DB Mail notifications.
But if the job fails (for example, if the backup disk is out of space), none of this happens. According to the log, the job doesn't even try to send a notification. Looking at the DBMail log, the Mail service does not start. No email is delivered to the drop folder of the receiving SMTP server. So I don't think my Database Mail configuration is the problem here. It is apparently something to do with the way the job itself handles errors.
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
------------- case when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%' and [New Extension or Replacement Audit Flag] like 'FLAG%' then 'Reject – New, Extension, Replacement invalid entry' --'it is jp' else '' end as [ES Fully approved data 1], case
Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
Hi, i have a MDF file used by an asp.net application (with sql server 2005 express). I want to make it to a permanent database in sql server express. I know that i can attach it from Management Studio, but is there no better way? Does it exist a way to 'import' or 'migrate' or copy' a .MDF.database into the central sql server, as if created directly in sql server express? After that, i can delete the .MDF file. Thanks Tartuffe
I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".