Syntax Error - Parameters In A Derieved Query
Jun 1, 2006
I am geting syntax error with the following query. I created a connection with SQL Native Client as Provider. I looks there is bug when we try to parameterise the derieved query
Select [EvidentiaryDataDefinitionHistoryID]
, EvidentiaryDataDefinitionID, Name, ModifiedUTCDate
From HistoryEvidentiaryDataDefinition HEDD (nolock)
Inner Join
(Select max([EvidentiaryDataDefinitionHistoryID]) AS EDDHistoryID
from HistoryEvidentiaryDataDefinition (nolock)
where ModifiedUTCDate >= ? and ModifiedUTCDate < ?
group by EvidentiaryDataDefinitionID,ModifiedUTCDate) T1
On T1.EDDHistoryID = HEDD.EvidentiaryDataDefinitionHistoryID
Regards
Koya
View 3 Replies
ADVERTISEMENT
Feb 17, 2008
Hi all,
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.
Please help and advise.
Thanks,
Scott Chang
View 18 Replies
View Related
Jan 23, 2008
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
View 12 Replies
View Related
Mar 15, 2008
I have modelled this query after another query which works fine and retrieves a result set. It accepts a search parameter and brings up all the websites in the database by searching on various columns for the search text. The article search query works the same way. I just changed the tables and parameters to match the tables. It uses the "view" and I had to go into the query and check a few columns in the query designer to add the column to the view.
Here's the query with the syntax error: 1 set ANSI_NULLS ON
2
3 set QUOTED_IDENTIFIER ON
4
5 GO
6
7 ALTER PROCEDURE [dbo].[_spArticleSearch]
8
9 @search varchar (100),
10
11 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, ArticleTitle, DateAdded DESC'
12
13 AS
14
15 EXEC('SELECT * from vArticle
16
17 where Active=1 AND ShowInDirectory=1 AND
18
19 (
20
21 Articletitle like ''%' + @search + '%''
22
23 OR Articletext LIKE ''%' + @search + '%''
24
25 OR ShortDesc LIKE ''%' + @search + '%''
26
27 OR Keywords LIKE ''%' + @search + '%''
28
29 IN (SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND (ACategoryName LIKE ''%' + @search + '%'' OR AParentID IN(SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND ACategoryName LIKE ''%' + @search + '%'')))
30
31 )
32
33 ORDER BY ' + @OrderBy )
34
35
36
When I run the stored procedure and input a word into the search box, and it gives me this:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
(1 row(s) affected)
This is the query it was modelled after which works fine and retrieves all the websites.
1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON3 GO4 5 ALTER PROCEDURE [dbo].[_spWebSiteSearch]6 @search varchar (100),7 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, TotalExchangedLinks Desc, SiteTitle, SiteURL, DateAdded DESC'8 9 AS10 11 EXEC('SELECT * from vWebSite12 where Active=1 AND ShowInDirectory=1 AND13 (14 sitetitle like ''%' + @search + '%''15 OR SiteURL LIKE ''%' + @search + '%''16 OR SiteDescription LIKE ''%' + @search + '%''17 OR CategoryID IN18 (SELECT CategoryID FROM tblCategory WHERE Active=1 AND (CategoryName LIKE ''%' + @search + '%'' OR ParentID IN(SELECT CategoryID FROM tblCategory WHERE Active=1 AND CategoryName LIKE ''%' + @search + '%'')))19 )20 ORDER BY ' + @OrderBy )21
Does anyone know where the problem may be? Do the columns in the view have to be in order? When I added my columns, it adds them to the very far right side of the view. Does the columns to be in order in the same way it searches in the query?
View 6 Replies
View Related
Feb 23, 2004
This thing is giving me 'Incorrect syntax near the keyword declare'. What's the correct form?
declare @Query varchar(8000)
set @Query = 'insert into PortfolioStock (PortfolioID, StockSymbol) select ' + cast(@Portfolio as varchar) + ', StockSymbol from PortfolioStock where StockSymbol in (''' + replace(ltrim(rtrim(@Textbox)), ' ', ''', ''') + ''')'
exec @Query
View 1 Replies
View Related
Jun 15, 2004
Ok I can run the query below in SQL Query Analyzer with no problems. However when I place the SQL query in my asp.net page I get a syntax error. It looks like there is some issue I am not seeing can someone help me. The error i get is "Incorrect syntax near 'pb_sub_recipes_1'. " I have narrowed it down to the area in bold as to where the syntax error appears to be occuring.
SELECT DISTINCT pb_customers.customer_name FROM pb_sub_recipes AS pb_sub_recipes_1 INNER JOIN ((((((pb_jobs INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id) INNER JOIN pb_recipes ON pb_jobs.recipe_id = pb_recipes.recipe_id) INNER JOIN pb_recipes_sub_recipes ON pb_recipes.recipe_id = pb_recipes_sub_recipes.recipe_id) INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id) INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id) LEFT JOIN pb_report_shippers ON pb_jobs.job_id = pb_report_shippers.job_id) ON pb_sub_recipes_1.sub_recipe_id = pb_recipes_sub_recipes.sub_recipe_id WHERE (((pb_jobs.date_time)> '5/30/2004') AND pb_customers.customer_id ='228' AND ((pb_report_shippers.shipper_date_time) Is Null) AND ((pb_jobs.job_deleted)=0)) GROUP BY pb_customers.customer_name, pb_jobs.date_time, pb_sub_recipes.energy,pb_sub_recipes.dose,pb_jobs.job_id,pb_sub_recipes.specie,pb_sub_recipes_1.cost_per_wafer, pb_sub_recipes_1.setup_cost pb_sub_recipes_1.wafers_in_batch"
View 2 Replies
View Related
Apr 28, 2005
Hi,
I am using SQL query analyzer and typed the following,
Update xupaddress as A1, xupaddress as B1
SET A1.address1 = upper(B1.address1)
set A1.address2 = upper(B1.address2)
set A1.city = upper(B1.city)
set A1.state = upper(B1.state)
where A1.ProfileID = B1.ProfileID
I am getting followin error :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Please point out what is wrong
Thank you
View 1 Replies
View Related
Mar 2, 2005
" ORDER ( [Accounting Date].[Account Week].Members , DESC ) ON ROWS "
what is wrong with this line???
View 9 Replies
View Related
Mar 4, 2004
Hi all
i am using a query
SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM
{oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'}
WHERE lcactivityT.licensedclient_id = '1'
This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a
t token ON , line 0 offset 84.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
.......
i removed the white spaces and i also have the correct driver, what can i do?
Thanku
View 4 Replies
View Related
Dec 14, 2006
I cannot identify where the syntax error is, please help.
INSERT INTO [Attendence/Activity Log] (ID Number, Date, Activity, Duration) VALUES ('39', '12/14/06', 'Health & Nutrition', '2')
View 1 Replies
View Related
Feb 6, 2007
Hi all,
I use
="<Query><XmlData>" & Parameters!XMLData.Value & "</XmlData><ElementPath>Product {@}</ElementPath></Query>"
and I enter XMLData with value "<Products><Product>Chair</Product><Product>Table</Product></Products>"
for the query expression of the dataset, and I always got error:
Query failed for dataset "Dataset1"
Incorrect syntax near '<'
What's wrong with it?
Thanks,
Jone
View 1 Replies
View Related
Feb 28, 2008
I am not able to use WHERE Clause in my query. What am I doing wrong?
Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'
Using SQL Server 2000
DSN to a CACHE database on local network
Thanks in advance,
Sam
View 3 Replies
View Related
Jan 22, 2008
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
View 1 Replies
View Related
Jan 31, 2008
I'm getting the error listed above. To create this I used the wizard in VS2005 for the datagrid. The delete works but not the updating. I can't seem to find what's wrong. In the update parameters I've removed eventID and added it, neither works. Delete does work. Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>
View 4 Replies
View Related
Apr 27, 2008
Hi,
I am having trouble getting the following query to execute in the report builder:
OPEN Symmetric KEY DemoKey Decryption BY Certificate DemoCertificate;
SELECT
PIN,
EffDate,
Cast(DecryptByKey(Address1) AS varchar(200)) AS Address1,
Cast(DecryptByKey(Address2) AS varchar(200)) AS Address2,
Cast(DecryptByKey(Address3) AS varchar(200)) AS Address3,
Cast(DecryptByKey(City) AS varchar(125)) AS City,
Cast(DecryptByKey(State) AS varchar(125)) AS State,
Cast(DecryptByKey(ZipCode) AS varchar(90)) AS ZipCode,
Cast(DecryptByKey(Country) AS varchar(125)) AS Country
FROM Address
CLOSE Symmetric KEY DemoKey;
I am getting the following error when I try to generate a report in the report builder:
Semantic query execution failed. Incorrect syntax near the keyword 'Open'.
Incorrect syntax near ')'.
----------------------------
Query execution failed for data set 'dataSet'.
----------------------------
An error has occurred during report processing.
I was getting a similar error when I tried to enter the query using the Named Query window of the dsv file. However, I was able to overcome this obstacle by entering the query directly into the code of the dsv file and it works perfectly. That is, when I click on "Explore Data" in the dsv design view the data is retrieved from the database and is decrypted properly.
It appears that the report builder's semantic query engine does not accept transact-sql statements that involve data decryption. Does anybody know if this is true? Or is there some workaround for this situation? Any help would be greatly appreciated.
Thanks,
Mark
View 5 Replies
View Related
Jan 23, 2008
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
Thanks,
Lee
View 4 Replies
View Related
May 12, 2008
Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating Dim quantity As Integer Dim weight As Integer Dim packed As Integer quantity = CInt(bagsOnPallet.Text) weight = CInt(lbsInBags.SelectedValue) packed = weight * quantity e.Command.Parameters("@packedLbs").Value += packed e.Command.Parameters("@AvailableLbs").Value -= packed End Sub
View 4 Replies
View Related
May 10, 2004
Hi all,
I had two Nvarchar(255) columns & retreive it like this
DIM CN1 AS SQLCONNECTION
DIM DR1 AS SQLDATAADAPTER
DIM DTABLE AS DATATABLE
DIM DTSET AS DATASET
DTSET=NEW DATASET()
DR1=NEW SQLDATAADAPTER("SP_PRO1",CN1)
DR1.SELECTCOMMAND.COMMANDTYPE=COMMANDTYPE.STOREDPROCEDURE
DR1.SELECTCOMMAND.PARAMETERS.ADD("@TYPE1", REQUEST.QUERYSTRING("PID"))
DR1.SELECTCOMMAND.PARAMETERS.ADD("@TYPE1", REQUEST.QUERYSTRING("UID"))
DR1.FILL(DTSET,CINT(INTCURRINDEX.TEXT),CINT (INTPAGESIZE.TEXT),"SP_PRO1")
DL1.DATASOURCE=DTSET.TABLES(0).DEFAULTVIEW
DL1.DATABIND()
CN1.CLOSE()
END SUB
How to insert "N" National Code to parameter? I try it N"@TYPE1" or "N@TYPE1" with failed. Any ideas or advise? Thanks a lot.
Aris
View 2 Replies
View Related
Feb 23, 2007
I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?
I'm mostly an Oracle and PL/SQL type so I'm a little lost here...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"
AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"
Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
<HeaderStyle HorizontalAlign="Left" />
<Columns>
<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />
<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"
ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"
SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">
<SelectParameters>
<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
Dec 30, 2007
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
View 5 Replies
View Related
Dec 15, 2004
Can anyone tell me syntax for executing a stored procedure using osql which accepts 4 input parameters which are char data types?
I am getting syntax errors all the time..
View 8 Replies
View Related
Sep 1, 2014
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
View 9 Replies
View Related
Dec 27, 2000
I'm trying something like this:
CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
BEGIN TRANSACTION
INSERT INTO Junk (Dist)
VALUES (@Dist)
COMMIT TRANSACTION
select @CheckNo=@@IDENTITY
If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?
PS I'm using Microsoft SQL 7.0
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Apr 20, 2007
Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07
View 2 Replies
View Related
Jan 7, 2004
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO
Thanx in front
Cya
View 14 Replies
View Related
Nov 24, 2004
Hello,
the following alter table statement:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED
is answered with:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.
which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.
Note that:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL
DOES get executed, and
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY
produces the same error.
Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.
So - anyone an idea?
View 4 Replies
View Related
Aug 1, 2004
Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.
CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)
Thanks,
View 2 Replies
View Related
Nov 30, 1998
I recently wrote a query similar to this:
Select e.name, e.employee_id, d.department_name
from employees e,departments d
where e.department_id = d.department_id
An associate recommended that I change this query to add the words INNER JOIN like this:
Select e.name, e.employee_id, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id
I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster.
Would anyone like to confirm this?
View 2 Replies
View Related
Aug 29, 2007
Hi.. I want to join this query
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0
in this query
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
The Place where i need to Join is after
Code:
iCalls_Status.Status_ID,iCalls_Status.Status_Label
and before
Code:
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )
I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec
The Final Query would be something like this
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
but this syntax is not correct..Please can U get me the Correct Syntax.
View 7 Replies
View Related
Jan 13, 2005
I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------
View 1 Replies
View Related
