T-SQL (SS2K8) :: Proc Giving Error In One Session And Runs In Another
Nov 13, 2014
I have one drop and create procedure script in one sql session and execute the procedure in other session. Now when i am compiling the procedure in first session, it gets completed successfully.
Now when i move to other session and try to execute the procedure, it gives me error saying :
"Invalid column name 'ColumnName'."
Now when I execute the procedure in same session in which procedure was compiled, it runs successfully. Once run, I go back to my other session and it runs again.
Hello!Is it possible to pass a stored procedure a parameter, say @table anduse it as a table in the sql command?Finally i want a proc to copy tables from a database to another database.THERE IS MY CODE:CREATE PROCEDURE [user].[copytable]@dbSRC varchar(100),@dbTRGT varchar(100),@table varchar(100)ASBEGIN TRANSACTION FreeAndCopyTableTRUNCATE TABLE [@dbTRGT].admin.[@table]INSERT INTO [@dbTRGT].admin.[@table]SELECT *FROM [@dbSRC].admin.[@table]COMMIT TRANSACTION FreeAndCopyTableIF @@error <0 GOTO E_Copy_FailE_Copy_Fail:ROLLBACK TRANSACTION FreeAndCopyTableGOthanks in advance,muh
We are having issues w/ a stored proc call in our application. When I run the proc through a query window in Mgmt Studio, it comes back in .3 seconds. However, when the application runs the proc (w/ the same parameters), it takes 30 seconds for the proc to complete. When I run a trace with the proc call through Mgmt Studio, it says 4000 read, but through the app, it says 4 million reads. What is happening?? (app uses Hibernate 3.2/ Java 1.5/ JDBC)
Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?
I suspect this is very poor design but I've been asked to research this: can I maintain a persistent communication session inside an sqlclr proc? (tcp/binary packets)
The process has to maintain very high throughput - setting up and tearing down a connection on each transaction is cost prohibitive. For TCO and deployment reasons it is preferable not to have a separate windows service etc that accepts requests from inside the clr proc. Is there the luxury of an alternative integrated into the database?
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable
select ClaimNumber, Surname, FirstName, CASE WHEN New= 0 THEN 'Yes' ELSE 'No' END AS Processed
from EntryTable) Select * from ProcessedYesNo Where Ignore_dupe='Yes';
why I am getting an syntex error I check the query it is ok atleast the inner query is running but when I apply the with than I am getting Syntax error near 'WITH'
I have imported a DTS package to sql 2005. I am trying to run this DTS package from the JOB using DTSRun. and i am getting the following error. I thought it was some issue with the SQL Agent serivice account which this is running, i have created a proxy which has super privilages.. but still the job is failing. When i run the DTS package directly it's running fine.
I am confused !!!!!
Message Executed as user: AMRsql_seasdv. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed. Dev
when i try to run this following query, i get an error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The ntext data type cannot be selected as DISTINCT because it is not comparable.
/resultados_termo.asp, line 176
the query:
select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' )
when i use UNION ALL, i get repeated rows. i need the select distinct on it.
since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.
I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?). I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).
We have a table of students with fields for the ID of the school district and their school name. For example:
DistrictID StudentName SchoolName
10001 John Smith Washington Elementary
10001 Jane Smith Lincoln Middle
10002 David White Hill High
... ... ...
I want is a listing with the school district ID, the student name, and the number of schools in the district.
I believe the following statement should give me what I want:
SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)
FROM StudentData
However, I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'distinct'.
If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want. If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want. I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.
The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference: Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx
Anyone have an idea one what I'm doing wrong or how to go about getting this information? Thanks! Matt Penner
I'm trying to install Sql Server 2005 Express Edition making it as one of the pre-requisities of an windows app using click once deployment tech and in one system every time i'm getting an error that "An error occured attempting to install myappname". Here its pointing to an log file called install.log which is as follows-
The following properties have been set: Property: [AdminUser] = true {boolean} Property: [ProcessorArchitecture] = Intel {string} Property: [VersionNT] = 5.1.2 {version} Running checks for package 'Microsoft Data Access Components 2.8', phase BuildList Reading value 'FullInstallVer' of registry key 'HKLMSoftwareMicrosoftDataAccess' Read string value '2.81.1117.0' Setting value '2.81.1117.0 {string}' for property 'MDACVersion' The following properties have been set for package 'Microsoft Data Access Components 2.8': Property: [MDACVersion] = 2.81.1117.0 {string} Running checks for command 'MDAC28mdac_typ.exe' Result of running operator 'VersionGreaterThanOrEqualTo' on property 'MDACVersion' and value '2.80': true Result of checks for command 'MDAC28mdac_typ.exe' is 'Bypass' 'Microsoft Data Access Components 2.8' RunCheck result: No Install Needed Running checks for package '.NET Framework 2.0', phase BuildList Running external check with command line "C:DOCUME~1JOEMER~1LOCALS~1TempVSD567.tmpdotnetfxdotnetchk.exe" Process exited with code 1 Setting value '1 {int}' for property 'DotNetInstalled' Reading value 'Version' of registry key 'HKLMSoftwareMicrosoftInternet Explorer' Read string value '7.0.5730.11' Setting value '7.0.5730.11 {string}' for property 'IEVersion' The following properties have been set for package '.NET Framework 2.0': Property: [DotNetInstalled] = 1 {int} Property: [IEVersion] = 7.0.5730.11 {string} Running checks for command 'dotnetfxinstmsia.exe' Result of running operator 'ValueExists' on property 'VersionNT': true Result of checks for command 'dotnetfxinstmsia.exe' is 'Bypass' Running checks for command 'dotnetfxWindowsInstaller-KB893803-v2-x86.exe' Result of running operator 'ValueExists' on property 'Version9x': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.0.3': false Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionMsi' and value '3.0': true Result of checks for command 'dotnetfxWindowsInstaller-KB893803-v2-x86.exe' is 'Bypass' Running checks for command 'dotnetfxdotnetfx.exe' Result of running operator 'ValueNotEqualTo' on property 'DotNetInstalled' and value '0': true Result of checks for command 'dotnetfxdotnetfx.exe' is 'Bypass' '.NET Framework 2.0' RunCheck result: No Install Needed Running checks for package 'Windows Installer 3.1', phase BuildList The following properties have been set for package 'Windows Installer 3.1': Running checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe' Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionMsi' and value '3.1': true Result of checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe' is 'Bypass' 'Windows Installer 3.1' RunCheck result: No Install Needed Running checks for package 'SQL Server 2005 Express Edition', phase BuildList Running external check with command line "C:DOCUME~1JOEMER~1LOCALS~1TempVSD567.tmpSqlExpressSqlExpressChk.exe" Process exited with code 0 Setting value '0 {int}' for property 'SQLExpressInstalled' The following properties have been set for package 'SQL Server 2005 Express Edition': Property: [SQLExpressInstalled] = 0 {int} Running checks for command 'SqlExpresssqlexpr32.exe' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': true Result of checks for command 'SqlExpresssqlexpr32.exe' is 'Bypass' Running checks for command 'SqlExpresssqlexpr32.exe' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': true Result of checks for command 'SqlExpresssqlexpr32.exe' is 'Bypass' 'SQL Server 2005 Express Edition' RunCheck result: No Install Needed Launching Application. Running command 'http://reporterapp.stats.com/reporter/BasketballDataCollection.application' with arguments '' ShellExecuteEx failed with error code 1155 Error: The following error occurred attempting to install 'http://reporterapp.stats.com/reporter/BasketballDataCollection.application': "No application is associated with the specified file for this operation. "
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night. It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information: - It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node). - The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access. - When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running. - Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
When I try to manually run one of my scheduled job, DR. Watson comes up and SQL Server crashes. the following error message is received in the NT event log. "Unable to read local Event log, reason the data area passed to a system call is too small". I am new to SQL and this is causing me some degree of stress!!!! Also please recommend a good book for a newbie like me.
I am using ActiveX Script tasks to branch out on the different tasks that I have within the DTS. When I want a particular branch of tasks not to be executed, I give a "Main = DTSStepScriptResult_DontExecuteTask"
This seems to be working fine in terms of functionality, but there is an error that is displayed saying "Task reported failure on execution".
How do I get rid of this? I need to exit the task, and still just get one message box at the end of it all that happily reads "Succesfully completed tasks"
IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]')) begin alter table [Case] alter column Case_Escalated__c bit null print 'Case_Escalated__c altered sucessfully for [Case]' end
Msg 1792, Level 16, State 1, Line 3 Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.
A simple alter statement to populate a column is giving the error below. UPDATE AM_PROFILE_4101_0 SET _92284 = CONVERT(varchar(1000),fv.varcharValue) FROM AM_PROFILE_4101_0 pt INNER JOIN cyfield_value fv ON fv.fieldID = 92284 AND pt.AM_PROFILE_ID = fv.AMid
Cannot create a row of size 8125 which is greater than the allowable maximum of 8060. The table as many varchar(max) columns such as _92284, and many are populated with around 1000 bytes of data. Everything I'm reading tells me SQL 2005 supports large row sizes. But why is this error still coming up.
Other usage info: These numbered columns (i.e. _92284) are dropped, added, and repopulated with data every night.
I am working on a SSIS solution that has 4 packages. All 4 have run before and I have not modified any of them, however I keep getting the error message and it keeps locking up on one of my packages. Does anyone have any advice or a remedy for this?
I am trying to access a .Net assembly in script component, which internally uses Microsoft Enterpise library dll's.
The problem I am facing is when I copy the config sections needed for the Enterprise library from web.config to dtsdebughost.exe.config file and run the package, It ends in failure with below message "Error: The script files failed to load."
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
I have an OLEDB command in my package that has to execute some SQL script. But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab.
My DQL script is as shown below DECLARE @Cost AS money SET @Cost=? --Some update statements a table OLEDB Command works if write the declare and set statements after update statements. Like below. But I don€™t need it.
--Some update statements a table DECLARE @Cost AS money SET @Cost=?
I also observer that,Oledb Command gives error for the code given below. Just paste the following Script in OLEDB command, it gives error in column mapping tab DECLARE @Cost AS money SET @Cost=? Any Idea on this behaviour?
I have written a stored procedure that calls a C# class. The sp is called by a trigger on table A. The class has 2 methods. Method 1 is the main call from the stored procedure. Inside it, it sets a connection to the db. Then it calls another private method which has it's own connection and returns a dataset so that I can close that connection by the end of the method. When I execute the sp from within sql management studio, it runs just fine. But when being called from the trigger, I get the "transaction context in use by another session error" error. I read that I needed to wrap my 2nd connection in ... using (new TransactionScope(TransactionScopeOption.Suppress)){ open conn here, do work, etc.} I've tried that and I still get the same error from the trigger. Anyone know what else I can try? Thanks.
When i am connecting Report Manager report through our ASP.NET 2.0 application using Report Viewer control frequently we get message ASP.Net Session expired .
How can we avoid this error.While i directly opens Reports through the Report Manager reports are opening without any problem.
My .Net application Code. ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://SERVER/reportserver"); ReportViewer1.ServerReport.ReportPath = @"/myreport/reportname";
I am using this tag <%Transaction=Required%> to do the transaction.
And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...
It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.
I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.
I have SQL Server 2000, and our web application is in WebObjects.
I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.
This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.
Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.
Here is the relevant portion of the trigger:
create trigger t_press_run_change on dbo.press_run_line_item for insert, update, delete as
updatepress_run_line_item setis_changed = 1 from deleted d join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id where(p.is_changed = 0 or p.is_changed is null) AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0)) OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0)) OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0)) OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0)) OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,'')) OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,'')) OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,'')) OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,'')) OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,'')) OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,'')) OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,'')) OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0)) OR(isNull(p.address_company,'')<>isNull(d.address_company,'')) OR(isNull(p.address1,'')<>IsNull(d.address1,'')) OR(isNull(p.address2,'')<>IsNull(d.address2,'')) OR(isNull(p.address_city,'')<>IsNull(d.address_city,'')) OR(isNull(p.address_state,'')<>IsNull(d.address_state,'')) OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,'')) OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,'')) OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))
I am trying to run DTS package (stored on SQL Server) using Visual basic but i am getting the following error
Runtime error '-2147217843 (80040e4d)'
Login failed for 'MyUserID'
************************************* i used the following code in VB program
Sub Command1_Click() Dim dtsp As New DTS.Package dtsp.LoadFromSQLServer _ ServerName:="MyServer", _ ServerUserName:="MyUserID", _ ServerPassword:="MyPassword", _ PackageName:="DTSDemo" dtsp.Execute End Sub
*************************************
I can Run the package direct from SQL server but get error by Vb program. Any idea why it is so.
This is my procedure and the error is incorrect syntax near '01'
DECLARE @returnDay int
--Looking at current date, SELECT @returnDay = DatePart(day,GetDate()) --If is the 7th of the current moth then If @returnDay = 24
EXEC master.dbo.xp_sendmail @query = 'SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered, b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID) inner join Products c ON (b.ProductID = c.ProductID) inner join FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID', @recipients=test@hotmail.com', @message='Submitting Results for the previous month', @subject=' results for previous month', @attach_results = 'true', @separator = '/s'
SELECT @@ERROR As ErrorNumber
What am I missing here now, I am quite new to stored procedures
I have a c# stored procedure that is being called from a trigger. When I execute it from management studio, it works just fine. But, when I update a record in the table that has the trigger that calls the sp, I get the error "transaction context in use by another session error". I've tried a few of the "fixes" that I found through searching, but so far nothing seems to work. What I've tried so far is...
Removing the transaction from my code making sure my code is only using 1 connection setting XACT_ABORT ON
