Inserting Records And Displaying Them
Oct 26, 2006
Before I start driving myself nuts, I'd like to make sure my approach is correct.
I want to create a simple job posting board.
I have a text boxes for company name, email, job title, and job description, and a submit button.
I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"
When someone fills out the fields and clicks submit, it will insert the new records.
So,
1. Is this the correct approach so far?
2. What is the best way to display the job listings? A grid view?
3. At submit time, how can I include that day's date?
4. How can I get the records in the database to delete after 90 days?
Thanks.
View 1 Replies
ADVERTISEMENT
Apr 18, 2004
i have such a error in my sql server db
i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS
but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters .
i am using nchar and nvarchar and ntext
View 5 Replies
View Related
Jul 9, 2007
HI all I have this table below and i need to get records from col1 >1 and col2 >1
col1 col2 language
1 a s
1 b p
3 c e
3 c f
SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)
with the above query it gives me this
col1 col2 expr1
3 c 2
the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below
col1 col2 expr1 language
3 c 2 e
3 c 2 f
I need the result above inorder to normalize the table and bring out the language part into another table
thanks
View 2 Replies
View Related
Aug 9, 2007
Hi,
I would like to know how would i display following data.
exa:- (Note:- I wrote '---' so to display properly in to the forum
Table1
ComputerName--IpAddress
ABC1-----------10.1.2.3
ABC1-----------10.1.2.4
ABC1-----------10.1.2.5
ABC2-----------10.1.2.6
ABC2-----------10.1.2.7
ABC3-----------10.1.2.8
ABC3-----------10.1.2.9
ABC4-----------10.1.2.10
Table2
ComputerName--NetCard
ABC1-----------<card1>
ABC1-----------<card2>
ABC2-----------<card3>
ABC2-----------<card4>
ABC3-----------<card5>
ABC3-----------<card6>
Table3
ComputerName--Model
ABC1-----------DEll
ABC2-----------DEll
ABC3-----------DEll
Now i want output should be displayed like this
ComputerName--IpAddress--NetCard Model
ABC1-----------10.1.2.3---<card1>---DEll
----------------10.1.2.4---<card2>
----------------10.1.2.5
ABC2-----------10.1.2.6---<card3>---DEll
----------------10.1.2.7---<card4>
ABC3-----------10.1.2.8---<card5>---DEll
----------------10.1.2.9---<card6>
ABC4-----------10.1.2.10
Black filled should not displayed anything
Is it possible
--------------------
Thanks and regards
Abhishek Joshi
India- Bangalore
View 4 Replies
View Related
Jan 26, 2007
Hi there,
I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.
Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?
User tableUserid Username
1 Bob2 Luke3 John
Store procedure resultCompanyname Username
Acrm Corp NullMaxwellarm Ltd Null
Regards,
View 1 Replies
View Related
Jul 12, 2006
I want to display some horse racing results I have on a page but I only want to show the results for the last 7 days.
I have fields for date, time of race, horses name and result.
How can I just get records within the last 7 days to display?
I'm new to this SQL malarky so please be gentle!
View 4 Replies
View Related
Feb 5, 2004
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3
This is what I would like to have:
parameter # Calls
------------ -------
desktop 15
network 19
software 3
Please Help!
View 2 Replies
View Related
Mar 10, 2015
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
View 5 Replies
View Related
Jun 21, 2007
I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:
This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).
Here are the fields and some test data in each of the two important tables:
=============
tblDepartment =============================
departmentIDuserID
-----------------------------
1user1
2user1
1user2
2user3
3user3
3user4
===========
tblRequest ========================================
requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
43user3
53user4
62user1
What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):
requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
52user1
We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.
Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!
View 4 Replies
View Related
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
Jan 17, 2007
How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false
View 4 Replies
View Related
Feb 2, 2006
Hi,
I'm using VWD 2005 Express with SQL Server 2005 Express. I've created a page "create.aspx" which will be used to insert records into a table within the database.
I've used the DetailsView control together with a SqlDataSource control on the create.aspx page.
When I run the create.aspx page it retrieves the first record in the table of the database becuase the SqlDataSource is obviously performing a"Select * from Table" command.
I don't want this to occur. I want an empty page with no records. I know I can set a WHERE clause to a value that will never return any results but I don't beleieve this is an elegant way of doing things becuase I am 'hitting the database' for no reason other than to get the table structure poplutaed into the DetailsView control.
Questions:
1. Am I using the right controls? (in particular the SqlDataSource control)
2. Is there a better way of doing this?
Your advice is most appreciated. Thank you
View 7 Replies
View Related
May 29, 2006
pls i have this issue on inserting records into SQL SERVER Express. this is the code so far :-
imports system.data
imports system.data.sqlclient
Protected Sub btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn1.Click
Dim strConn As String = ConfigurationManager.ConnectionStrings("Connect1").ConnectionString
Dim ObjConn As New SqlConnection(strConn)
Dim strComm As String = "insert Products(ProductName,UnitPrice) values(@ProductName,@UnitPrice)"
Dim ObjComm As New SqlCommand(strComm, ObjConn)
ObjComm.Parameters.AddWithValue("@ProductName", txtprodname.Text)
ObjComm.Parameters.AddWithValue("@UnitPrice", SqlDbType.Money).Value = txtprice.Text
ObjConn.Open()
ObjComm.ExecuteNonQuery()
ObjConn.Close()
End Sub
End Class
The issue i have is that anytime i click the button to insert a new record, i get two records added to the database automatically
View 6 Replies
View Related
Jul 11, 2006
hi im a beginner trying to build an application that needs the user to enter some data into a table named Delievary. This table is related to another table named Products. The Products table has the following columns :- ProductsId - PK
ProductName
ProductPrice
Delievary table has the following columns
:- DelivaryId - PK
QuantityRecieved
ProductId - FK
i know to select i have to use inner joins but to insert is what i dont really know about. Please help me out
View 5 Replies
View Related
Jul 12, 2006
Ok i hav three tables namely customers, orders and products.
This are there structures Customers-: CustomerId-PK
FirstName
LastName
Address
Orders -: OrderId -PK
QuantityOrderd
OrderDate
CustomerId -FK
ProductId -FK
Products -: ProductId -PK
ProductName
UnitPrice
my question is that i need to relate them so that i can get info on a customer order
Thanks
View 1 Replies
View Related
Feb 23, 2007
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section.
I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck.
Regards!
Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open()
For Each ..... Problems here also
Next
cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
View 7 Replies
View Related
Jun 28, 2007
I have the Temporary table:ItemDetailID (int)FieldID (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
View 7 Replies
View Related
Nov 8, 2004
Hi,
I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.
Then insert 15 records in the child table.
Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?
Thanks!
View 7 Replies
View Related
Feb 11, 2000
Hi!
Are there any parameters that could faster the insert statement?
Maybe some cache or buffer?
Michal
View 1 Replies
View Related
Apr 24, 2007
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn
View 3 Replies
View Related
Jan 12, 2015
I'm faced with the task of creating a script that will do a series of inserts. The insert statements will look the same except for one value, which will differ for each insert.
For simplicity's sake, here is a table with some inserts:
CREATE TABLE
#MyTable (
MyPk INT IDENTITY,
MyValue VARCHAR(24)
)
[Code] ....
Rather than having to modify the string for each individual update statement, I'm hoping there is a simple way to create a variable (perhaps an array) which holds an indefinite number of values, then have a loop which automatically does an insert for each specified value in the array. Is something like this possible? Or is there a preferred industry standard for this sort of thing that I should use instead?
View 2 Replies
View Related
Feb 16, 2006
I'm getting a foreign key error as well as Indentity Insert error when I'm trying to run a stored proc that inserts values from another table..help!!
View 4 Replies
View Related
Jun 27, 2006
I'm having a problem finding step by step instructions in the few books I have on how to upload to your new table and database once those are set up.
I've got some data in an excel file, and I try to run the DTS wizard to create a package to upload the data from excel, but I can't seem to straiten out my data type problems.
How do I get my excel data types to match my table data types? It shouldn't be this hard to upload something so rudimentary. =(
View 5 Replies
View Related
Jul 10, 2007
hello everyone
I have a question about new record insertion. I have a SQL Database that I have cofigured as a publisher for a mobile database. I want to be able using the PDA to insert new Customers and synchronize the CE database with the SQL DB. But I encounter a problem with the column field rowguid that SQL inserts as a unique indentifier during the publication. It cannot accept null values and I can't insert a value using the keyboard since the value is generated by SQL.
So my impression is that you cannot use PDA to insert new records, only to update old ones. Is that true or I miss something??
If someone knows the answer please let me know.
Thank you in advanced.
View 8 Replies
View Related
Apr 1, 2004
Hi..
I really need a help.
Is there any way to insert multiple records into a table in one go?
I have a table named Fruit.
It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour.
FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case
I have an Apple.
Fruit id=2
OwnerId=2
Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query:
Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error
Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed.
Thank you in advanced.
View 3 Replies
View Related
Mar 23, 2006
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
View 4 Replies
View Related
Oct 10, 2001
Hello all,
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
EventName
EventStatusID
NumberofDays
NumberofStores
PreferredDate1
PleaseContactFlag
EventStoreSelection
EventClusterID
Note: The following fields have their Default Value set to (0)
SystemID
EventStatusID
Coop
NewProductFlag
TVSupportFlag
RadioSupportFlag
FSISupportFlag
RollbackPricing
PleaseContactFlag
Default Value set to (1):
KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT
AS
IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int
DECLARE @EventID int
DECLARE @AccountID int
DECLARE @BillingContactID int
DECLARE @EventName varchar(100)
DECLARE @EventStatusID tinyint
DECLARE @Coop bit
DECLARE @CoopSupplier varchar
DECLARE @SamplesPerDay int
DECLARE @BrochuresPerDay int
DECLARE @AverageDailyMovement int
DECLARE @SalesGoal int
DECLARE @NumberofDays int
DECLARE @NumberofHours int
DECLARE @NumberofStores int
DECLARE @WeekNumber tinyint
DECLARE @PreferredHourID tinyint
DECLARE @PreferredHourother char(20)
DECLARE @PreferredDate1 varchar(20)
DECLARE @PreferredDate2 varchar(20)
DECLARE @NewProductFlag bit
DECLARE @TVSupportFlag bit
DECLARE @RadioSupportFlag bit
DECLARE @FSISupportFlag bit
DECLARE @RollbackPricing bit
DECLARE @PleaseContactFlag bit
DECLARE @EventStoreSelection tinyint
DECLARE @EventClusterID int
DECLARE @KitInformationID tinyint
DECLARE @KitDescription varchar(1000)
DECLARE @KitOther varchar(200)
DECLARE @MCProgNum varchar(7)
DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED
SELECT @EventID = EventID FROM INSERTED
SELECT @AccountID = AccountID FROM INSERTED
SELECT @BillingContactID = BillingContactID FROM INSERTED
SELECT @EventName = EventName FROM INSERTED
SELECT @EventStatusID = EventStatusID FROM INSERTED
SELECT @Coop = Coop FROM INSERTED
SELECT @CoopSupplier = CoopSupplier FROM INSERTED
SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED
SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED
SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED
SELECT @SalesGoal = SalesGoal FROM INSERTED
SELECT @NumberofDays = NumberofDays FROM INSERTED
SELECT @NumberofHours = NumberofHours FROM INSERTED
SELECT @NumberofStores = NumberofStores FROM INSERTED
SELECT @WeekNumber = WeekNumber FROM INSERTED
SELECT @PreferredHourID = PreferredHourID FROM INSERTED
SELECT @PreferredHourother = PreferredHourother FROM INSERTED
SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED
SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED
SELECT @NewProductFlag = NewProductFlag FROM INSERTED
SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED
SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED
SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED
SELECT @RollbackPricing = RollbackPricing FROM INSERTED
SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED
SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED
SELECT @EventClusterID = EventClusterID FROM INSERTED
SELECT @KitInformationID = KitInformationID FROM INSERTED
SELECT @KitDescription = KitDescription FROM INSERTED
SELECT @KitOther = KitOther FROM INSERTED
SELECT @MCProgNum = MCProgNum FROM INSERTED
SELECT @rowguid = rowguid FROM INSERTED
INSERT INTO mcweb2.dbo.mc_events
(SystemID,
EventID,
AccountID,
BillingContactID,
EventName,
EventStatusID,
Coop,
CoopSupplier,
SamplesPerDay,
BrochuresPerDay,
AverageDailyMovement,
SalesGoal,
NumberofDays,
NumberofHours,
NumberofStores,
WeekNumber,
PreferredHourID,
PreferredHourother,
PreferredDate1,
PreferredDate2,
NewProductFlag,
TVSupportFlag,
RadioSupportFlag,
FSISupportFlag,
RollbackPricing,
PleaseContactFlag,
EventStoreSelection,
EventClusterID,
KitInformationID,
KitDescription,
KitOther,
MCProgNum,
rowguid)
VALUES
(@SystemID,
@EventID,
@AccountID,
@BillingContactID,
@EventName,
@EventStatusID,
@Coop,
@CoopSupplier,
@SamplesPerDay,
@BrochuresPerDay,
@AverageDailyMovement,
@SalesGoal,
@NumberofDays,
@NumberofHours,
@NumberofStores,
@WeekNumber,
@PreferredHourID,
@PreferredHourother,
@PreferredDate1,
@PreferredDate2,
@NewProductFlag,
@TVSupportFlag,
@RadioSupportFlag,
@FSISupportFlag,
@RollbackPricing,
@PleaseContactFlag,
@EventStoreSelection,
@EventClusterID,
@KitInformationID,
@KitDescription,
@KitOther,
@MCProgNum,
@rowguid)
SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF
END
TIA,
Bruce Wexler
Programmer/Analyst
IT Department
Mass Connections
Ph: (562) 365-0200 x1091
Fx: (562) 365-0283
http://www.massconnections.com
View 1 Replies
View Related
Nov 5, 2004
Hi,
I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?
Thanks a lot!
View 1 Replies
View Related
Nov 7, 2014
I am comparing names of people from a table without id field to a table that has those people along with their ids.
I have a select statements using different field combinations that fetches ids of these people and presents the result set like:
table1id,table1firstname,table2firstname,table1lastname,table2lastname
E.g.:
1 john john smith smith
1 john j smith smith
1 john jon smith smit
I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...
View 1 Replies
View Related
Mar 19, 2008
Folks:
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
===================================================================================
Same Query when trying to insert the records in a temp table it takes hours.
===================================================================================
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
SELECT * FROM #TResults
============================================
Thanks !
View 7 Replies
View Related
Jul 23, 2005
I have a very big table with 20 million records DistinctProjectionKeywhich i join several times to different tables in this query.select distinct distinctprojectionkeyid,dpk.MarketID,dpk.Classificationid,dpk.DistributorID,dpk.ManufacturerID,dpk.LocationID,dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR asfactor3,P1.FACTOR as factor4into Projectionfactors1FROM DistinctProjectionKey dpk INNER JOIN D_Time tON t.TimeID = dpk.TimeIDINNER JOIN (select * from (select distinctClassificationID_Major,'fam' as lab fromStagingOLTP..ClassificationFlat) cf1)cfON cf.ClassificationID_Major = dpk.ClassificationIDLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4ON t.TheDate = p4.TheDateAND p4.Name = 'FAM'AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))AND dpk.MarketID = p4.MarketIDAND p4.ManufacturerID IS NULLAND p4.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3ON t.TheDate = p3.TheDateAND p3.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))AND dpk.MarketID = p3.MarketIDAND p3.ClassificationID = dpk.ClassificationIDAND p3.ManufacturerID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2ON t.TheDate = p2.TheDateAND p2.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))AND dpk.MarketID = p2.MarketIDAND p2.ManufacturerID = dpk.ManufacturerIDAND p2.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1ON t.TheDate = p1.TheDateAND p1.Name = 'fam'AND cast(cf.Lab as varchar(20))= cast(p1.Lab as varchar(20))AND dpk.MarketID = p1.MarketIDAND p1.ManufacturerID = dpk.ManufacturerIDAND p1.ClassificationID = dpk.ClassificationIDthe other table have fewer number of records .I find that when I try to do the insert tempdb goes out of control ,itgrows above 100 GB?Would anyone know the reason why and the solution to apply to avoidthis problem?The other tables have fewer recodsClassification flat has 5000 records and projection default flat has32652 records.AjayAjay
View 2 Replies
View Related
Jul 23, 2005
I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)
View 3 Replies
View Related
Sep 6, 2006
I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.
The problem is SSIS seems to throw a strange error when I do this:
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.
When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.
Does anyone have any ideas?
View 7 Replies
View Related