Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?
(No huge rush, the someone-else has already fixed the recent case)
SQL 2K, SP3
Hello!How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT andthen switch it ON straight afterwards. So I can insert strings likeO'Neil etc.It's my only design option at the moment. Everything works fine and asfar as I can see, it's only set to OFF for the currentconnection/session so no other users will run into problems at thesame time if they need it ON, right?Thanks Martin
View 4 Replies View RelatedOK, so I know that following works if I want update/insert the value "joe's bar" into the table.
set SET QUOTED_IDENTIFIER on
update #temp set id=4 where name ='joe''s bar'
The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)
I have a very strange problem, it only happen to one SQL Server, otherSQL Server seems to be fineI got the following error when trying to run a sp against one of theSQL Server:SELECT failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct foruse with indexed views and/or indexes on computed columns and/or querynotifications and/or xml data type methods.If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,everything works fine, but the questions is why should I do that? andwhy it only happen to only one SQLServer ? The database option onQUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)Thanks in advance.JohnEnclose is the statement that create the databaseif db_id('testdb') is not nulldrop database [testdb]gobeginUSE [master]CREATE DATABASE [testdb] ON PRIMARY( NAME = N'testdb',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb.mdf' ,SIZE = 8192KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'testdb_log',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb_log.ldf' ,SIZE = 29504KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASEXEC dbo.sp_dbcmptlevel @dbname=N'testdb',@new_cmptlevel=90ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFFALTER DATABASE [testdb] SET ANSI_NULLS OFFALTER DATABASE [testdb] SET ANSI_PADDING OFFALTER DATABASE [testdb] SET ANSI_WARNINGS OFFALTER DATABASE [testdb] SET ARITHABORT OFFALTER DATABASE [testdb] SET AUTO_CLOSE OFFALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ONALTER DATABASE [testdb] SET AUTO_SHRINK OFFALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ONALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFFALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBALALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFFALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFFALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFFALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFFALTER DATABASE [testdb] SET ENABLE_BROKERALTER DATABASE [testdb]SETAUTO_UPDATE_STATISTICS_ASYNC ONALTER DATABASE [testdb]SETDATE_CORRELATION_OPTIMIZATION OFFALTER DATABASE [testdb] SET TRUSTWORTHY OFFALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLEALTER DATABASE [testdb] SET READ_WRITEALTER DATABASE [testdb] SET RECOVERY FULLALTER DATABASE [testdb] SET MULTI_USERALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUMALTER DATABASE [testdb] SET DB_CHAINING OFFend
View 4 Replies View Relateddoes anyone know how to keep QA from adding the lines setting thesetwo options on and off along with blank lines at the beginning and endof every object you edit? i have searched quite a bit on this buthaven't been able to come up with anything.
View 1 Replies View RelatedWhen I execute a SET QUOTED_IDENTIFIER statement, does it only apply to the current batch or current connection? I noticed it doesn't change the "Quoted Identifiers Enabled" setting in the Options page of the Database Properties in Management Studio.
Thanks,
-Dave
I am working for a client that would has XML data being passed into a Service Broker queue.
View 8 Replies View RelatedIs there any way via T-SQL to run through a database and ensure that QUOTED_IDENTIFIER and ANSI_NULLS is set for all stored procs and functions etc on a database without having to recreate every proc / fn ?
The reason this is an issue is I'm trying to take advantage of indexed views to get a performance increase in an application - but not all of the procs/fn's have historically been created with ANSI_NULLS and QUOTED_IDENTIFIER set - so any proc that's not set correctly will fail once the indexed view has been created.
I could I suppose, script out all the procs / functions and do a bit of search and replace to ensure that these are set correctly by recreating all procs and functions - but I'm trying to avoid doing that as I've over 500 databases to upgrade - a metadata hack may be preferable.
Thoughts or comments or possible alternative approaches are welcome...
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."error after creating a view.We wanted a composite unique constraint that ignored nulls, so we setup a view using the following script:/* --- start --- */BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITGOCREATE VIEW vw_MyViewWITH SCHEMABINDINGASSELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULLGO/* --- end --- */and then added the constraint to the new view/* --- start --- */CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ONvw_MyView(Col1, Col2)GO/* --- end --- */I thought we were doing fine, 'til we started running some DELETEstored procedures and got the above error. The error also citedARITHABORT as an incorrect setting until we ran this script:/* --- start --- */USE masterDECLARE @value intSELECT @value = value FROM syscurconfigsWHERE config = 1534SET @value = @value | 64EXEC sp_configure 'user options', @valueRECONFIGURE/* --- end --- */TIA to anyone kind enough to shed some light on this for me. Is theresomething we should have done differently in creating the view andindex? If not, what's the procedure for working through thesesettings errors?I've read through some other threads on this subject, but didn'treally find what I was looking for. Thanks again for any help. Wouldbe appreciated.-matt
View 3 Replies View RelatedWhen I want to delete a data from a table that this tabl has a triggerand this trigger reached another tables to delete the data in cursor Ihave this messeage:DELETE failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'.My trigger :CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]FOR DELETEASBEGINDECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINCbigintSELECT @rows_affected = @@ROWCOUNTIF @rows_affected = 0RETURN -- No rows changed, exit triggerBEGINDECLARE Miktar CURSOR FORSELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINCFROM deletedOPEN MiktarFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCWHILE @@fetch_status = 0BEGINSET QUOTED_IDENTIFIER ONDELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINCAND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1SET QUOTED_IDENTIFIER OFFPRINT @DEPOBKINCFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCENDCLOSE MiktarDEALLOCATE MiktarENDEND
View 6 Replies View RelatedIm trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedI tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
This is working:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'
Why ? Why ? Why ?
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
View 3 Replies View RelatedI have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables
Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown
The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).
my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.
Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");}
private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"];
if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
Hi,
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
Cheers
Chris
Hi everyone.
I'm working on an assignment and I am about to give up. I can't figure out what I'm doing wrong. It seems like I have everything worked out, but when I run the SQL query i've come up with, I get errors regarding INVALID tables.
As far as I can tell, all my tables are valid. Can anyone give me any pointers on what i'm doing wrong **read: I'm not asking for my assignment to be done for me, just asking for help because I am so close to getting the right answer....i think**
Here is what was provided:
http://i47.photobucket.com/albums/f152/hmarandi/problem.gif
Here is the Query I have come up with which gives me errors!
Code:
-- START --
CREATE TABLE dept
(deptnameCHAR(15),
empid CHAR(8),
CONSTRAINT PKdeptname PRIMARY KEY (deptname));
ALTER TABLE dept
ADD CONSTRAINT FKempid FOREIGN KEY (empid) REFERENCES Employee(empid);
-- START --
CREATE TABLE Employee
( empid CHAR(8),
deptname CHAR(15),
empfname VARCHAR(10) NOT NULL,
emplname VARCHAR(10) NOT NULL,
empphone CHAR(15),
empemail VARCHAR(20) NOT NULL,
bossid CHAR(8),
empsalary DECIMAL(9,2) CONSTRAINT CHKEmpsalay Check (empsalary > 5),
CONSTRAINT PKempid PRIMARY KEY (empid),
CONSTRAINT uniqueEmail UNIQUE(empemail) );
-- Add constraint
ALTER TABLE Employee
ADD CONSTRAINT FKdeptname FOREIGN KEY (deptname) REFERENCES dept(deptname);
-- START --
CREATE TABLE POrder
(OrderID CHAR(8),
OrderDatedatetime,
CustIDCHAR(8),
EmpIDCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
-- Add constraint
ALTER TABLE POrder
ADD CONSTRAINT FKCustID FOREIGN KEY (CustID) REFERENCES Customer (CustID);
ALTER TABLE POrder
ADD CONSTRAINT FKEmpID FOREIGN KEY (EmpID) REFERENCES Employee (EmpID);
-- START --
CREATE TABLE Customer
(CustID CHAR(8),
CustNameVARCHAR(15),
BalanceCHAR(8)
CONSTRAINT PKCustIDPRIMARY KEY (CustID));
-- START --
CREATE TABLE OrderItem
(OrderIDCHAR(8),
ProdIDCHAR(8),
QtyCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
ALTER TABLE OrderItem
ADD CONSTRAINT PKProdIDPRIMARY KEY (ProdID)
ALTER TABLE OrderItem
ADD CONSTRAINT FKOrderID FOREIGN KEY (OrderID) REFERENCES POrder (OrderID);
ALTER TABLE OrderItem
ADD CONSTRAINT FKProdID FOREIGN KEY (ProdID) REFERENCES Product(ProdID);
-- START --
CREATE TABLE Product
(ProdIDCHAR(8),
ProdNameVARCHAR(15),
MakerVARCHAR(15),
StockSizeCHAR (6),
PriceCHAR (10)
CONSTRAINT PKProdIDPRIMARY KEY (ProdID));
Any HELP would be greatly appreciated.
Thanks!
it gives an error saying
"incorrect syntax near update" . can anyone tell me why?
EXEC("update "+@sTable+"
set status='A'
where breakdate < datediff(day,'08/12/1960',getdate())
and clientid=12059
and status ='F'")
I am trying to created a view and have a need for conditional logic:
Here is what I presently have (not working):
----------------------------------------------------------------------------
IF (ISDATE(COMPLETIONDATE) = 1)
BEGIN
CASE
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
ELSE
IF (ISDATE(COMPLETIONDATE) = 0)
BEGIN
CASE
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
--------------------------------------------------------------------------
Can someone tell me what I am doing wrong?
Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".
Thanks...
select * from dbo.Advertisement_Search '%'
Advertisement_Search is a store procedure and i am trying this in ms sql.
Thanks in advance.
Hello all.
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))
BEGIN
PRINT N'Adding foreign keys to [dbo].[tblProductStock]'
ALTER TABLE [dbo].[tblProductStock] ADD
CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
Thanks for reading.
INSERT INTO IS_REGISTERED VALUES
(54907,2715,'I-2001')
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__IS_REGISTERED__629A9179'. Cannot insert duplicate key in object 'IS_REGISTERED'.
The statement has been terminated.
How do I fix this? I am trying to insert this into the IS_REGISTERED Table.
Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001
IS_REGISTERED (Student_ID, Section_ID, Semester)
albanie
Hi everybody,
I posted this issue a couple of days ago, but got no solution yet. Anyhow, here is the thing:
"A limited user account is able to see items on the report manager with no permission?"
I have created a local user account on the domain machine where the reports are deployed. This user is not a member of administrator group. It is just a user under the User Group. This user doesn't even have a permission on the Report Manager, not even a browser role. However, this user is able to see the contents of the report manager and also can make changes role assignment under the properties tab in the report manager.
Accourding to my observation so far, any user account created on this domain machine is acting like an admin on the Report Manager, with out being given a permission on the Report Manager. I know something is wrong
Please somebody advise on this
here i am trying to get the count of both match ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) + (AECPRDA_1.product_id = z.vendorcode)... probably AND won't do the trick.
i believe my below query (3) is wrong. what changes should i make to get both the match and continue further.
(3) should be atleast greater than (1) or (2)
1. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (only the first match)
2. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 2183 (only the second match)
3. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11)) and (AECPRDA_1.product_id = z.vendorcode) )
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (1st & 2nd match.. i expect the count to be higher than 2)
what i am doing wrong here...
[Column 9] == "00000000" ? NULL(DT_WSTR,10) : RIGHT([Column 9],2) + "/" + SUBSTRING([Column 9],5,2) + "/" + SUBSTRING([Column 9],1,4)
Where destination sql table own as datatype smalldatetime and accept NULLS.
Where Column 9 is defined on the pipeline as [DT_WSTR] with lenght of 10.
Otherwise Column 0 is exactly the same than Column 9 and works fine with this expression:
RIGHT([Column 0],2) + "/" + SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],1,4)
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 9" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Hi All!
Simple problem, I'm missing something.
I have this at the end of my statement
IF @@ROWCOUNT = 0
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jrudolf@ikon.com',
@body= @body,
@body_format = 'HTML',
@Subject='NO RECORDS TO DELETE',
@profile_name = 'Ikon'
Else
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jrudolf@ikon.com',
@body= @body,
@body_format = 'HTML',
@Subject='PLEASE REVIEW RECORDS',
@profile_name = 'Ikon'
END
I'm thinking when it returns 0 rows it should do the first email, else it should do the second email. But it's always doing the second. And I know it's 0 rows, because I'm not getting any results. And I made sure of it.
Thanks!
Rudy