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 RepliesNo body is replying ot my preious post, although people have been reading it.
View 6 Replies View Related---------------------------------------------------------------
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...
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"
Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
AND
(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
TNX
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.
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 RelatedGreetings 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.
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 RelatedI 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 RelatedI 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
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 ?
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 RelatedHi,
Is it possible to write a stored procedure which can post an XML file to a server using HTTP?
Thanks!
Can anyone help me to get the hotfix SQL Server 2000 build 8.00.2215 ?
As i do not find any download link.
How to find out all my post in this forum?
View 2 Replies View RelatedI need to delete a post by particular date and time, what is the syntax for that. anybody help
Articles on various categories
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
This is a test post
View 7 Replies View RelatedI 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 RelatedIf u get any solution then plz share knowledge with me because I am new in SQL
View 3 Replies View RelatedI 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
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 RelatedI'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
hi all,
View 2 Replies View RelatedHELP! 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 RelatedHi 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.
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.
Has anyone experienced any unexpected difficulties with this service pack?
View 2 Replies View RelatedI'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?
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.
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