I have a couple of queries that i run on my server but i need to automate them now. I have created a new job in the sql agent jobs and set up my steps accordingly.
My queries all run in a query window without trouble and they also parse in the command window of the sql agen job-step screen. When I run them manually, it fails with the first query moaning about my variables etc etc.
Is there some sort of limitation that i am not aware of or something?
Here is the following mind-numbing problem I have (and wished I did not have to experience)
A set of 2 SSIS packages is scheduled to run in a sql server agent job on the same server. Both packages use an environment variable that point to a package configuration file. In this file there are 2 connections, one to a sql server with a sql server user id and passwordn, another to an AS400 DB2. Both packages are deployed on the same server in SSIS server under MSDB sql storage, with package protection level set to 'rely on server storage and roles for access control'.
Today the connection to the As400 needed to change, it is now connected to another AS400 server. The packages have been modified to use the new connection. In the configuration file the old connection has been commented out and the new connection string was added, the connection itself was given a new more meaningfull name in the packages.
Running the packages from visual studio 2005 works. After testing I have deployed the packages to SSIS server in MSDB storage.
Now when I start the sql server agent job that runs these packages, the job quits with an error, in the history I see an error message that it failed to connect to the sql server with the given sql server user account.
When I the step in the sql server agent job properties for both packages, under the Tab 'Data sources' I see that it is using the new AS400 connection. I can also see the connectionstring for the sql server with the user id (but no password).
To make it possible for my packages to run (the users are waiting for the data) I have solved it like this:
- under the 'configurations' tab I have added the name of my package configuration file.
- i did this for both packages in both job steps
when I run the job , it works without problems.
Now , my question is: I have hardcoded the pathname for the package configuration file in my job. Instead of the package using the environment variable to find th epackage configuration file. I would prefer my packages , when in a sql server agent job to also use the environment variable. What can I do to make this happen?
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
I'm new to using xp_sendmail, and here is what I get... ===================================== EXEC master..xp_sendmail @recipients = 'amccollough', @subject = 'EMAIL TEST', @message = 'This is a test' (result="Mail Sent", it works fine) ===================================== But this one fails... ===================================== EXEC master..xp_sendmail @recipients = 'amccollough', @subject = 'EMAIL TEST', @message = 'This is a test', @query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES' (result="Server: Msg 17969, Level 16, State 1, Line 0 Unable to login to SQL Server.") ===================================== I've tried tons of variations with adding in @dbuse and @set_user, and am totally lost as what to try next. Anybody have suggestions?
I created a job using the SQL Server Agent of type Operating System Command (CmdExec). The command was to open a web page that will execute a series of batch programs. I tried running the job I created several times and according to the history of the job, it was successful. However, I checked the supposed changes to some records in the database and no changes were done. I also checked the trace log of the web page but there was no information logged.
I'm convinced that the problem is not on the web page since when I tried manually opening the web page through the browser, the changes were done successfully.
For reference, below is the script of the job:
USE [msdb] GO /****** Object: Job [try] Script Date: 05/05/2007 11:06:58 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/05/2007 11:06:58 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
x.value('Description[1]','VARCHAR(255)') as description,
x.value('Url[1]','VARCHAR(255)') as url,
x.value('Orderable[1]', 'VARCHAR(255)') AS orderable,
x.value('Taxable[1]', 'VARCHAR(255)') AS taxable,
x.value('Path[1]', 'VARCHAR(255)') as htmlpath,
x.value('Caption[1]', 'VARCHAR(255)') as caption,
x.value('Thumb[1]', 'VARCHAR(255)') AS thumb,
x.value('Picture[1]', 'VARCHAR(255)') AS picture,
x.value('(Pricing/BasePrice)[1]', 'MONEY') AS baseprice,
x.value('(Pricing/LocalizedBasePrice)[1]', 'MONEY') AS localizedbaseprice,
x.value('(Pricing/OriginalPrice)[1]', 'MONEY') AS originalprice,
x.value('(Pricing/LocalizedOrignalPrice)[1]', 'MONEY') AS localizedoriginalprice,
x.value('(Pricing/SalePrice)[1]', 'MONEY') AS saleprice,
x.value('(Pricing/LocalizedSalePrice)[1]', 'MONEY') AS localizedsaleprice,
x.value('Availability[1]', 'VARCHAR(255)') AS availability,
x.value('Weight[1]', 'VARCHAR(255)') AS weight,
x.value('OptionLists[1]', 'VARCHAR(255)') as optionlist
FROM @x.nodes('/StoreExport/Products/Product') s(x)
--SELECT * FROM Products
This workings fine, but when I add it to the server agent to run nightly it fails.
The only thing I see for the error is: Message Executed as user: NT AUTHORITYSYSTEM. INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Can some one explain what happened and how to fix this Dee
Today, my server began refusing connections for no apparent reason. I'm getting 2 different errors. 1. Server rejected the connection; Access to the selected database has been denied. (from one remote DSN) 2. Client unable to establish connection. (from another DSN)
It seems as if my server has lost it's mojo!!?? I have no clue as to why this is happening all of a sudden. Please help.
thanks,
Joe
Also, is there a better way I can trace my SQL Server ODBC errors (other than through Profiler)?
THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column. "Cannot insert the value NULL into column UID column does not allow" Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed. Is this common? THe source and destination tables are in the same databes.
When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.
The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.
If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?
Hello All, I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. The searchString, Type and the Location (where the user who is searching belongs to). When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.For example: Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c. So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters. Any help would be greatfull. Query: SELECT Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @SR + '%' OR [Client.SSN] LIKE '%' + @SR + '%' OR [Client.LastName] LIKE '%' + @SR + '%' OR [Client.FirstName] LIKE '%' + @SR + '%' OR [Client.MiddleName] LIKE '%' + @SR + '%' )
I've gotten spoiled with all these query builders. Now in SQL server management studio express its gone. I don't understand whats wrong with their query....any help would be appreciated.
SELECT [Products].myID, [ProductDetails].ShortName FROM [Products] INNER JOIN [ProductDetails] ON [Products].DetailID = [ProductDetails].myID
Parse comes back asCommand(s) completed successfully. Execute comes back asMsg 208, Level 16, State 1, Line 1Invalid object name 'Products'.
Hi, I'm using SQL SERVER 7.0. I'm trying to run the following query: ------------------------------------------------------------------------- DECLARE @DATABASENAME VARCHAR(255), @TABLENAME VARCHAR(255)
EXEC("SELECT @RUN_ID = (select run_id from " + @DATABASENAME + ".." + "t905_run_statistics where TABLE_NAME_NM = '" + @TABLENAME + "' and DATE_TABLE_LOAD_END_DTE is null)")
select @run_id, @dataBasename, @taBlename --------------------------------------------------------------------------- But I'm getting this error when I run it:
Server: Msg 137, Level 15, State 1, Line 0 Must declare the variable '@RUN_ID'.
I've been over this about 1,000,000 times but I can't figure out what I'm doing wrong. Can anyone help me out?
My problem is that I am not able to select a distinct set of results of all users and the ImageName if that user has an image which is approved and ProfileImage is 'yes' or '1'. Can anyone help me with writing the correct SQL for this with some basic explanation. Thanks!!
I run a sproc through query analyzer, and all is fine, when I run the job via the SQL Agent, [SQLSTATE 01000] appears after every line in the "log" file, any ideas ?
When running the snapshot agent for a new publication I get the message:
Message: Query for data failed Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile) at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider) at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem) at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc() at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0) Get help: http://help/0 Message: COLV_90_TO_80: Cannot convert parameter param1: Resulting colv would have too many entries. Stack: (Source: MSSQLServer, Error number: 20005) Get help: http://help/20005 Message: The statement has been terminated. Stack: (Source: MSSQLServer, Error number: 3621)
The database has another publication, that was created prior to upgrade to sql server 2005 from sql server 2000 sp4. This publication is working ok, and there are no problems when running the snapshot agent for this publication.
The publications are using filtered articles, and all properties for the publications are the same.
Any help to solve this problem will be much apreciated.
Hi;I'm here for many hours trying to do this but i couldn't find a way.I have a table whith a field called [DOB], where i have people's date of birth. Now, i need a SQL query to get people who's birthdays are in between two dates "BUT", what about the year on the date? I use to do this on Access: SELECT * FROM Members WHERE DATESERIAL(YEAR(NOW()), MONTH(DOB), DAY(DOB)) BETWEEN @startDate AND @endDate In the query above the year is not a problem because the DateSerial() function add the current year for all birthdates making it easyer to user parameters like: 06/01/2006 to 06/30/2006 Unfortunately, SQL Server does not support DateSerial() function. I appreciate any help on this. Thanks a lot.
I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_idI'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.SELECT DISTINCT times.employee_id, employee.employee_nameFROM employee INNER JOIN times ON employee.employee_id = times.employee_id WHERE (times.job_id <> @job_id) Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
Hello, I m stuck here with a very nasty problem. I am selecting data from different tables based on the search criteria of the user. But when i run the following quesry it does not give any results in my script.
select s.* from my_profile m,signup s where s.gender='male' and s.age between '18' and '26' and eye_color in (7) and bodytype in (6) and languages in ('11, 15') and ethnicity in ('1, 3, 5') and religion in (12) and occupation in (4) and education in (7) and income_level in (4) and drinking in (4) and smoking in (1) and relationship in (5) and want_children in (2) and m.distance='10' and zip='9988' and s.sid=m.sid
The "languages in ('11, 15') and ethnicity in ('1, 3, 5') " part fails the query in the script. If I remove this part it works fine in both SQL and Asp script.
Please help me out!! This problem is pretty weird and cant make out why does this happens.. Any help will be greatly appreciated!
In my M/c the query analyser (SQL 2000) is not working when ever I try to run it by Using Enterprise manager / Explorer / Start menu - Program / Isqlw.exe.
When I Tried the task manager it shows the process isqlw is working. But I can't see any window coming up ..
Please help me otherwise I will go mad....using other tools to query . 50 % time i use this tool in work. U guys know how important it is .. Thank u
EntertainmentType typeId PK tinyint type varchar(30)
Entertainment id PK int typeId FK tinyint title varchar(35) description varchar(300) purchaseDate smalldatetime
CheckedOut recordId PK int id >> dunno if I should make this a foreign key - relates to Entertainment.id checkOutDate smalldatetime dueBackDate smalldatetime userId returned bit
It is actually has a relationship that is similar to a regular customers, orders set of tables.
I have a list of movies and every time a movie is checked out a record gets added to the checkedout table. So while there is 1 of each movie in the entertainment table ... the movie may be referred to in the checkedout table multiple times ...
The result set that I am trying to get, and that i've spent all day on, is - all the movies and an indication of whether they are currently available for checkout.
i have the following, which I also had help with ...
select * from entertainment where entId not in ( select entId from checkedout where -- checks if dates conflict, assume 2 days checkout ( checkOutDate > dateadd(d,2,getdate()) or dueBackDate < getdate() ) or -- checks if current booking returned and is now available ( checkOutDate < getdate() and dueBackDate > getdate() and returned = 'true') )
though this returns a list of all the movies that are currently available for checkout. I need to be able to show all the movies that I have, so that someone knows that I have it even if its not available right now. The relationship is very similar to a customers - orders set of tables and I suppose the equivalent would be asking for a list of all the customers indicating the lastest product they bought ...
If I replace not in with exists I get the desired result but it won't work with a join so I don't know how to indicate if its available or not. Does anyone have any suggestions ... I appreciate any help you can provide ...
I am unable to execute the following query against SqlCE 3.1. Could someone guide me what is wrong.
SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1 FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06';
Error Message: Major Error 0x80040E14, Minor Error 25501 > SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1 FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06' There was an error parsing the query. [ Token line number = 1,Token line offset = 46,Token in error = THEN ]
But when I execute foloowing queries: 1) select MAX(CONTENT_NUMBER)from PRELIMINARY_CODES; Result: NULL
We've got a problem with our replication. If I try to run the snapshot agent I get the message "Query for data failed". The detail view of this message shows the following:
Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @@error, but when you run the same stored procedure in a query window the error handling does get run?
Hi, The following INSERT query works in all aspects apart from the date value: String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text); The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900"). In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT. What am I doing wrong? Thanks very much. Regards Gary
I am trying to move my application (asp.net) from a non-paged select to a paged query.
I am having a problem. Below is my Stored Procedure. The first Query in the procedure works...the rest (which are commented out ALL work interactively, but fail when the program tries to access....The ONLY THING I change is the stored procedure I switch the comment lines to the non-paged procedure and it works, I try to use the any of the paged procedures and it fails with the same error (included below)
I can't see where any of the queries are returning different results. I have also included the program abort that happens, it is the same for all of the paged queries.
ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment ( @EquipmentTypeId int, @StartRowIndex int, @MaximumRows int ) AS
-- ************************************************************************************************ -- Non-Paged OUTPUT -- THIS WORKS!!!!! SET NOCOUNT ON SELECT Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0
/* -- ************************************************************************************************ -- Using a Temp Table --THIS WORKS INTERACTIVELY, But NOT When Called by the program SET NOCOUNT ON create table #PagedEquipment ( IndexId int IDENTITY(1,1) Not NULL, EquipId int )
-- Insert the rows from Equipment into the PagedEquipment table Insert INTO #PagedEquipment (EquipId) select EquipmentId From Equipment WHERE IsDeleted = 0
SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1) */
/* -- ********************************************************************************************** --Using the With to create a temp table (in memory)..works interactively but fails when --called by the application.. --THIS WORKS INTERACTIVELY, But NOT When Called by the program Set NOCOUNT ON; With PagedEquipment AS ( SELECT EquipmentId, ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber FROM Equipment WHERE EquipmentTypeId = @EquipmentTypeId AND IsDeleted = 0 )
SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType FROM PagedEquipment INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows) return -- ******************************************************************************************* */
/* -- ******************************************************************************************** --nested selects --THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM SET NOCOUNT ON Select * From ( Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber, Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0 ) equip Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows ) -- ************************************************************************************************ */
Server Error in '/pUse' Application. --------------------------------------------------------------------------------
Arithmetic overflow error converting expression to data type int. 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: Arithmetic overflow error converting expression to data type int.
Source Error:
Line 148: { Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>(); Line 150: while (reader.Read()) Line 151: equipment.Add(GetEquipmentFromReader(reader)); Line 152: return equipment;
Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlDataReader.HasMoreRows() +150 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212 System.Data.SqlClient.SqlDataReader.Read() +9 PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150 PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Hi folks,Hopefully this is a simple fix, but I keep getting Syntax error withthis statement in an MS SQL DTS statement and in Query Analyzer:Update A Set A.deptcode = A.deptcode,A.type = A.Type,a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit)Where a.Type in ('FYTD04', 'Prior Year','Budget')From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeBelow is the error from Query Analyzer:Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'From'.Where do I place my Where..In statement since I only want to limit theUpdate to run for items where a.type is FYTD04, Prior Year, or Budget?Thanks,Alex.
