Friday, March 29, 2013

[SQL Server] Need help with a .bat for a stored procedure, please.

[SQL Server] Need help with a .bat for a stored procedure, please.

Need help with a .bat for a stored procedure, please.

Posted: 29 Mar 2013 04:09 AM PDT

Hello all, I'm new to SQL and teaching myself as I go. I don't know what to do now though. I'm hoping someone can help or get me in the right direction. I have a script to do a bulk insert. I created a stored procedure for my bulk insert. I saved a copy of my stored procedure.sql in a folder. I have a program that can run an external program. It will only find .bat and .exe files. I need to save my stored procedure as a .bat so that I can have my other program trigger it. Here is my stored procedure:USE [EricaTraining]GO/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[LoadDailyAdjReport] AS Bulk Insert EricaTraining.dbo.cust_adj From 'C:\TEST\importformat.txt' With ( FieldTerminator= '|', Rowterminator= '' )Here is what I have tried in a .bat file:@echo offsqlcmd -S myservername\databasename -i C:\mypath\thestoredprocedure.sqlthat didn't work, so then i tried:@echo offsqlcmd -Smyservername\databasename -E -iE C:\mypath\thestoredprocedure.sqlThis doesn't seem to work either. The problem is... I don't really know how to use all the aliases or if the .bat file needs to have a closing command or if my stored procedure needs something else... If any of you could offer some advice, I'd really appreciate it. Thank you!

Problem with Subquery in Select Statement

Posted: 29 Mar 2013 12:45 AM PDT

This is my initial query that does work.SELECT VW_ChildrenCurrentStatus.FosterChildID, FosterChild.LegacyChildID, Person.FirstName, Person.LastName, Person.MiddleInitial, Person.DateOfBirth, DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age, VW_ChildrenCurrentStatus.StatusDate, VW_ChildrenCurrentStatus.ProgramDescription, VW_ChildrenCurrentStatus.HomeName, (SELECT MAX(ActivityStartDate) AS MaxActivityStartDate FROM ChildActivity WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate, VW_ChildrenCurrentStatus.HomeNumber, VW_ChildrenCurrentStatus.LOCSetting, VW_ChildrenCurrentStatus.FundingSource, VW_ChildrenCurrentStatus.LOCCode, VW_ChildrenCurrentStatus.LOCShortDesc, AgencyCase.CaseName, AgencyCase.WMSCaseName, AgencyCase.CaseNamePersonIDFROM FosterChild LEFT OUTER JOIN Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN CaseChild LEFT OUTER JOIN AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildIDWHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'ORDER BY Person.LastName, Person.FirstNameHere is a second query that I would like to add as another sub query to add the address columns. By itself, it works.The Address table can have more than one address for a personID, I need to return the fields associated with the most recent LastModified date.SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted]FROM [Options].[dbo].[Address] where PersonID =39785 order by LastModified desc I have replaced the "39785" with the "AgencyCase.CaseNamePersonID" This is the query I end up with.SELECT VW_ChildrenCurrentStatus.FosterChildID, FosterChild.LegacyChildID, Person.FirstName, Person.LastName, Person.MiddleInitial, Person.DateOfBirth, DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age, VW_ChildrenCurrentStatus.StatusDate, VW_ChildrenCurrentStatus.ProgramDescription, VW_ChildrenCurrentStatus.HomeName, (SELECT MAX(ActivityStartDate) AS MaxActivityStartDate FROM ChildActivity WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate, VW_ChildrenCurrentStatus.HomeNumber, VW_ChildrenCurrentStatus.LOCSetting, VW_ChildrenCurrentStatus.FundingSource, VW_ChildrenCurrentStatus.LOCCode, VW_ChildrenCurrentStatus.LOCShortDesc, AgencyCase.CaseName, AgencyCase.WMSCaseName, AgencyCase.CaseNamePersonID, (SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted] FROM [Options].[dbo].[Address] WHERE PersonID = AgencyCase.CaseNamePersonID ORDER BY LastModified desc) as AddressColumnsFROM FosterChild LEFT OUTER JOIN Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN CaseChild LEFT OUTER JOIN AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildIDWHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'ORDER BY Person.LastName, Person.FirstNameError Message : Msg 116, Level 16, State 1, Line 25Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.Any help or ideas or different ways to accomplish the same thing would be greatly appreciated.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog