Hello--First Post & Already Need Help!
Nov 7, 2007
Greetings friends:
I am a student at DePaul University in Chicago, IL. We have a big db project tomorrow and I'm proud with the work I've done so far, I've designed my own db and tested it using queries and such. Pretty good for being introduced to SQL just a couple short months ago. Anyway, I've sort of run into a wall here. I need to have an attribute of a table be computed from others.
I'm working with 2005 Server Management Studio and I have found the computed column specification under the column properties tab. I know that I have to enter a formula, but I'm just not sure on what to do. I have a CHG_HOUR attribute in a table called "EXPERTISE" which signifies how much a consultant charges per hour, based on what he exactly does. I also have a JOB_HOURS attribute in a JOB table (that links my CONTRACT table to CONSULTANT). I want to basically give a formula that multiplies the CHG_HOUR from the EXPERTISE table by the JOB_HOURS in the JOB table. Any suggestions on how I might do this?
Thank you in advance for your response, and hopefully with more practice and courses, I can be the one helping people like me on these boards in a few years.
View 2 Replies
ADVERTISEMENT
Oct 22, 2007
---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
View 1 Replies
View Related
May 25, 2006
Alright so here is what I am trying to do.
I have a form that someone fills out it has a text box as title, and a drop down box that is a category, and then a text area that is for their explanation.
On the back end I am using a stored procedure called sp_store_bkm. When I execute this it works just fine and puts the data that I put in it into the to table below is the Stored procedure code:
ALTER PROCEDURE sp_store_bkm @oID nvarchar OUTPUT, @oTitle nvarchar(50), @oCategory nvarchar(50), @obkmtext nvarchar(MAX)
AS BEGIN INSERT INTO tbl_bkms(Title, Category, bkmtext) VALUES(@oTitle, @oCategory, @obkmtext)Set @oID= SCOPE_IDENTITY() END
Now on my front end it comes up with an error in the lower left (erros on page). When I click on the error for details it seems like it is coming fromt he connection string. I cant find anything wrong with the connection string. Below is my code for the aspx page.
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<script>
function Submit2_onclick() {
Dim Connection As SqlConnection = "server=localhost;Database=BKM.mdf;integrated security=SSPI;" connection.Open() Try Dim command As SqlCommand = New SqlCommand("sp_store_bkm", connection) command.CommandType = CommandType.StoredProcedure Dim oID As New SqlParameter("@oID", SqlDbType.Int) oID.Direction = ParameterDirection.Output command.Parameters.Add(oID) command.Parameters.Add("@oTitle", title.text) command.Parameters.Add("@oCategory", category.text) command.Parameters.Add("@obkmtext", bkmtext.text) command.ExecuteNonQuery() Dim sOrderID As String = oID.Value }</script>
<form method="post"> <table cellpadding="10" style="width: 100%"> <tr> <td style="width: 100px"> <span style="font-size: 10pt; font-family: Verdana"> Login ID: <br /> </span> <asp:LoginName ID="LoginName1" runat="server" Font-Names="Verdana" Font-Size="10pt" ForeColor="Red" /> <span style="font-size: 10pt; font-family: Verdana"> <br /> <br /> Title:<br /> </span> <input id="title" style="width: 374px" type="text" /><br /> <br /> <span style="font-size: 10pt; font-family: Verdana"> Category:<br /> </span> <select id="Category" name="D1" size="1" language="javascript" onclick="return Select1_onclick()"> <option selected="selected">Office Applications</option> <option>VPN</option> <option>WLAN</option> </select> <br /> <span style="font-size: 10pt; font-family: Verdana"> <br /> Your BKM<br /> </span> <textarea id="bkmtext" style="width: 378px; height: 196px"></textarea><br /> <br /> <input id="Reset1" type="reset" value="reset" /> <input id="Submit2" type="submit" value="submit" language="javascript" onclick="return Submit2_onclick()" /></td> </tr> </table> </form></asp:Content>
Please help.
View 3 Replies
View Related
Jul 12, 2001
Can you help me? I'm pointing on the thread just below this post (along with the other messages on the message board).
View 1 Replies
View Related
Jul 20, 2005
Just testing out this posting thing... Thanks!!Join Bytes!----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View 2 Replies
View Related
Mar 8, 2007
I have a question about the post SP2 GDR that just came out in the last day or two. My laptop only has the client tools installed, not the server components (except for the SSIS server component). Is there any need for me to install this GDR? Does it impact the Management Studio code which views maintenance plans on remote SQL Servers or anything?
View 3 Replies
View Related
Apr 27, 2007
I am developing an application using Visual Web Designer (language VB) with a SQL Server 2000 back end.
Within my logic I have a point where I want to say "Run SQL procedure GEDFinish". GEDFinish can take several minutes, and I don't need to wait for it's results - it would be fine if it ran overnight. I can easily check whether GEDFinish has run from the database, in situation where my application logic needs to know if it has already run.
Currently I only know how to run a procedure under the direct control of my web program, but with GEDFinish this fails due to timeouts. (This will be worse in the production site). What I really want to do is to have my web program submit "Exec GEDFinish" to the SQL agent, and then forget about it. It would be even better if I could build a script for the SQL agent, for example "EXEC GEDFinish Parameter=7"
Help! How do I do this?
Thanks, Robert Barnes
View 6 Replies
View Related
Mar 1, 2002
Sorry it is my second posting , I am trying to find answer to my problem
Hello everybody.
I have problem with mail
I have 3 servers
A) SQL2000 sp2 Standard edition 1024 MB memory
B) SQL2000 sp2 Standard edition 3072 MB memory
C) SQL2000 sp2 Enterprise edition 2048 MB memory
All servers use same mail profile and they all run under same account MYDomainsvcSql2000 for server and agent and connected to same mail server
1. server C run fine
2. servers A and B could stop mail service during the day and the only way to restart it is to open EM and retype password for Sql server account (it will force Server and agent restart)
Any idea ?
View 1 Replies
View Related
Apr 7, 2004
it's been too long since I answer something on indexes and it shouldn't, indexes are a important part of SQL
View 3 Replies
View Related
Jun 21, 2004
Hi,
Is it possible to write a stored procedure which can post an XML file to a server using HTTP?
Thanks!
View 6 Replies
View Related
Aug 21, 2007
Can anyone help me to get the hotfix SQL Server 2000 build 8.00.2215 ?
As i do not find any download link.
View 3 Replies
View Related
Feb 10, 2006
How to find out all my post in this forum?
View 2 Replies
View Related
Feb 5, 2007
I need to delete a post by particular date and time, what is the syntax for that. anybody help
Articles on various categories
View 7 Replies
View Related
May 22, 2007
Hi, I am new to this forum and was wondering where i can see all my previous post? (i.e all the post i was ever involved in)
dallr
View 15 Replies
View Related
Aug 1, 2007
This is a test post
View 7 Replies
View Related
Sep 28, 2007
Ok so someone answered my last quesiton about finding what tables are in a Stored procedure. Is there an opposite of that. If I wanted to type in a table and it shows all the stored procedures that use that table.
View 3 Replies
View Related
Jul 20, 2005
I am at a client that uses an xml form to email from java rather thansetup a true email server. Does anyone have any suggestions as to howms sql can write to the xml or maybe even call java or xml directly.This will be done as part of the error detecting process of various sqljobs in EM.Thanks,Mike--Posted via http://dbforums.com
View 1 Replies
View Related
Mar 20, 2007
If u get any solution then plz share knowledge with me because I am new in SQL
View 3 Replies
View Related
Sep 1, 2006
I was hoping there would be a newbie section in here... But I'll post anyway (And sorry in advance if its a really dumb question)I am using VWDE and have a SQL DB which has been imported from one of my existing forums, when it shows in the database explorer its show like belowtblArcade (jdName)tblArcadePB (jdName)etc..etc...What I want to know is why this DB has (jdName) after the Table name and my other DB's shown don't?? They are just shown as tblArcade
tblArcadePB etc..etc.. My main questions are "How do I remove the brackets from the forum DB" as when I try to preview data in VWDE I get a message Invalid object name 'tblArcade'.I know its to do with this extra bit as it only happens to the tables that have this on the end... Hope that makes sense and someone can help me, if you could reply as if I'm really dumb it would be appreciated
View 4 Replies
View Related
Sep 20, 2006
Hello,I'm trying to update a single field of a record and i want to do it using a standard multi line text box but I'm not sure how to write the c# command to process the sql update. I would also like the entry to be added into the database with line breaks. Thanks for your help
View 5 Replies
View Related
Apr 15, 2007
I'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.
Dim NTAdatasource As New SqlDataSource()
NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text
NTAdatasource.InsertCommand = "INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@YourName, @EmailAddress, @DateTimeStamp)"
NTAdatasource.InsertParameters.Add("YourName", txtYourName.Text)
NTAdatasource.InsertParameters.Add("EmailAddress", txtEmailAddress.Text)
NTAdatasource.InsertParameters.Add("DateTimeStamp", DateTime.Now)
Dim RowsAdded As Integer = 0
Try
RowsAdded = NTAdatabase.Insert()
Catch ex As Exception
Server.Transfer("problem.aspx")
End Try
If RowsAdded <> 1 Then
Server.Transfer("problem.aspx")
Else
Server.Transfer("success.aspx")
End If
End Sub
The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?
Thanks
View 1 Replies
View Related
Nov 28, 2001
HELP! I'm trying to pass parameters to a DTS Package(SELECT * FROM TABLE WHERE STATE = ?) What is the format from xp_cmdshell("exec master..xp_cmdshell 'dtsrun /Ssql /?????")? Any ideas? Thanks!
View 2 Replies
View Related
Dec 6, 2001
Hi all,
I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK).
thanks in advance.
Minesh.
View 3 Replies
View Related
Apr 4, 2003
I can run the xp_cmdshell command with any other command until I put something like this is.
CREATE TRIGGER RunUpdateProgram ON [dbo].[UpdateQue]
FOR INSERT
AS
declare @MybatchID varchar(32),
@sendcommand varchar(32)
select @MybatchID = batchid from updateque
select @sendcommand = '"d:xanaduostarterprog.exe ' + @MybatchID + '"'
exec master..xp_cmdshell @sendcommand, no_output
The starterprog is setup to receive commandline input and then do its thing but everytime I try to add in a commandline to the xp_cmdshell command i get nothing.
The security is right and the program is right because i can run it until i try to add that last part "@MybatchID". If I take out the quotes then it runs the program but without the commandline input. I've tested the program manually and it correctly accepts commandlines that way.
Anyone got any ideas on what I've done wrong here.
View 1 Replies
View Related
Jan 14, 1999
Has anyone experienced any unexpected difficulties with this service pack?
View 2 Replies
View Related
Oct 13, 2004
I'm making a "simple" ASP forum to integrate into a webpage solution. I just recently ran into a problem with sorting threads.
I have a page that displays all threads in a given forum (Threads.ThreadForumID=iForumID) this page will have all pinned threads on top of the list the threads should then be sorted according to when a message last was posted inside the thread (Posts.PostDate)
Sort order:
Threads.ThreadPinned
Posts.PostDate or Posts.LastPostDate if it has been set.
The tables are as follows:
Threads table:
ThreadIDNumber
ThreadNameText
ThreadViewCountNumber
ThreadOwnerIDNumber(Ref to thread owner id, user)
ThreadLockedBoolean
ThreadPinnedBoolean
ThreadForumIDNumber(Ref to ForumID, forum)
Posts table:
PostIDNumber
PostTextText
PostOwnerIDNumber(Ref to post owner, user)
PostLastEditByNumber(Ref to last user that edited, user)
PostThreadIDNumber(Ref to thread)
PostIPText
PostNicknameText
PostDateDate
PostLastEditDateDate
Current "sort":
This is the current SQL statement it is not in any way correct. It's just temporary. I have tried different joins but I can't seem to get it right!
SELECT ThreadID
FROM Threads
WHERE ThreadForumID=" & iForumID & "
ORDER BY ThreadPinned DESC, ThreadID DESC
Could someone please give me a hand here?
View 13 Replies
View Related
Feb 4, 2005
I just released a beta and I am too busy with the testers right now.
New company. Brought into automate the back office and to advise on the design on their software. Lots of sloppiness here. Denormalized tables, inline SQL all of the usual crap I get paid to cleanup.
I have'nt gotten to this part of there process yet but they do some over night processing that can take up to 12 hours. (I can't wait to look at this code).
Data entry people get here at 6 am and their applications can't seem to connect to the SQL Server but the processing has stopped. They just hang and hang. The network guy comes in about 8 am and restarts the SQL Service Fonzi style and everything is fine. The DBA thinks it's a network issue which seems a tid bit ridiculous to me since restarting the service "fixes" it.
I suspecting it's a memory issue.
Any ideas on where to start.
View 9 Replies
View Related
Apr 13, 2004
I am trying to setup a shape, shape attributes and calculate the cross
sectional area using the formula specified in the tbShapes.Formula field.
See the code below.
What this does is convert the formula
(Width * Flange) + (((Height - Flange) * Leg) * Count)
to
(108 * 4) + (((36 - 4) * 5) *2)
Now I need to calculate the expression above, but the
expression is a varchar string.
Any help?
USE NORTHWIND
GO
SET NOCOUNT ON
CREATE TABLE [dbo].[tbProductCodes] (
[ProductCode] [int] NOT NULL ,
[fkAccountID] [int] NOT NULL ,
[Product] [varchar] (50) NOT NULL ,
[fkShapeID] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbProductCodes (ProductCode, fkAccountID, Product, fkShapeID)
SELECT 2001, 1, 'New Product', 1
GO
CREATE TABLE [dbo].[tbProductTemplateAttributeValues] (
[fkTemplateID] [int] NOT NULL ,
[fkAttributeID] [int] NOT NULL ,
[AttributeValue] [float] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 1, 2, 36 UNION ALL
SELECT 1, 3, 4 UNION ALL
SELECT 1, 4, 5 UNION ALL
SELECT 1, 5, 2
GO
CREATE TABLE [dbo].[tbProductTemplates] (
[TemplateID] [int] NOT NULL ,
[fkProductCode] [int] NOT NULL ,
[Template] [varchar] (50) NOT NULL ,
[fkMixID] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO tbProductTemplates (TemplateID, fkProductCode, Template, fkMixID)
SELECT 1, 2001, 'ProductTemplate', 1
GO
CREATE TABLE [dbo].[tbShapeAttributes] (
[AttributeID] [int] NOT NULL ,
[fkShapeID] [int] NOT NULL ,
[Attribute] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
INSERT tbShapeAttributes (AttributeID, fkShapeID, Attribute)
SELECT 1, 1, 'Width' UNION ALL
SELECT 2, 1, 'Height' UNION ALL
SELECT 3, 1, 'Flange' UNION ALL
SELECT 4, 1, 'Leg' UNION ALL
SELECT 5, 1, 'Count'
GO
CREATE TABLE [dbo].[tbShapes] (
[ShapeID] [int] NOT NULL ,
[Shape] [varchar] (50) NOT NULL ,
[Formula] [varchar] (100) NULL
) ON [PRIMARY]
GO
INSERT INTO tbShapes (ShapeID, Shape, Formula)
SELECT 1, 'Double T', '(Width * Flange) + (((Height - Flange) * Leg) * Count)'
GO
CREATE PROCEDURE usp_shapes_GetCrossSection
@iTemplate int,
@cResult varchar (500) OUTPUT
AS
declare @cAttribute varchar(50),
@fAttribute float
-- Get the formula for the templates shape
SELECT @cResult = s.Formula
FROM tbShapes AS s INNER JOIN tbProductCodes AS pc
ON s.ShapeID = pc.fkShapeID
INNER JOIN tbProductTemplates AS pt
ON pc.ProductCode = pt.fkProductCode
WHERE pt.TemplateID = @iTemplate
SELECT @cResult AS Formula
DECLARE AttributeCursor CURSOR FOR
SELECT sa.Attribute,
av.AttributeValue
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @iTemplate
OPEN AttributeCursor
FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute
while(@@FETCH_STATUS = 0)
BEGIN
SELECT @cResult = REPLACE(@cResult, @cAttribute, CAST(@fAttribute AS VarChar))
FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute
END
SELECT @cResult AS NewFormula
CLOSE AttributeCursor
DEALLOCATE AttributeCursor
GO
-- Test stored proc
declare @iTemplate int, @fResult float
SET @iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @iTemplate, @fResult OUTPUT
SELECT @fResult AS Result
GO
drop table [dbo].[tbProductCodes]
GO
drop table [dbo].[tbProductTemplateAttributeValues]
GO
drop table [dbo].[tbProductTemplates]
GO
drop table [dbo].[tbShapeAttributes]
GO
drop table [dbo].[tbShapes]
GO
DROP PROCEDURE usp_shapes_GetCrossSection
GO
Mike B
View 3 Replies
View Related
May 11, 2007
No body is replying ot my preious post, although people have been reading it.
View 6 Replies
View Related
May 18, 2007
I'm putting together a .NET web page for my group at work to use for server and infrastructure equipment inventory. I'm having a great deal of trouble with one query. The query needs to pull from three tables; 'Rack', 'Server', and 'Infrastructure.' What I need it to do is SUM the number of rack units (slots) used by servers and infrastructure equipment, grouped by the rack number, and subtract it from the number of units the rack has available. I have a query that works for a single rack...
SELECT [Server].[Rack_No], ([Available_Slots] - (SUM([Rack_Units_Used]) + (SELECT SUM([Rack_Units_Used]) AS [RUs]
FROM [Infrastructure] WHERE [Infrastructure].[Rack_No] = 'Rack1'))) AS [RUs]
FROM [Server], [Rack]
WHERE [Server].[Rack_No] = 'Rack1' AND [Rack].[Rack_No] = 'Rack1'
GROUP BY [Server].[Rack_No], [Available_Slots];
...but, I cannot seem to put one together that will generate a report of all the racks. The closest I've come is this query...
SELECT [Rack].[Rack_No], ([Available_Slots] - (SUM([Infrastructure].[Rack_Units_Used]) + (SUM([Server].[Rack_Units_Used])))) AS [RUs]
FROM [Server], [Rack], [Infrastructure]
WHERE [Server].[Rack_No] = [Rack].[Rack_No] AND [Infrastructure].[Rack_No] = [Rack].[Rack_No]
GROUP BY [Rack].[Rack_No], [Available_Slots]
ORDER BY [Rack].[Rack_No];
But I have to problems. First, the results are completely inaccurate. Second, It cannot deal with null values if there isn't at least one server AND at least one piece of infrastructure equipment installed in a given rack. I need it to return a '0' instead of 'NULL' if that is the case.
View 5 Replies
View Related
Jan 25, 2008
Hi there
We've just starting rolling out SQL Server 2005 SP2 (9.0.3042) on 1 single server and some clusters.
Generally we are never implemented post fix etc etc just service pack. I can see more post fixes comes out. For the single box, what is the best practise to apply this upgrade? Apply this fix and hoping no issue :) I guess testing it's the best but if you get the so many hot fixes thay means have to test one by one?
How do you for the cluster? Once you update one node, does it actually update the second node as well?
Waiting for the feedback.
Thanks
View 1 Replies
View Related
