.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Insert into and Store procedure problem

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
 
Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa


View Complete Post


More Related Resource Links

Help!, Problem with creating my store procedure.

  
I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

Store Procedure Debugging Problem.

  
Hi,
I am trying to debug the store procedure in SQL server 2005 . I have windows 7 64-Bit on my client computer and server is running SQL server 2005 standard edition. I am trying to debug store procedures in visual studio 2005 .  I followed the below steps. 

I have followed all the articales however still i am unable to debug the store proc in my application . I did the following.

1: My local machnice user has administrator rights on my local machine and in SQL server it has sys admin rights. 

2: On my client machine i am running windwos 7 64-bit version , database server is on windows Server 2003 64-bit edition. 

3: I configured the remote debugger on windows server then i start the remote debugger 64 bit .

Now when i try to debug the store procedure i got the following error message.



Unable to start T-SQL Debugging. Could not connect to computer eicsrv01 . The Microsoft visual studio Remote Debugging

Monitor on the remote computer cannot connect to the local computer . A firewall may be preventing communication via 

Problem insert POCO to Velocity Cache

  

Hello,

I want to put POCO object to Velocity cache. How can I serializable ICollection to Velocity cache, or exclude ICollection from serialization?

I got this exception

Type 'System.Collections.Generic.ICollection`1[Model.Collection]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute.  If the type is a collection, consider marking it with the CollectionDataContractAttribute.  See the Microsoft .NET Framework documentation for other supported types.


Thanks

Tomas



Nested listview fk-problem with insert

  

Hi! 

My problem is this, i have a nested listview that works fine but if i need to do an insert and the nested listview dont have retrived any values (that is no posts created) i cant get hold of the fk that i need. If i put a hiddenfield in the parents listview with the fk how can i find it in codebehind when i do an insert? 

Best 

E


SQL Insert with Index Scan Problem....

  
Hi,   I'm having a very strange problem when inserting rows into a table. Let me try to explain a little better: Table1 (for example Book) with a lot of fields and one foreign key to Table2 (idVolume) and a primary key (identity - idBook). This table has a clustered index only on the primary Key. (this table has something like 10 Millions rows) Table2 (for example Volume) with a lot of fields and a primary key (idVolume) identity. (this table has hundreds of rows)   Now, the problem starts when I make a insertion on Table2. When I made an insertion, it was taking minutes to finish, so I stopped it. After looking into the execution plan of the query, there was something very very strange, there seems to be a clustered index scan on Table1... the problem is that since Table1 has so many rows, this index scan takes forever... Can anyone explain me why does the insertion on Table2 makes an clustered index scan on Table1? It really doesn't make any sense to me.   Thanks in advance. João Teixeira

Insert the results of a stored procedure into specific columns

  
Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

Basic data type problem with database insert

  
Hi,I'm trying to write some values into a database, but I think there's an issue with the data types.  Here's what I've got: public static string Hello(string pageId, string inOut) { string itemId = ""; string period = ""; var cookie = HttpContext.Current.Request.Cookies["visitorGUID"].Value; SqlConnection conn; SqlCommand comm; conn = new SqlConnection(GlobalSettings.DbDSN); comm = new SqlCommand("INSERT INTO myTable (memberGUID) VALUES (@memberGUID);", conn); comm.Parameters.Add("@memberGUID", System.Data.SqlDbType.Text); comm.Parameters["@memberGUID"].Value = cookie; try { conn.Open(); comm.ExecuteNonQuery(); return "Item added: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } catch { return "Item caught: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } finally { conn.Close(); } } At the moment I'm just trying to insert the value of the cookie into the database as a test.  It works fine when I

problem for using webservice (sql parameter stored Procedure)

  
i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

SSIS - Call a Store procedure

  
Can someone show me an example of how to call a store procedure that checks for empty fields in a SQL database table, by returning a value when fields are empty. then send an email to the adminstratore.

Bulk Insert Problem

  
I am trying to perform a bulk insert on pipe delimited file (approx 25 columns,5000+ rows of data).  Normally this is no problem however this file has a header and trailer record with no delimiters (1 column).  I could use a utility like grep to remove the header/trailer record but I would like to capture the entire file inside sql server. I can bulk insert the entire file into a single column (wide) that would include the delimiters.  My problem from there is to process the single column data into the appropriate rows /columns.  I am looking for a set solution as opposed to a cursor type solution. HTML?  Seems slow.  Cross Apply?   Changing the file format is not a viable solution. I’m likely to have more than a few of these oddball formats with a variable number of columns.  Any ideas?   I thank you for your time on this puzzle.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

  
Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Store Procedure

  
How can i insert and delete a data with use of one store procedure ........... SP File:- ALTER PROCEDURE dbo.One @id int, @Name Varchar(50), @select char(1) AS begin  if @select ='i' insert into j (id,Name) values (@id ,@Name) end begin if @select ='d' delete from j  where id= @id end RETURN How can i called tis two query

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

How I can to check if Store Procedure altered(been called )?

  
I have ado.net connection to SP               conn = new SqlConnection("Data Source=DY;Initial Catalog=DMSA;Integrated Security=True");                 conn.Open();                   // 1.  create a command object identifying                 //     the stored procedure                 SqlCommand cmd = new SqlCommand("dbo.UpdateCorporationFile", conn);                   // 2. set the command object so it knows                 //    to execute a stored procedure                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@count_create_C_W", DBNull.Value));        &nb

insert result of stored procedure in new table

  
hi how can i insert the result of stored procedure in the new table. Best Regards. Morteza
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend