Query analyser is saying there is an error near the WHERE keyword:
INSERT INTO EMPLOYEE_TABLE (BANK_ACC, SORT_CODE)
VALUES (63035391,603030)
WHERE 40 IN (SELECT EMPLOY_REF FROM EMPLOYEE_TABLE);
Thanks people.
I am getting an error Msg 8155, Level 16, State 2, Procedure usp_ImportNewASC_ParticipantPlanFundBalances, Line 1No column was specified for column 49 of 'a'.I think its something to do with the line number 48 and 102 ALLOC_PER is char(9) so i want to to cast it as a int and insert into the prodction data. 1 UPDATE
2 Statements..ParticipantPlanFundBalances1
3 SET
4 Act1 = ACT_ID1,
5 TotAct1 = TOT_ACT1,
6 Act2 = ACT_ID2,
7 TotAct2 = TOT_ACT2,
8 Act3 = ACT_ID3,
9 TotAct3 = TOT_ACT3,
10 Act4 = ACT_ID4,
11 TotAct4 = TOT_ACT4,
12 Act5 = ACT_ID5,
13 TotAct5 = TOT_ACT5,
14 Act6 = ACT_ID6,
15 TotAct6 = TOT_ACT6,
16 Act7 = ACT_ID7,
17 TotAct7 = TOT_ACT7,
18 Act8 = ACT_ID8,
19 TotAct8 = TOT_ACT8,
20 Act9 = ACT_ID9,
21 TotAct9 = TOT_ACT9,
22 Act10 = ACT_ID10,
23 TotAct10 = TOT_ACT10,
24 Act11 = ACT_ID11,
25 TotAct11 = TOT_ACT11,
26 Act12 = ACT_ID12,
27 TotAct12 = TOT_ACT12,
28 Act13 = ACT_ID13,
29 TotAct13 = TOT_ACT13,
30 Act14 = ACT_ID14,
31 TotAct14 = TOT_ACT14,
32 Act15 = ACT_ID15,
33 TotAct15 = TOT_ACT15,
34 Act16 = ACT_ID16,
35 TotAct16 = TOT_ACT16,
36 Act17 = ACT_ID17,
37 TotAct17 = TOT_ACT17,
38 Act18 = ACT_ID18,
39 TotAct18 = TOT_ACT18,
40 Act19 = ACT_ID19,
41 TotAct19 = TOT_ACT19,
42 Act20 = ACT_ID20,
43 TotAct20 = TOT_ACT20,
44 OpeningUnits = UNIT_OP,
45 OPricePerUnit = PRICE_OP,
46 ClosingUnits = UNIT_CL,
47 CPricePerUnit = PRICE_CL,
48 AllocationPercent = i.ALLOC_PER1
49
50 FROM
51 Statements..ParticipantPlanFundBalances1 pfb
52 JOIN (
53 Select
54 cp.PlanId,
55 p.ParticipantId,
56 @PeriodId Period,
57 f.FundId,
58 a.ACT_ID1,
59 a.TOT_ACT1,
60 a.ACT_ID2,
61 a.TOT_ACT2,
62 a.ACT_ID3,
63 a.TOT_ACT3,
64 a.ACT_ID4,
65 a.TOT_ACT4,
66 a.ACT_ID5,
67 a.TOT_ACT5,
68 a.ACT_ID6,
69 a.TOT_ACT6,
70 a.ACT_ID7,
71 a.TOT_ACT7,
72 a.ACT_ID8,
73 a.TOT_ACT8,
74 a.ACT_ID9,
75 a.TOT_ACT9,
76 a.ACT_ID10,
77 a.TOT_ACT10,
78 a.ACT_ID11,
79 a.TOT_ACT11,
80 a.ACT_ID12,
81 a.TOT_ACT12,
82 a.ACT_ID13,
83 a.TOT_ACT13,
84 a.ACT_ID14,
85 a.TOT_ACT14,
86 a.ACT_ID15,
87 a.TOT_ACT15,
88 a.ACT_ID16,
89 a.TOT_ACT16,
90 a.ACT_ID17,
91 a.TOT_ACT17,
92 a.ACT_ID18,
93 a.TOT_ACT18,
94 a.ACT_ID19,
95 a.TOT_ACT19,
96 a.ACT_ID20,
97 a.TOT_ACT20,
98 a.UNIT_OP,
99 a.PRICE_OP,
100 a.UNIT_CL,
101 a.PRICE_CL,
102 Cast(i.ALLOC_PER1 as int)
103 FROM
104 ASDBF a
105 -- Derive the unique PlanId from the Statements ClientPlan table
106 INNER JOIN Statements..ClientPlan cp
107 ON a.PLAN_NUM = cp.ClientPlanId
108 AND
109 cp.ClientId = @ClientId
110 -- Derive the unique ParticipantId from the Statements Participant table
111 INNER JOIN Statements..Participant p
112 ON a.PART_ID = p.PartId
113 --Derive the unique FundID from the Statements Fund Table...
114 INNER JOIN Statements..Fund f
115 ONa.FUND_ID = f.Cusip
116 OR
117 a.FUND_ID = f.Ticker
118 OR
119 a.FUND_ID = f.ClientFundId
120 -- get the allocation percent from the INVSRC
121 LEFT Outer JOIN INVSRC i
122 ONa.FUND_ID = i.INV_ID
123 AND
124 a.PLAN_NUM = i.Plan_Number
125 AND
126 a.PART_ID = i.PART_ID
127 --INNER JOIN Statements..AscActCodes as
128 --ON
129
130 WHERE
131 a.Import = 1
132 )a
133 ON pfb.PlanId = a.PlanId
134 AND
135 pfb.ParticipantId = a.ParticipantId
136 AND
137 pfb.PeriodId = PeriodId
138 AND
139 pfb.FundId = a.FundId
140
Any help will be appreciated
Regards
Karen
Hi all
insert into externaltemp(Cid,Keywords)
values(select id as Cid, f_name+','+ l_name as keywords from RPO.dbo.REQUIREMENT_RESOURCE)
Thanks in Advance.
Malathi Rao
I wrote this update statement and it looks right but I keep getting an error that says "incorrect syntax next to c" what did I do wrong?
Here is the statement:
UPDATE tblclients c
SET intproviderId = (SELECT TOP 1 t.inttransferid FROM tbltransfer t WHERE t.intclientids = c.intclientid ORDER BY t.inttransferid DESC)
The following sql statement is rejected by asp.net (working with sqlserver). It says something is wrong near "TableFlights". But I don'tsee anything wrong with it.Any help is appreciated:SELECT TableFlights.IdFlight, Concat = (TableFlights.OrganizerLastName+ ', ' + TableFlights.OrganizerFirstName + ' Flt:' +TableFlights.FlightNumber + ' on ' +Convert(char(10,TableFlights.FlightDate,101)) FROM TableFlights INNERJOIN TablePassengers ON TableFlights.IdFlight=TablePassengers.IdFlightWhere TablePassengers.Email = 'marvin@micro-net.com' Order ByTableFlights.OrganizerLastName, TableFlights.OrganizerFirstName-- Marvin
View 1 Replies View RelatedHello DBA's
i use this proc to insert & update the records in the databse
update works good untill i change the user id.
userid can be changed where adminuserid is the IDENTITY coulmn in the table.ALTER PROCEDURE [dbo].[spinsertusers]
-- Add the parameters for the stored procedure here
@adminuserid varchar(36),
@userid varchar(15),
@fname varchar(25),
@mname varchar(25),
@lname varchar(25),
@password varchar(15),
@address1 varchar(255),
@address2 varchar(255),
@postcode varchar(15),
@cityidentity varchar(36),
@dob smalldatetime ,
@email varchar(50),
@crtduser varchar(36),
@crtdon datetime,
@isactive char(4),
@mode char(10),
@reccount INT output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Count INT
-- Insert statements for procedure here
IF (@mode='insert')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @count=0
--Inserting the Records
BEGIN
INSERT INTO [school].[dbo].[AdminUserMaster]
([AdminUserIdentity]
,[FirstName]
,[MiddleName]
,[LastName]
,[UserID]
,[Password]
,[Address1]
,[Address2]
,[PostCode]
,[CityIdentity]
,[DOB]
,
,[CreatedBy]
,[CreatedOn]
,[IsActive])
VALUES
(
@adminuserid ,
@fname ,
@mname ,
@lname ,
@userid ,
@password,
@address1,
@address2,
@postcode,
@cityidentity ,
@dob,
@email,
@crtduser,
@crtdon ,
@isactive
)
SET @reccount=2
return @reccount
END
-- End of Query
IF (@mode='update')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @Count=1
--Query for Update the Records
BEGIN
update AdminUserMaster
set
[FirstName]=@fname ,
[MiddleName] =@mname,
[LastName]=@lname,
[userid]=@userid ,
[Address1]=@address1,
[Address2]=@address2,
[PostCode]=@postcode,
[CityIdentity]=@cityidentity,
[DOB]=@dob,
=@email,
ModifiedBy=@crtduser ,
ModifiedOn=@crtdon ,
isactive='Y'
where
AdminUserIdentity= @adminuserid
set @reccount=3
return @reccount
END
-- End of Query
END
I am not an expert with sp. I am trying to pass the column name as a parameter but I get the following error "Must declare the variable '@strColumnName'."
ALTER PROCEDURE dbo.TempGetDataForDropDown
(
@strColumnNamenvarchar (50)
)
as
EXEC ('SELECT DISTINCT @strColumnName = ' + @strColumnName + ' FROM Customers')
SELECT 'http=' replace (prot,'ww','+')',+'replace(city,'u','+')',+'proj_s tate'+'proj_zip5
INTO :ls_MAP FROM project4 WHERE proj_id=:gv_proj_id;
DECLARE @DBName varchar(100);
SET @DBName = 'TestTest';
CREATE DATABASE @DBName ON PRIMARY;
message is: wrong syntax near '@DBName'.
I am trying to run an sql query with a function in SQL SERVER MANAGEMENT STUDIO and am getting the following error.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'UserID'.
here is my Query
-----------------
USE vportal4vsearch
GO
CREATE FUNCTION dbo.ConcatUserGroups(@UserID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @groupname Varchar(2000)
SELECT @groupname = COALESCE(u.group_name + ', ', '') + CONVERT(varchar(20), u.group_name)
FROMdbo.[user] A
JOIN dbo.UserGroup_combo UG
ON UG.ug_userID = a.USER_ID
JOIN dbo.usergroup U
ON UG.ug_groupID = u.group_ID
WHEREA.USER_ID = @UserID
ORDER BY U.GROUP_NAME
RETURN @groupname
END
GO
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ' ' or user_username is Null then 'None' else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then 'Yes' else 'No' end),
dbo.ConcatUserGroups(UserID),
TheatreCode = (Case When User_TheaterCode = ' ' or User_TheaterCode is Null then 'N/A' else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ' ' or User_Jobtitle is Null then 'N/A' else User_Jobtitle end)
FROM [user]
GO
DROP FUNCTION dbo.ConcatUserGroups
GO
[CODE]
Private Sub pulllikehell()
Dim cn As SqlCeConnection
Dim rda As SqlCeRemoteDataAccess = Nothing
Dim sqlEngine As SqlCeEngine
Try
' Create database if it doesn't already exist
If (Not File.Exists("My DocumentsORDER.sdf")) Then
sqlEngine = New SqlCeEngine
sqlEngine.LocalConnectionString = "Data Source=My DocumentsORDER.sdf;"
sqlEngine.CreateDatabase()
sqlEngine.Dispose()
Else
' Open the connection to the database
cn = New SqlCeConnection("Data Source=My Documents" & _
"ORDER.sdf:")
cn.Open()
Dim cmd As SqlCeCommand = cn.CreateCommand()
' Drop the FieldMemos table
cmd.CommandText = "DROP TABLE STAFF"
cmd.ExecuteNonQuery()
' Close the connection
End If
' Instantiate the RDA Object
rda = New SqlCeRemoteDataAccess
' Connection String to the SQL Server.
Dim remoteConnectString As String = "Provider=SQLOLEDB;" & _
"Data Source=KANGALERT/SQLEXPRESS;" & _
"Initial Catalog=ORDER;"
rda.InternetLogin = ""
rda.InternetPassword = ""
rda.InternetUrl = "http://kangalert/server/sqlcesa30.dll"
rda.LocalConnectionString = "Data Source=My Documents" & _
"ORDER.sdf;"
rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
RdaTrackOption.TrackingOnWithIndexes)
MsgBox("ok")
Catch sqlex As SqlCeException
Dim sqlError As SqlCeError
For Each sqlError In sqlex.Errors
MessageBox.Show(sqlError.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
rda.Dispose()
End Try
End Sub
[/CODE]
computer server name is kangalert
and got 2 sqlservice run in my pc, SQLEXPRESS and MSSQLSERVER
DATABASE NAME IS ORDER with a TABLE call STAFF
when come to this statement
rda.LocalConnectionString = "Data Source=My DocumentsORDER.sdf"
ERROR MESSAGE: A request to send data to the computer running IIS has failed. For more information, see HRESULT.
when come to this statement
rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
RdaTrackOption.TrackingOnWithIndexes)
ERROR MESSAGE :A required property is not specified. [ Property name = SubscriberConnectionString ]
HOPEFULLY ANYONE CAN HELP ME SOLVE IT. THANKS IN ADVANCE
Whats wrong with my stored procedure i thought i am declaring the varible when i write this check it out can some one help me out thanks!!!!
CREATE PROCEDURE dbo.GetCompanyDEMO
[
@CompanyName nvarchar(40)
]
As
Select ContactName, ContactTitle, Address, City, Region, Phone
FROM Customers C
Where C.CompanyName = @CompanyName
1 SELECT
2 RowNumber,
3 'Source.Dbf, Plan.Dbf',
4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.'
5 FROM
6 SourceDbf f
7 JOIN
8 (
9 SELECT DISTINCT
10 SOURCE_NUM,
11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
12 WHEN 2 Then SRC2NAME
13 WHEN 3 THEN SRC3NAME
14 WHEN 4 THEN SRC4NAME
15 WHEN 5 THEN SRC5NAME
16 WHEN 6 THEN SRC6NAME
17 WHEN 7 THEN SRC7NAME
18 WHEN 8 THEN SRC8NAME
19 WHEN 9 THEN SRC9NAME
20 WHEN 10 THEN SRC10NAME
21 WHEN 11 THEN SRC11NAME
22 WHEN 12 THEN SRC12NAME
23 WHEN 13 THEN SRC13NAME
24 WHEN 14 THEN SRC14NAME
25 WHEN 15 THEN SRC15NAME
26 END
27 FROM
28 PlanDBF p
29 Where
30 p.PLAN_NUM = s.PLAN_NUM
31 ) as SourceName
32 FROM
33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM
i am getting an error on Line 33 and this what the error says...
Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
Any help will appreciated..
Regards
Karen
I have tried running this query multiple times with no success I get the following errorIncorrect syntax near '('.I tried with quotes and without quotes around the 10 and also without the brackets around variable. It runs when an integer in entered in the variables place but that is not what I want. What am I doing wrong DECLARE @p AS intSET @p='10'SELECT TOP (@p)* FROM my_tbl order by newid()
View 3 Replies View RelatedServer Error in '/reputation' Application.
Compilation Error 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: BC30456: 'InserParameters' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.Source Error:
Line 15: registrationDataSource.InsertCommand = "INSERT TO Reputation (firstname, lastname)VALUES(@First Name, @Last Name)"
Line 16:
Line 17: registrationDataSource.InserParameters.Add("firstname", firstname.txt)
Line 18: registrationDataSource.InserParameters.Add("lastname", lastname.txt)
Line 19: Source File: C:UsersQaiphyx
eputationDefault.aspx.vb Line: 17
Show Detailed Compiler Output:
strCommand = "SELECT * FROM tblevents WHERE startingDate=#"&startDate &"# AND eventtitle like '%"&criteria &"%' ORDER BY " &sSortSt I want to find any records that match a certain keyword on a specific date... But nothing comes up even though there is an event matching that criteria on the given date. Do I need brackets around my query or something?
View 6 Replies View RelatedEXEC sp_addlinkedserver
@server = 'LinkedServer', --Server Name
@provider = 'Microsoft.Jet.OLEDB.4.0', --Provider Name
@srvproduct = 'OLE DB Provider for Jet', --Product
@datasrc = 'C:DataX2000Master.mdb' --Data Source
Before the above query was okay.....
I used to run the same query as I today ran but I got this error message, any help pls.....
Server: Msg 7370, Level 16, State 2, Line 1
One or more properties could not be set on the query for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. The provider could not support a required property.
I cant get this to work for some reason i am clueless, here is my code:
And here is the error:
//////////////////////////////////////////////////////////
ADODB.Recordset error '800a0e79'
Operation is not allowed when the object is open.
/home/details.asp, line 106
/////////////////////////////////////////////////////////
Begin Code:
/////////////////////////////////////////////////////////
<%
Color = "#05D1B8"
rma_id = Request.QueryString("rma_id")
SQL = "SELECT * FROM TBL_RMA WHERE RMA_ID='"&rma_id&"'"
rs.open SQL, Conn
If Not rs.BOF Then
%>
<table>
<tr>
<td bgcolor="<%=Color%>">RMA ID</td>
<td bgcolor="<%=Color%>">DATE GENERATED</td>
</tr>
<tr>
<td><%=rs("RMA_ID")%></td>
<td><%=rs("RMA_DATE_GENERATED")%></td>
</tr>
</table>
</br>
<table>
<tr>
<td bgcolor="<%=Color%>">RMA STATUS</td>
</tr>
<tr>
<td><%=rs("RMA_STATUS_ID")%></td>
</tr>
</table>
<br>
<table>
<tr>
<td bgcolor="<%=Color%>">PART NUMBER</td>
<td bgcolor="<%=Color%>">DESCRIPTION</td>
<td bgcolor="<%=Color%>">SERIAL NUMBER</td>
</tr>
<tr>
<td><%=rs("RMA_PRODUCT_ID")%></td>
<td><%=rs("RMA_PRODUCT_ID_DESCRIPTION")%></td>
<td><%=rs("RMA_SERIAL_NUMBER")%></td>
</tr>
</table>
<br>
<table>
<tr>
<td bgcolor="<%=Color%>">RECEIVED DATE</td>
<td bgcolor="<%=Color%>">PRIORITY</td>
<td bgcolor="<%=Color%>">TRANSACTION</td>
</tr>
<tr>
<td><%=rs("RMA_PRODUCT_ARRIVAL_DATE")%></td>
<td><%=rs("RMA_PRIORITY_ID")%></td>
<td><%=rs("RMA_TRANSACTION_TYPE_ID")%></td>
</tr>
</table>
<table>
<tr>
<td bgcolor="<%=Color%>">PROBLEM DESCRIPTION</td>
</tr>
<tr>
<td><%=rs("RMA_PROBLEM_DESCRIPTION")%></td>
</tr>
</table>
<br>
<table>
<tr>
<td bgcolor="<%=Color%>">TO MFG</td>
<td bgcolor="<%=Color%>">VIA</td>
<td bgcolor="<%=Color%>">MFG RA</td>
</tr>
<tr>
<td><%=rs("RMA_TO_MFG_DATE")%></td>
<td><%=rs("RMA_TO_MFG_VIA")%></td>
<td><%=rs("RMA_TO_MFG_RA")%></td>
</tr>
<tr>
<td bgcolor="<%=Color%>">FROM MFG</td>
<td bgcolor="<%=Color%>">VIA</td>
</tr>
<tr>
<td><%=rs("RMA_FROM_MFG_DATE")%></td>
<td><%=rs("RMA_FROM_MFG_VIA")%></td>
</tr>
</table>
<table>
<tr>
<td bgcolor="<%=Color%>">PROBLEM FOUND</td>
</tr>
<tr>
<td><%=rs("RMA_PROBLEM_FOUND")%></td>
</tr>
</table>
<%
Else
Response.Redirect("http://usautomatic.dyndns.org/home/failed.asp")
End If
If rs.EOF Then
rs.Close
End If
%>
<%
rma_id = Request.QueryString("rma_id")
CUSTOMER_ID = session("CUSTOMER_ID")
SQL = "SELECT * FROM tracking_export.csv WHERE CUSTOMERID='"&CUSTOMER_ID&"' AND RMA_ID='"&rma_id&"'"
rs.open SQL, Conn2
If Not rs.BOF Then
%>
<tr>
<td><a href="http://wwwapps.ups.com/WebTracking/processInputRequest?HTMLVersion=5.0&sort_by=status&tracknums_displayed=5&TypeOfInquiryNumber=T&loc=en_US&InquiryNumber1=<%=rs("ShipmentID")%>&InquiryNumber2=&InquiryNumber3=&InquiryNumber4=&InquiryNumber5=&AgreeToTermsAndConditions=yes&track.x=32&track.y=6">Track</a></td>
</tr>
<%
Else
Response.Redirect("http://usautomatic.dyndns.org/home/failed.asp")
End If
If rs.EOF Then
RS.Close
End If
%>
/////////////////////////////////////////////////////////
Any help would be appreciated.
Thanks,
X-Centric
I am trying to sum a column together. This is what I am doing:
SELECT
sum(theoreticalTime= (Select
sum(rcp.theoreticalTime)
From
Traveler_X3 trv Join Recipe_X3 rcp On (trv.RecipeID = rcp.RecipeID)
Where
trv.Oper = rtsA.oper And
trv.RouteGroupID = (
Select
rgp.routeGroupID
From
Route_Group_X3 rgp
Where
rgp.routeGroupName = ( Select
rtl.RouteGroup
From
RouteList_X3 rtl
WHERE
rtl.route = rtsA.route
)
)
))
FROM
Routes_X3 rtsA
where
rtsA.route=( SELECT
lhm.route
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot = 'S6D0IQ002A' AND
lhm.oper = x3o.oper AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper)
)
and rtsA.seq BETWEEN ( SELECT
seq
FROM
Routes_X3
WHERE
oper =( SELECT
lhm.oper
FROM
brettb.pdash2.dbo.lothistorymoves lhm
WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm WHERE lhm.lot = 'S6D0IQ002A')
)
and route=( select top 1
lhm.route
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot = 'S6D0IQ002A' AND
lhm.oper = x3o.oper AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot ='S6D0IQ002A' AND lhm.oper = x3o.oper)
)
)+1 and 219;
What i got working was to put the query in a temp table called #temp1 and then run this:
select sum(theoreticalTime) from #temp1;
but isnt there a way to do this in one query?
Thank you for any help
Hi, I recently modified all the messages with severity 15 and 16 to be logged in windows applications log... I then discovered that my Application is generating lots of errors of type:
Error: 16945, Severity: 16, State: 1
The cursor was not declared.
followed by
Error: 16955, Severity: 16, State: 2
Could not create an acceptable cursor.
I think this error is occuring since a lot, but I discovered it now because I recently modified which messages are logged (I added those with severity of 15 and 16).
This error is occuring everytime (one error every 3 minutes!!)... I dont know what part of my app is having trouble, i think its a bug of sql server... Does somebody know anything about this error?
Thanks!!
DECLARE @Filter nvarchar(2048)
DECLARE @EventName nvarchar(2048)
DECLARE @Ticker nvarchar(2048)
DECLARE @Cusip nvarchar(2048)
SET @EventName = 'DIVIDEND'
SET @Ticker = 'IBM'
SET @Cusip = ''
SET @Filter = 'Name like ''' + @EventName + '%'' AND Ticker Like ''' + @Ticker + '%'' AND Cusip like ''' + @Cusip + '%'''
Select * from eventdatadetails
where @Filter
I want to execute the above SELECT statement and the filters in the where clause should come from the @Fitler variable... but query analyzer gives an error. If i replace the data in the @Filter with the variable in the query, everything works fine ...
table1 = 500 rows
table2 = 1071560
table1.col1 = table2.col2 = 359
table1.col1 <> table2.col2 = 535779641 <---- instead it should return 141..
whats wrong? seems like it s table2 rows X table1 rows..
I'm very new to SQL.
YOur help is appreciated.
Ok system_user is the main table.
All the CREATED_BY and LAST_UPDATED_BY columns in all tables are supposed to reference back
to the system_user table.
Anything with _TYPE (i.e. CONTACT_TYPE, ADDRESS_TYPE ) is supposed to map to common_lookup
table.
Heres the error I get:
ERROR at line 12:
no mathiching unique or primary key for this column-list.
Heres where I I think I have the mistakes.
Pks and FKs
Thank you.
CREATE TABLE system_user
( SYSTEM_USER_ID NUMBER
, SYSTEM_USER_NAME VARCHAR2(20)
, SYSTEM_USER_GROUP_ID NUMBER
, SYSTEM_USER_TYPE NUMBER
, LAST_NAME VARCHAR2(20)
, FIRST_NAME VARCHAR2(20)
, MIDDLE_INITIAL VARCHAR2(1)
, CREATED_BY NUMBER CONSTRAINT nn_system_user_1 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_system_user_2 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_system_user_1 PRIMARY KEY (SYSTEM_USER_ID, CREATED_BY, LAST_UPDATED_BY));
CREATE TABLE common_lookup
( COMMON_LOOKUP_ID NUMBER
, COMMON_LOOKUP_CONTEXT VARCHAR2(30)
, COMMON_LOOKUP_TYPE VARCHAR2(30)
, COMMON_LOOKUP_MEANING VARCHAR2(30)
, CREATED_BY NUMBER CONSTRAINT common_lookup_1 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT common_lookup_2 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_common_lookup_1 PRIMARY KEY (COMMON_LOOKUP_ID)
, CONSTRAINT fk_common_lookup_1 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY)
, CONSTRAINT fk_common_lookup_2 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE contact
( CONTACT_ID NUMBER
, MEMBER_ID NUMBER
, CONTACT_TYPE NUMBER CONSTRAINT nn_contact_1 NOT NULL
, LAST_NAME VARCHAR2(20)
, FIRST_NAME VARCHAR2(20)
, MIDDLE_INITIAL VARCHAR2(1)
, CREATED_BY NUMBER CONSTRAINT nn_contact_2 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_contact_3 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_contact_1 PRIMARY KEY (CONTACT_ID)
, CONSTRAINT fk_contact_1 FOREIGN KEY (CONTACT_TYPE)
REFERENCES common_lookup (COMMON_LOOKUP_ID)
, CONSTRAINT fk_contact_2 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY)
, CONSTRAINT fk_contact_3 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE address
( ADDRESS_ID NUMBER
, CONTACT_ID NUMBER CONSTRAINT nn_address_1 NOT NULL
, ADDRESS_TYPE NUMBER CONSTRAINT nn_address_2 NOT NULL
, CITY VARCHAR2(30)
, STATE_PROVINCE VARCHAR2(30)
, POSTAL_CODE VARCHAR2(20)
, CREATED_BY NUMBER CONSTRAINT nn_address_3 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_address_4 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_address_1 PRIMARY KEY (ADDRESS_ID)
, CONSTRAINT fk_address_1 FOREIGN KEY (CONTACT_ID)
REFERENCES contact (CONTACT_ID)
, CONSTRAINT fk_address_2 FOREIGN KEY (ADDRESS_TYPE)
REFERENCES system_user (COMMON_LOOKUP_ID)
, CONSTRAINT fk_address_3 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY);
, CONSTRAINT fk_address_4 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE telephone
( TELEPHONE_ID NUMBER
, CONTACT_ID NUMBER CONSTRAINT nn_telephone_1 NOT NULL
, ADDRESS_ID NUMBER CONSTRAINT nn_telephone_2 NOT NULL
, TELEPHONE_TYPE NUMBER CONSTRAINT nn_telephone_3 NOT NULL
, COUNTRY_CODE VARCHAR2(3)
, AREA_CODE VARCHAR2(6)
, TELEPHONE_NUMBER VARCHAR2(10)
, CREATED_BY NUMBER CONSTRAINT nn_telephone_4 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_telephone_5 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_telephone_1 PRIMARY KEY (TELEPHONE_ID)
, CONSTRAINT fk_telephone_1 FOREIGN KEY (CONTACT_ID)
REFERENCES contact (CONTACT_ID)
, CONSTRAINT fk_telephone_2 FOREIGN KEY (ADDRESS_ID)
REFERENCES address (ADDRESS_ID)
, CONSTRAINT fk_telephone_3 FOREIGN KEY (TELEPHONE_TYPE)
REFERENCES system_user (COMMON_LOOKUP_ID)
, CONSTRAINT fk_telephone_4 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY);
, CONSTRAINT fk_telephone_5 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CASE field_val
WHEN 'M5' THEN EXEC ( @query)
ELSE THEN print ''
END
I have a SP , and i state a query .. but i only
want this query to be executed based on the value
of field_val , otherwise i print a blank ..
but when i check the syntax (ms sql server 2000)
i get "invalid syntax near CASE ..
CREATE TABLE [dbo].[TestTable]( [Column1] [int] (SQL_Latin1_General_Cp437_BIN) not null, [Column2] [varchar] (7) not null, [Column3] [bit] not null, [Column4] [int] Null, [Column5] [varchar] (12) Null, [Column6] [varchar] (24) Null, [Column7] [varchar] (56) Null ) ON [Primary]
View 1 Replies View Related
Hi
I am running this piece of code to execute a package programatically and Yet I get Failure. What could be Wrong with this. If so can anyone tell me How I can debug in SSIS.
Imports Microsoft.SqlServer.Dts.Runtime
Module Module1
Sub Main()
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = _
"c:ssisintegration services project2integration services project2Package.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()
Console.WriteLine(pkgResults.ToString())
Console.ReadKey()
End Sub
End Module
Compilation Error
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.
original source code
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" SelectCommand="SELECT [authorID], [firstname] FROM [author] where id=1" ></asp:SqlDataSource>
<asp:Repeater ID="rpt" DataSourceID="SqlDataSource1" runat="server">
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Style="z-index: 100; left: 65px; position: absolute;
top: 59px" Text='<%# authorID %>' ></asp:TextBox>
<asp:TextBox ID="TextBox2" runat="server" Style="z-index: 101; left: 66px; position: absolute;
top: 96px" Text='<%# firstName %>'></asp:TextBox>
</ItemTemplate>
</asp:Repeater>
<asp:Button ID="Button1" runat="server" Style="z-index: 102; left: 46px; position: absolute;
top: 164px" Text="next" />
<asp:Button ID="Button2" runat="server" Style="z-index: 103; left: 102px; position: absolute;
top: 163px" Text="first" />
<asp:Button ID="Button3" runat="server" Style="z-index: 104; left: 156px; position: absolute;
top: 162px" Text="prev" />
<asp:Button ID="Button4" runat="server" Style="z-index: 106; left: 208px; position: absolute;
top: 162px" Text="last" />
</div>
</form>
Compiler Error Message: CS0103: The name 'authorID' does not exist in the current contextSource Error:
Line 13: <asp:Repeater ID="rpt" DataSourceID="SqlDataSource1" runat="server">
Line 14: <ItemTemplate>
Line 15: <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 100; left: 65px; position: absolute;
Line 16: top: 59px" Text='<%# authorID %>' ></asp:TextBox>
Line 17: <asp:TextBox ID="TextBox2" runat="server" Style="z-index: 101; left: 66px; position: absolute;
Hi,I have code on a page to update one table and insert info into another, but I cant make it work. I am new to coding and i think there are many mistakes. Please can someone pick out a few that need to be changed for the page to work.Code:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "ConnectionString"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable WHERE productID='@productID' = " + TextBox1.Text + command.ExecuteNonQuery(); return true; con.Close();} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "ConnectionString"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT Transactions SET Usersname = @UserName" + "; INSERT Transactions SET Itemid WHERE productID='@productID' = @productID; INSERT Transactions SET itemname = @Itemsname" + "; INSERT Transactions SET Date WHERE productID='@productID' = " + DateTime.Now.ToString() + "; INSERT Transactions SET Qty WHERE productID='@productID' = " + TextBox1.Text; command.Parameters.Add("@UserName", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); return true; con.Close(); }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) ); ExecuteInsert(Int32.Parse);} Current error message is:The best overloaded method match for 'detailproview.ExecuteInsert(string)' has some invalid argumentsto Line 74: ExecuteInsert(Int32.Parse); Thanks if someone can help!Jon
View 3 Replies View RelatedHello All, i am trying to connect to SQL Database using OleDb connection, but i keep getting error. here is my connection code. string strCon = "Provider=SQLOLEDB.1;Data source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"; OleDbConnection con = null; OleDbCommand cmd = null; con = new OleDbConnection(strCon); con.Open(); // deleting the exisitng one//
cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM Detail"; cmd.ExecuteNonQuery(); con.Close(); con.Dispose(); con = null;this is the error i am getting ..........No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). 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.OleDb.OleDbException: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).Source Error:
Line 43: OleDbCommand cmd = null;Line 44: con = new OleDbConnection(strCon);Line 45: con.Open();Line 46: // deleting the exisitng one//Line 47: cmd = con.CreateCommand(); can some please tell me where i am making the mistake. I really appreciate it.Thanks
UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1
I have wacked my head over this query I dont see anything wrong with it
any ideas?
Hello all.
Ive got the following SQL query and i want to return only distinct [ReviewID]'s, however SQL Server is complaining about it. This is the query:
SELECT DISTINCT Top 5 [ReviewID],[ReviewType],[ReviewTypeName],[LoginID],[LoginForename],[LoginSurname],[Approved],[ReviewDate]
,[Stars],[RelatedProductID],[Title],[Copy],[Rating], [Image1], [Image1Width], [Image1Height], [Image1AltText], [Image2], [Image2Width], [Image2Height], [Image2AltText],
[CategoryL4]
FROM [feManagedReview]
This is whats its complaining about:
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Any ideas where im going wrong?
Hi heres my script, why does it not work?
CREATE PROC [dbo].[sp_append_CemexSQLDB_to_EQuIS5_V2]
@DBINnvarchar(50),
@DBOUTnvarchar(50)
AS
DECLARE @SQLStrVarchar(1000)
/* Start Transaction, incase of error */
BEGIN TRAN
/* Appending Ref tables */
SET @SQLStr = 'INSERT INTO ' + QUOTENAME(@DBOUT) '.dbo.rt_action_level_type'
SET @SQLStr = 'SELECT *'
SET @SQLStr = 'FROM ' + QUOTENAME(@DBIN) '.dbo.rt_action_level_type;' + ' OPTION (KEEP PLAN)'
EXEC (@SQLStr)
IF @@ERROR <> 0 GOTO ERRORHANDLER
COMMIT TRAN
RETURN 0
ERRORHANDLER:
PRINT "Unexpected Error Ocurred!"
ROLLBACK TRAN
RETURN 1
GO
The error is 'Server: Msg 170, Level 15, State 1, Procedure sp_append_SQLtables_to_NewDB_V2, Line 13
Line 13: Incorrect syntax near '.dbo.rt_action_level_type'.'
can anybody help
Im getting way too many rows retured..what its trying to do is inserta 0 for revenue for months 7 - 12 (aka July through December) for eachof these cost centers for each payor type..Im getting a lot of repeatsand the concatenation field date always comes back as January 2003instead of the month and date its supposed to--Fiscal Yeardeclare @year smallintset @year = 2004--Month number the Fiscal year starts and endsdeclare @month smallintset @month = 7--Place holder for number of costcentersdeclare @cccounter smallint--loop counter for cost centersdeclare @ccount smallintset @ccount = 1--Place holder for number of payor typesdeclare @ptcounter smallint--loop counter for payor typesdeclare @pcount smallintset @pcount = 1--Temp table to store the blank values for all cost centers/payortypes for the fiscal yeardeclare @Recorded_Revenue_tmp table(Revenue money default 0,[Date] varchar(15),monthn smallint,yearn smallint,[CostCenter] varchar(50),[PayorType] varchar(50))--Temp table to store the values of the cost centersdeclare @costcenter_tmp table(ccid int IDENTITY (1,1),ccname varchar(50))--Inserts cost centers and code into the @costcenter_tmp temp tableinsert into @costcenter_tmp (ccname) select costcenter.fullname + ' '+ costcenter.code from costcenter, agency_cost_centerwhere costcenter.oid = agency_cost_center.cost_center_moniker--Sets the @cccounter variable to the number of cost centersselect @cccounter = count(*) from @costcenter_tmp--Temp table to store the values of the payor typesdeclare @payor_type_tmp table(ptid int identity (1,1),ptname varchar(50))--Inserts payor types into the @payor_type_tmp temp tableInsert into @payor_type_tmp(ptname)select fullname from payor_type,payorwhere payor_type.oid = payor.payor_type_moniker--Sets the @ptcounter variable to the number of payor typesselect @ptcounter = count(*) from @payor_type_tmp--Loop that gets the first part of the fiscal yearWhile (@month <13)begin--Loop that gets the value of the cost center to insertWhile (@ccount <= @cccounter)begin--Loop that inserts values for the first part of the fiscal year intothe @Recorded_Revenue_tmp temp tablewhile (@pcount <= @ptcounter)beginInsert into @Recorded_Revenue_tmp(Revenue, [Date], monthn,yearn,[CostCenter],[PayorType])select 0, datename(month, @month)+ ' ' + cast(@year -1 as varchar(4)),@month, @year -1, ccname, ptnamefrom @costcenter_tmp ct,@payor_type_tmp pt where ct.ccid = @ccount andpt.ptid = @pcountset @pcount = @pcount + 1endset @pcount = 1set @ccount = @ccount + 1endset @ccount = 1set @month = @month + 1endselect * from @Recorded_Revenue_tmpsample return data:(returns 16008 rows!!!)..0000January 200372003Genesis Assertive Community Treatment Team250SAGA..0000January 200372003Genesis Assertive Community Treatment Team250Self Pay..0000January 200372003Genesis Assertive Community Treatment Team250ABH..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercialthanks -Jim
View 2 Replies View Related