Search Results

Search found 3935 results on 158 pages for 'extended procedures'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 28/158 | < Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >

  • Possible to get the line number of the currently executing sproc in SQL Server?

    - by Ian Kemp
    A few years back I worked in a Sybase/Delphi environment, using the BDE to connect to the DB server. We had a little Delphi app that, given the name of a currently executing stored procedure, could tell you what line of that sproc was currently being executed. This was exceptionally useful for debugging sprocs that seemed to be hanging. I'd like to use this functionality in SQL Server, but I can't remember whether it was a Sybase or a BDE feature. Is this functionality available in SQL Server and if so, what command(s) do I need to use to view this information?

    Read the article

  • How do I get output into Java from a SELECT stored procedure in Oracle?

    - by Ventrue
    I'm using Java to connect to an Oracle 10 Database. I want to create a stored procedure (don't ask why) that takes no arguments and returns a lot of rows. Specifically, in Java I want to be able to get this data with something like: ResultSet rs = stmt.executeQuery("call getChildless"); where getChildless is the query: SELECT objectid FROM Object WHERE objectid NOT IN (SELECT parent FROM subparts); However, I just cannot for the life of me figure out how to get my output from the stored procedure. I've googled it and I get all this sample code that Oracle won't compile, presumably it's for a previous version. Refcursors seem to come up a lot, but I'm not sure if that's what I actually want, to use it with a ResultSet.

    Read the article

  • How do I create a stored procedure that calls sp_refreshview for each view in the database?

    - by Allrameest
    Today I run this select 'exec sp_refreshview N''['+table_schema+'].['+table_name+']''' from information_schema.tables where table_type = 'view' This generates a lot of: exec sp_refreshview N'[SCHEMA].[TABLE]'. I then copy the result to the query editor window and run all those execs. How do I do this all at once? I would like to have a stored procedure called something like dev.RefreshAllViews which I can execute to do this...

    Read the article

  • SQL optimization: deletes taking a long time

    - by Will
    I have an Oracle SQL query as part of a stored proc: DELETE FROM item i WHERE NOT EXISTS (SELECT 1 FROM item_queue q WHERE q.n=i.n) AND NOT EXISTS (SELECT 1 FROM tool_queue t WHERE t.n=i.n); A bit about the tables: item contains about 10k rows with an index on the n column item_queue contains about 1mil rows also with index on n column tool_queue contains about 5mil rows indexed as well I am wondering if the query/subqueries can be optimized somehow to make them run faster, I thought that deletes were generally fairly fast

    Read the article

  • Export view data programmatically in Access/SQL Server

    - by andy
    We have an Access application front-end connected to a SQL Server 2000 database. We would like to be able to programmatically export the results of some views to whatever format we can (ideally Excel, but CSV / tab delimited is fine). Up until now we've just hit F11, opened up the view, and hit File-Save As, but we're starting to get results with more than 16,000 results, which can't be exported. I'd like some sort of server side stored procedure we can trigger that will do this. I'm aware of the sp_makewebtask procedure that does this, however it requires administrative rights on the server, and for obvious reasons we can't give that to everyone. Any ideas?

    Read the article

  • SqlServer2008 + expensive union all

    - by Tim Mahy
    Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union all of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and reference that new one from the 5 tables). The query is rather expensive / slow what are our other options? It's allowed to think outside the box. Unfortunately I cannot give more information like the table/view/SP definition because of customer confidentiality... greetings, Tim

    Read the article

  • INSERT stored procedure does not work?

    - by vikitor
    Hello, I'm trying to make an insertion from one database called suspension to the table called Notification in the ANimals database. My stored procedure is this: ALTER PROCEDURE [dbo].[spCreateNotification] -- Add the parameters for the stored procedure here @notRecID int, @notName nvarchar(50), @notRecStatus nvarchar(1), @notAdded smalldatetime, @notByWho int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Animals.dbo.Notification values (@notRecID, @notName, @notRecStatus, null, @notAdded, @notByWho); END The null inserting is to replenish one column that otherwise will not be filled, I've tried different ways, like using also the names for the columns after the name of the table and then only indicate in values the fields I've got. I know it is not a problem of the stored procedure because I executed it from the sql server management studio and it works introducing the parameters. Then I guess the problem must be in the repository when I call the stored procedure: public void createNotification(Notification not) { try { DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus, (DateTime)not.NotAdded, (int)not.NotByWho); } catch { return; } } It does not record the value in the database. I've been debugging and getting mad about this, because it works when I execute it manually, but not when I automatize the proccess in my application. Does anyone see anything wrong with my code? Thank you

    Read the article

  • How to add second JOIN clause in Linq To Sql?

    - by Refracted Paladin
    I am having a lot of trouble coming up with the Linq equivalent of this legacy stored procedure. The biggest hurdle is it doesn't seem to want to let me add a second 'clause' on the join with tblAddress. I am getting a Cannot resolve method... error. Can anyone point out what I am doing wrong? Below is, first, the SPROC I need to convert and, second, my LINQ attempt so far; which is FULL OF FAIL! Thanks SELECT dbo.tblPersonInsuranceCoverage.PersonInsuranceCoverageID, dbo.tblPersonInsuranceCoverage.EffectiveDate, dbo.tblPersonInsuranceCoverage.ExpirationDate, dbo.tblPersonInsuranceCoverage.Priority, dbo.tblAdminInsuranceCompanyType.TypeName AS CoverageCategory, dbo.tblBusiness.BusinessName, dbo.tblAdminInsuranceType.TypeName AS TypeName, CASE WHEN dbo.tblAddress.AddressLine1 IS NULL THEN '' ELSE dbo.tblAddress.AddressLine1 END + ' ' + CASE WHEN dbo.tblAddress.CityName IS NULL THEN '' ELSE '<BR>' + dbo.tblAddress.CityName END + ' ' + CASE WHEN dbo.tblAddress.StateID IS NULL THEN '' WHEN dbo.tblAddress.StateID = 'ns' THEN '' ELSE dbo.tblAddress.StateID END AS Address FROM dbo.tblPersonInsuranceCoverage LEFT OUTER JOIN dbo.tblInsuranceCompany ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyID = dbo.tblInsuranceCompany.InsuranceCompanyID LEFT OUTER JOIN dbo.tblBusiness ON dbo.tblBusiness.BusinessID = dbo.tblInsuranceCompany.BusinessID LEFT OUTER JOIN dbo.tblAddress ON dbo.tblAddress.BusinessID = dbo.tblBusiness.BusinessID and tblAddress.AddressTypeID = 'b' LEFT OUTER JOIN dbo.tblAdminInsuranceCompanyType ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyTypeID = dbo.tblAdminInsuranceCompanyType.InsuranceCompanyTypeID LEFT OUTER JOIN dbo.tblAdminInsuranceType ON dbo.tblPersonInsuranceCoverage.InsuranceTypeID = dbo.tblAdminInsuranceType.InsuranceTypeID WHERE tblPersonInsuranceCoverage.PersonID = @PersonID var coverage = from insuranceCoverage in context.tblPersonInsuranceCoverages where insuranceCoverage.PersonID == personID join insuranceCompany in context.tblInsuranceCompanies on insuranceCoverage.InsuranceCompanyID equals insuranceCompany.InsuranceCompanyID join address in context.tblAddresses on insuranceCompany.tblBusiness.BusinessID equals address.BusinessID where address.AddressTypeID = 'b' select new { insuranceCoverage.PersonInsuranceCoverageID, insuranceCoverage.EffectiveDate, insuranceCoverage.ExpirationDate, insuranceCoverage.Priority, CoverageCategory = insuranceCompany.tblAdminInsuranceCompanyType.TypeName, insuranceCompany.tblBusiness.BusinessName, TypeName = insuranceCoverage.InsuranceTypeID, Address = };

    Read the article

  • Export products and variants from SQL Server

    - by mickyjtwin
    I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a comma-delimited string, e.g: Product Code, Name, SKUs 111 P1 77, 22, 11 Is there an easier way to do this, so that each SKU is a row which the associated product code as well, i.e: Product Code, Name, SKUs 111 P1 77 111 P1 22 111 P1 11

    Read the article

  • SQL Server Cursor data as result of stored procedure

    - by Dmitry Borovsky
    Hello, I have a stored procedure DECLARE cursor FOR SELECT [FooData] From [FooTable]; OPEN cursor ; FETCH NEXT FROM cursor INTO @CurrFooData; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CurrFooData AS FooData; INSERT INTO Bar (BarData) VALUES(@CurrFooData); FETCH NEXT FROM cursor INTO @CurrFooData; END; CLOSE cursor DEALLOCATE cursor But in result I have a lot of tables, not one. How can I return one table with 'FooData' column and all '@CurrFooData' rows?

    Read the article

  • MS SQL Server 2008 Stored Procedure Result as Column Default Value

    - by user337501
    First of all, thank you guys. You always know how to direct me when I cant even find the words to explain what the heck im trying to do. The default values of the columns on a couple of my tables need to equal the result of some complicated calculations on other columns in other tables. My first thought is to simply have the column default value equal the result of a stored procedure. I would also have one or more of the parameters pulled from the columns in the calling table. I don't know the syntax of how to do it though, and any time the word "stored" and "procedure" land next to each other in google I'm flooded with info on Parameter default values and nothing relating to what I actually want. Half of that was more of a vent than a question...any ideas though? And plz plz dont say "Well, you could use an On-Insert Trigger to..."

    Read the article

  • error invoking store procedure with input parameter from ADO.Net

    - by George2
    Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong? System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied. class Program { private static SqlCommand _command; private static SqlConnection connection; private static readonly string _storedProcedureName = "Pr_Foo"; private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB"; public static void Prepare() { connection = new SqlConnection(connectionString); connection.Open(); _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public static void Dispose() { connection.Close(); } public static void Run() { try { SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101); Param1.Direction = ParameterDirection.Input; SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100); portal_SiteInfoID.Direction = ParameterDirection.Input; SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200); portal_RoleInfoID.Direction = ParameterDirection.Input; _command.ExecuteScalar(); } catch (Exception e) { Console.WriteLine(e); } } static void Main(string[] args) { try { Prepare(); Thread t1 = new Thread(Program.Run); t1.Start(); t1.Join(); Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message + "\t" + ex.StackTrace); } } } thanks in avdance, George

    Read the article

  • Insert Stored Procedure, Using Asp.Net WebForm to Insert new [Customer]

    - by user2953815
    Can someone please help me create a stored procedure to insert a new customer from a web form. I am having difficulty making the state a drop down list for customer to pick a state from the list and having that inserted into the database. INSERT INTO Customer ( Cust_First, Cust_Middle, Cust_Last, Cust_Phone, Cust_Alt_Phone, Cust_Email, Add_Line1, Add_Line2, Add_Bill_Line1, Add_Bill_Line2, City, State_Prov_Name, Postal_Zip_Code, Country_ID ) VALUES ( @Cust_First, @Cust_Middle, @Cust_Last, @Cust_Phone, @Cust_Alt_Phone, @Cust_Email, @Add_Line1, @Add_Line2, @Add_Bill_Line1, @Add_Bill_Line2, @City, @State_Prov_Name, @Postal_Zip_Code, @Country_ID )"> <InsertParameters> <asp:Parameter Name="Cust_First" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Middle" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Last" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Alt_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Email" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="City" Type="String"></asp:Parameter> <asp:Parameter Name="Postal_Zip_Code" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_ID" Type="Int32"></asp:Parameter> <asp:Parameter Name="State_Prov_Name" Type="String"></asp:Parameter> <asp:Parameter Name="Country_Name" Type="String"></asp:Parameter> </InsertParameters>

    Read the article

  • SQL Query to get Count within a Stored Proc

    - by sia
    So i need to figure out how i can get a record count value and use that count as a new value to insert into a table. Ex: In My Stored Procedure @Count int What im looking for @Count to equal "select top (1) _fieldName from _someTable order by _fieldName Desc" Finally insert into _newTable (_fieldName) values (@Count) End I dont have to use a variable, just trying to demonstrate what im really trying to do. My SQL knowledge is pretty limited so no laughing, or smirking! ;)

    Read the article

  • query in sql server for retrieving rows

    - by Arash khangaldi
    I have a table that contains the following 4 columns: id name lastname phone I want to write a stored procedure that gets an id as parameter, and then gets the name of that id and then use that name to get all the rows that their name is equal to the names that i found in last step! here it is my query, i know it's wrong but i'm new to sql commands: ALTER PROCEDURE dbo.GetAllNames @id int AS select name as Name from Users where id = @id -- i don't how to retrieve the names that are equal to Name select * from Users where name = Name can you correct my query and help me? Thanks.

    Read the article

  • MySQL db Audit Trail Trigger

    - by Natkeeran
    I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the procedure): The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set. Please let me know what could be the issue, or an alternate way to implement this. DROP PROCEDURE IF EXISTS addLogTrigger; DELIMITER $ CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255)) BEGIN SELECT CONCAT( 'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', GROUP_CONCAT( CONCAT( 'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO AuditLog (', 'TableName, ', 'RowPK, ', 'FieldName, ', 'OldValue, ', 'NewValue' ') VALUES ( ''', table_name, ''', NEW.', pkField, ', ''', column_name, ''', OLD.', column_name, ', NEW.', column_name, '); END IF;' ) SEPARATOR ' ' ), ' END;$' ) FROM information_schema.columns WHERE table_schema = database() AND table_name = tableName; END$ DELIMITER ;

    Read the article

  • how to use a parameterized function for the Default Binding of a Sql Server column

    - by Walt Gaber
    I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) at the time the new file is being recorded. I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding. I would like to avoid using a trigger. I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure. How can I call this function with values from the row that is being inserted? USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FileCatalog]( [id] [uniqueidentifier] NOT NULL, [catalog_timestamp] [datetime] NOT NULL, [primary_duplicate] nchar NOT NULL, [name] nvarchar NULL, [length] [bigint] NULL, [timestamp] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_id] DEFAULT (newid()) FOR [id] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_catalog_timestamp] DEFAULT (getdate()) FOR [catalog_timestamp] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_primary_duplicate] DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate] GO USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetPrimaryDuplicate] ( @name nvarchar(255), @length bigint, @timestamp datetime ) RETURNS nchar(1) AS BEGIN DECLARE @c int SELECT @c = COUNT(*) FROM FileCatalog WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P' IF @c > 0 RETURN 'D' -- Duplicate RETURN 'P' -- Primary END GO

    Read the article

  • SQL Server store procedure line number issue

    - by George2
    Hello everyone, I am using SQL Server 2008 Enterprise. I met with issue which says line 9 of store procedure foo is meeting with dead lock issue. My question is how to find exactly the 9th line of the store procedure? My confusion is because of coding format issue, how to locate 9th line correctly. thanks in advance, George

    Read the article

  • SQL Server stored procedure return code oddity

    - by gbn
    Hello The client that calls this code is restricted and can only deal with return codes from stored procs. So, we modified our usual contract to RETURN -1 on error and default to RETURN 0 if no error If the code hits the inner catch block, then the RETURN code default to -4. Where does this come from, does anyone know...? IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo GO CREATE TABLE dbo.foo ( KeyCol char(12) NOT NULL, ValueCol xml NOT NULL, Comment varchar(1000) NULL, CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (KeyCol) ) GO IF OBJECT_ID('dbo.bar') IS NOT NULL DROP PROCEDURE dbo.bar GO CREATE PROCEDURE dbo.bar @Key char(12), @Value xml, @Comment varchar(1000) AS SET NOCOUNT ON DECLARE @StartTranCount tinyint; BEGIN TRY SELECT @StartTranCount = @@TRANCOUNT; IF @StartTranCount = 0 BEGIN TRAN; BEGIN TRY --SELECT @StartTranCount = 'fish' INSERT dbo.foo (KeyCol, ValueCol, Comment) VALUES (@Key, @Value, @Comment); END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 --PK violation UPDATE dbo.foo SET ValueCol = @Value, Comment = @Comment WHERE KeyCol = @Key; ELSE RAISERROR ('Tits up', 16, 1); END CATCH IF @StartTranCount = 0 COMMIT TRAN; END TRY BEGIN CATCH IF @StartTranCount = 0 AND XACT_STATE() <> 0 ROLLBACK TRAN; RETURN -1 END CATCH --Without this, we'll send -4 if we hit the UPDATE CATCH block above --RETURN 0 GO --Run with RETURN 0 and fish line commented out DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' SELECT @rtn; SELECT * FROM dbo.foo DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar2 />', 'testing2' --updated OK but we get @rtn = -4 SELECT @rtn; SELECT * FROM dbo.foo --uncomment fish line DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' --Hit outer CATCH, @rtn = -1 as expected SELECT @rtn; SELECT * FROM dbo.foo

    Read the article

  • Stored procedure call in method with OperationBehavior attribute: problems with transactions

    - by Gerrie Schenck
    I'm using ADO.Net's ExecuteNonQuery to call a stored procedure, works like a charm stand-alone but when implementing it where it should be called I'm running into problems concerning transactions. For example System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. and also a timeout right after that. I've just found out the method which calls the stored procedure is marked with the following WCF attribute: [OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)] How will this influence the call my stored procedure? How can I tell .Net to execute the stored procedure outside this transaction? The stored procedure contains insert statements and also a transaction, but removing them doesn't change the behavior...

    Read the article

  • SQl Server: serializable level not working

    - by Zé Carlos
    I have the following SP: CREATE PROCEDURE [dbo].[sp_LockReader] AS BEGIN SET NOCOUNT ON; begin try set transaction isolation level serializable begin tran select * from teste commit tran end try begin catch rollback tran set transaction isolation level READ COMMITTED end catch set transaction isolation level READ COMMITTED END The table "test" has many values, so "select * from teste" takes several seconds. I run the sp_LockReader at same time in two diferent query windows and the second one starts showing test table contents without the first one terminates. Shouldn't serializeble level forces the second query to wait? How do i get the described behaviour? Thanks

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >