Anyone happen to know why I could be able to execute an sProc, which exists in multiple databases, and I get results against 1 DB and none in the other ?
Background:
I developed a report using one database, and then switched the settings of my Data Source over to another DB I need to use & test on as well. The first DB I used will bring me back expected results, the 2nd returns nothing (empty result set).
Is there some embedded data source reference that is messing me up here ?
I have tried closing out and re-opening the SOlution to ensure it refreshed data connection. I have tried deleteing and recreating the data set using the 2nd database as well.
Additional Info:
Same sProc loaded to both DBs
Same valid SQL credentials supplied to both DBs
Same valid parameter values supplied for each sProc execution
Execution in SQL Studio returns results in both DBs using above credentials and matchin parameter value.
Scenario 1: Sproc executed on local server against local tables that took 40 seconds to run, now takes 30 minutes to run. - No blocking locks - Sometimes "NOP" in command when sp_who2 is run. - perfmon shows nothing out of the ordinary when looking at server resources. (memory, processors, etc.) there have been NO configuration changes. - Occaisional lost packets (every 10th) with ping -t - I flushed the procedure cache, and rebooted the server.Scenario 2: Sproc executed on another server accesses tables on Scenario 1 local server via server link, runs with no problems in 30 seconds.SQL Server 2000 SP3a.
I am running into an issue while executing a sproc from Excel VBA. Everything connects fine, and I am passing a parameter, however, after a few seconds, it seems like the connection receives a "completed" command and continues down it's code, but the sproc is still executing. The result is that I never receive the record set from the sproc.Here is the code snippet from VBA:
' Create Recordset objects. Dim cmd As New ADODB.Command Dim conn As ADODB.Connection Dim prm As ADODB.Parameter Dim sConnString As String Dim rs As ADODB.Recordset Dim strQry As String Dim rowCount As Long
[code]....
And here is the sproc that is being called. the first thing it performs after the "IF" block (there are multiple steps that would consecutively be called after this, but all of the data hinges on this first step working) is a TRUNCATE statement. After running a SQL profiler while executing the VBA code, I consistently see an "account log out" entry; almost as if the connection from the Excel workbook is sent a disconnect instruction. The sproc continues to run and perform the rest of the script in the "IF" block, but the returned recordset is never returned back to Excel.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET NOCOUNT ON GO
I have a parent package which executes 14 child packages in parallel, which on average take ~10 seconds each to complete when I execute the parent packege using BIDS or DTEXEC.
However, if I run the parent package using SQL Management Studio (Integration Services > Stored Packages > MSDB > Right Click > Run Package) each package takes in excess of 10 minutes to run, getting progressively slower as each package starts.
Surely the package is executing in exactly the same way as BIDS/DTEXEC, just a differenct UI?
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control..... No rows affected. (0 row(s) returned) No rows affected. (0 row(s) returned) Running [dbo].[sp_UPD_MESample_ACT_Formdata] ( @ME_Rev_Nbr = 570858 , @A1 = No , @A2 = No , @A5 = NA , @A6 = NA , @A7 = NA , @SectionA_Comments = none , @B1 = No , @B2 = Yes , @B3 = NA , @B4 = NA , @B5 = Yes , @B6 = No , @B7 = Yes , @SectionB_Comments = none , @EI_1 = N/A , @EI_2 = N/A , @UI_1 = N/A , @UI_2 = N/A , @HH_1 = N/A , @HH_2 = N/A , @SHEL_1 = 363-030 , @SHEL_2 = N/A , @SUA_1 = N/A, @SUA_2 = N/A , @Cert_Period = 10/1/06 - 12/31/06 , @CR_Rev_Completed = Y ).
No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[sp_UPD_MESample_ACT_Formdata]. The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0). And yet every time I try to update the record in the formview online... I get Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified. 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: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see. Does anyone have any tips or tricks or info that might help me?
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks, Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry ( @EntryLogID int, @ProjectID int, @NewCategoryID int, @Expended decimal(10,2) ) AS DECLARE@OldCategoryID int
********************************************* BP_GetLedgerCategory ********************************************* ALTER PROCEDURE BP_GetLedgerCategory ( @EntryLogID int ) AS
SELECT CategoryID FROM BP_EntryLog WHERE EntryLogID = @EntryLogID
RETURN
********************************************* BP_UpdateCategories ********************************************* ALTER PROCEDURE BP_UpdateCategories ( @ProjectID int, @NewCategoryID int, @Expended decimal(10,2), @OldCategoryID int ) AS
UPDATE BP_Categories SET CatExpended = CatExpended + @Expended WHERE ProjectID = @ProjectID AND CategoryID = @NewCategoryID
UPDATE BP_Categories SET CatExpended = CatExpended - @Expended WHERE ProjectID = @ProjectID AND CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6)) as DECLARE @CityID integer declare @StateID integer declare @ZipID integer set @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null set @CityID= EXEC GetCityID(@City); set @StateID= EXEC GetStateID(@State); insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID) if @@ERROR = 0 SET @ZIPID = @@Identity select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs in the above stored proc? I mean what is the syntax??
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
Here's my case, I have written a stored procedure which will perform the following: 1. Grab data from a table using cursor, 2. Process data, 3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
I recently installed the Evaluation Edition of SQL Server 2005 x64 and it appears that MS Visual Studio 2005 is installed in stead of SQL Server Business Intelligence Development Studio. When I choose new project the only template available is "Blank Solution". How do I get all the templates (i.e. Analysis Server Project, Integration Services Project, Report Model Project, Report Server Wizard project, etc.)?
Or would it be better to uninstall MS Visual Studio 2005 and attempt to reinstall BIDS?
I am new to visual studio and I am still not sure of all its components and features.
I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio?
I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional?
I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool.
How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase.
Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology.
My goodness, it seems I still have so much to learn.
I have created a database under management studio and i want it to be connected in visual studio but it failed the error msgs said that the database can't be connected coz the database with same name exits but that is not true
How do i get the database that i am using in visual studio into my SQL server management studio? i need to create some scripts to create stored procedures on a live server.
I have SSRS in Visual Studio. I created a query that works fine in SQL Server Management Studio, but when pasted into Visual Studio I get the error "An expression of non-boolean type specified in a context where a condition is expected, near '('.
Here is the query. Can anyone help on why this isn't working? Thanks.
SELECT CASE WHEN MONTH(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = MONTH(GETDATE()) AND YEAR(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = YEAR(GETDATE()) THEN dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount ELSE 0 END AS CurrentMonth, CASE WHEN SubString(dbo.MAS_CCS_GL_Account.Account,1,3) = '400' THEN 'ALEDO' ELSE ' ' END AS Location, dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate, dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber, dbo.MAS_CCS_AR1_CustomerMaster.CustomerName, dbo.MAS_CCS_ARO_InvHistoryDetail.DetailSeqNumber, dbo.MAS_CCS_ARO_InvHistoryDetail.LineType, dbo.MAS_CCS_GL_Account.Account, dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount FROM dbo.MAS_CCS_AR1_CustomerMaster, dbo.MAS_CCS_ARN_InvHistoryHeader, dbo.MAS_CCS_ARO_InvHistoryDetail, dbo.MAS_CCS_GL_Account WHERE dbo.MAS_CCS_AR1_CustomerMaster.CustomerNumber = dbo.MAS_CCS_ARN_InvHistoryHeader.CustomerNumber AND dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber = dbo.MAS_CCS_ARO_InvHistoryDetail.InvoiceNumber AND dbo.MAS_CCS_ARO_InvHistoryDetail.SOGLSalesAcct = dbo.MAS_CCS_GL_Account.AccountKey
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID. What I have tried thus far: UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID) This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated. Thanks! Amanda
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something? SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle) ArticleID - int (PK) ArticleTitle - varchar ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect. but when using a sproc, i couldn't get anything The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text) AS SELECT ArticleID, ArticleTitle, ArticleContent FROM dbo.JournalArticle WHERE (ArticleAbstract LIKE @srch) GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything. I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC: ___________________ CREATE PROCEDURE dbo.sp_addMembershipRole @INCID Int AS declare @literal NVarChar (10) SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role]) VALUES (@INCID, @literal) GO ___________________
CODE: ___________________ using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Security.Cryptography; using System.Web.Security;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click); this.Load += new System.EventHandler(this.Page_Load);
} #endregion
public void btnAdd_Click(object sender, System.EventArgs e) { int intContactID; string StrCID = Session["CID"].ToString(); intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text) { lblError.Text = "Retyping of your desired password did not match. Please try again."; return; }
create procedure GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint) as begin declare @ZipID integer declare @AddrID integer set @AddrID=1 if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2) return(@AddrID) end GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6)) AS BEGIN DECLARE @CityID integer DECLARE @StateID integer DECLARE @ZipID integer
SET @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
In Microsoft.COM website do we have a comparision between Management Studio and Management Studio express in term of functions and features of the tool ? (Not SQL Server and SQL Server Express !)
I am currently using Management Studio Express to administer remote SQLServer databases I support as a DBA. I notice that I cannot "see" DTS packages on the remote server from Express, but when I login directly to the server (Term Services) and use the local Management Studio (Full?), that I CAN see the DTS packages.
1) What are the other differences between Management Studio Express and Management Studio "Full"?
2) Is it possible to aquire and install (upgrade) on my workstation, to Management Studio "Full" so that I can take advantage of the additional capabilities the "Express" version does not have, in order to manage my remote servers?
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'. This is my sproc ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT nvarchar(max) If @ClientId IS NULL Begin Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT)
How can I determine when a sproc or table was last used? I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure?? Thanks, DL
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA IF EXISTS (select * from syscomments where id = object_id ('TestSP')) DROP PROCEDURE TestSP
GO CREATE PROCEDURE TestSP /*Declare Variables*/ @ListStr varchar(100) /*Hold Delimited String*/ AS Set NoCount On DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/ DECLARE@CP int /*Len of String */ DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>'' Begin Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */ If @CP<>0 Begin Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/ Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/ End Else Begin Set @SV=Cast(@ListStr as varchar) Set @ListStr='' End Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/ End
Select InvUnit From @ListTbl LT INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection Dim adoCmd As ADODB.Command Dim adoRS As ADODB.Recordset Dim strLegend As String Dim strData As String
Set adoConn = New ADODB.Connection adoConn.Open connString
Set adoRS = New ADODB.Recordset Set adoCmd = New ADODB.Command
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?
Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers
