So Confused

Dec 15, 2007

OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.

I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.

Query1

SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"


Query2

Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed


thank you for any information or help.

View 3 Replies


ADVERTISEMENT

Confused :-(

Sep 13, 2004

Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.

View 2 Replies View Related

M Confused About Using Dbo And ..

Jan 4, 2007

Hi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.

The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.

The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.

Please let me know if there are any (BAD) chances for the following script to fail.


create database mosaikDB737
go
use mosaikDB737

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END

use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput

View 5 Replies View Related

Very New And Very Confused!!

Mar 5, 2008

Hi,

I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??

Sondra





Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click

Dim myReader As Data.SqlClient.SqlDataReader

Dim mySqlConnection As Data.SqlClient.SqlConnection

Dim mySqlCommand As Data.SqlClient.SqlCommand





'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.

mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())

Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"

mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)





Try

mySqlConnection.Open()

myReader = mySqlCommand.ExecuteReader()





If (myReader.HasRows) Then

'Read in the first row to initialize the DataReader; we will on read the first row.

myReader.Read()





Dim content As ContentPlaceHolder

content = Page.Master.FindControl("main")

Dim lbl As New Label()

lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")

content.Controls.Add(lbl)

End If

Catch ex As Exception

Console.WriteLine(ex.ToString())

Finally

If Not (myReader Is Nothing) Then

myReader.Close()

End If

If (mySqlConnection.State = Data.ConnectionState.Open) Then

mySqlConnection.Close()

End If

End Try





End Sub

View 1 Replies View Related

Confused About Permission

Aug 29, 2007

I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account.  So I did so and gave that account read only permissions.  The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions.  If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly.  But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account.  So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance! 

View 3 Replies View Related

Nullable Got Me Confused

May 30, 2006

I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.

View 3 Replies View Related

SP - Dazed And Confused

Mar 13, 2000

Hello,

I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).

The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)

I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value


In my SP I simply do;

INSERT blah blah
if @@error = 0
return(100)
else
return(200)

(Idon't ever get back "200")

Any ideas???

Thanks for your help.

View 1 Replies View Related

Confused About Replication

Dec 18, 2006

Hi

I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody

My goal:

I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.

Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:

Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.

Problaby I need some kind of an database on client and subscription to make the replication. :confused:

please help me, I'am almost finished with my project only replication part is over my head :(

if someone can point me to right direction in this issue. I would be greateful. :cool:

Thanks

KK

View 13 Replies View Related

Xp_sendMail (Confused)????

Jul 2, 2002

I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.

How can I use xp_sendmail using a dabase other than Master?? Please Help.

View 1 Replies View Related

Confused Joins

Mar 21, 2007

I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?

select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x

where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y


The Yak Village Idiot

View 4 Replies View Related

Confused. Need Some Help About SQL Coding

Jun 14, 2007

hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.


selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')



selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')


also is it wise to use Group by and having in sub-queries?

View 2 Replies View Related

Confused About Creating A Dup Mdf

Mar 13, 2008

Hi
I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X.
Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?

TIA

rank beginner

View 2 Replies View Related

Alias Has Confused Me.

Jul 23, 2005

I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.

View 5 Replies View Related

Little Bit Confused About Databases On CE

Nov 10, 2006

Hi there,

i need a database for my Windows CE application which i can update from a desktop application.

I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.

So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?

Can anyone give me a hint what the best solution could be?

Im using Visual Studio 2005 and a CE device.



Thanks a lot

View 1 Replies View Related

Overwhelmed And Confused ...

Mar 28, 2007

I'm trying to get up-to-speed on developing new websites using Visual Web Designer and SQL Server Express.



I have previously installed various Microsoft web development components (Visual Studio 2005, .NET Framework 1/2/3 and SQL Server 2005). I've also tried out the new Web Expressions Beta (and Design and Blends, altho the install keys for the latter two never worked and I never received an answer as to what to do for that in the appropriate forums).



TODAY, I'm just trying to get Visual Web Developer and SQL Server Express installed so that I can start down the path of "Connecting to an Existing Database" as outlined in the (downloaded) "Microsoft Visual Web Developer 2005 Express Edition - Build a Web Site Now!" PDF gude (page 138: "Start Visual Web Developer and display the Database Explorer window.").



HOWEVER, when I attempt to install "SQLEXPR32.exe" I get the following message after all the unpacking seems to complete:


"SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Franework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme."

NOW, my first confusion is this: is "SQLEXPR32.exe" Server 2005 or SQL Server Express -- and what's the difference? Following this bit of unhelpful error message, I searched through the SQL Server Express pages and found a live link to a "uninstall tool" that hinted that it could solve my left-over garbage problems. However, then I run that I get:


"The setup has encountered an unexpected error in datastore. The action is Write_CommitFlag .... blah-blah-blah"

so it sounds like the automatic uninstall tool has gotten lost.



ODDLY, the codish window which follows contains the following:


"




Reference to undefined entity 'nbsp'. Error processing resource 'file:///C:/DOCUME~1/Kevin/LOCALS~1/Temp/IXP000.TMP/failed....

&nbsp; <!-- div id="RPCreated" style="display:none">
-----------------------------^




" which very peculiarly looks like something has gotten lost and confused over a non-breakable space? ("&nbsp;"). Huh?



ME? I'm totally lost! Is there no step-by-step cookbook that I can use to just start all over and go through the painful (DSL) downloads and get my show on the road?



Sign me ... "depressed on the garden isle of Kauai ..."

View 5 Replies View Related

Lost And Confused About SQL

Jul 21, 2007

What is SQL?

After running a disk cleanup and defrag, I now have this icon and it tells me SQL is not connected. What is it? I am on a campus which supplies "high speed" internet connection... Please, help! I'm not savvy at any of this!

View 4 Replies View Related

Using CheckpointFile: Confused!

Sep 12, 2007



There are two properties in using CheckpointFile: CheckpointUsage and SaveCheckpoints:
It says: SaveCheckpoints indicates whether the package saves checkpoints while CheckpointUsage indicates whether the package uses checkpoints.

The confusion is: If CheckpointUsage is set to NEVER, and SaveCheckpoints is set to be YES, is there any checkpoint file saved on disk even if a filename is specified? It is easy to find out through a simple testing, but the teminologies here are kind of confusion.

comments please ?

View 1 Replies View Related

Confused About Pivot

Sep 5, 2007

I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y

into this....

A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y


Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?

View 3 Replies View Related

Confused By Drilldown

Mar 5, 2007

the field which has drilldown option is always showed minimum width after delopying to website

I attempted to set all properties of this fields as i can,such as "width","can grow" etc, but all do not work

any idea about this?

thanks

View 1 Replies View Related

SqlDataSources Or DataSets - Confused

Apr 10, 2008

 I was watching the "To Do List" AJAX video created by Joe Stagner and while watching the video I noticed (in designer) Joe use a Dataset to populate a Gridview and this got me thinking. I have a few gridviews in my app at work that just use SqlDataSources, and they seem to work fine, but is it better to use Datasets ? 

View 3 Replies View Related

Confused Need Help With SQLDataSource Control

Jun 2, 2008

I have a gridview that is tied to a SQLDataSource control. As the gridview is filled some of the ASPX code calls some code in the code behind page. Where I am having trouble is that in one of these calls I need to some how pass the id filed ("LISTID")of the table for the SQL statement in the code behind page and I'm not sure how to do it.
The code behind function is always using the same id and I do not know how to change it. Below is all the code.
 
Here is the calling code in the ASPX page.
"DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399"> Here is the entire ASPX code.     class="header">            class="title">My Lists         "Span1">            Current List Filter:             "DropDownList2" cssclass="filterdropdown" AutoPostBack="true" runat="server">                "Active" Value="False">                "Done" Value="true">                                    --------------------------------------------------------------------------------"separator2"/>                        "list2">        "ScriptManager1"  EnablePartialRendering="true"  runat="server">          "Up2" UpdateMode="Conditional" runat="server">                                                                  "GridView1" runat="server" AutoGenerateColumns="False"                         DataKeyNames="LISTID" DataSourceID="SqlDataSource1"                        AllowPaging="True" AllowSorting="True" EnableViewState="False" GridLines="None"                         AlternatingRowStyle-BackColor="#FFFFCC" AlternatingRowStyle-Font-Size="Small"                         ForeColor="#FFFFCC" Font-Size="Small" AlternatingRowStyle-ForeColor="Black"                         Width="100%" HeaderStyle-ForeColor="White">                                                     "False">                                                            "LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update" ForeColor="#003399">                                "LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" ForeColor="#003399">                                                                                        "LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" ForeColor="#003399">                                "DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399">                                                        "buttons">                            "commands">                                                                        "ITEM_DETAILS"  HeaderText="Details"  SortExpression="ITEM_DETAILS" ControlStyle-ForeColor="#003399">                            "name_edit">                            "name">                            "name">                                                                        "Pri" itemStyle-HorizontalAlign="Center" SortExpression="Priority" ControlStyle-ForeColor="#003399">                                                            "DropDownList1" Width="75" SelectedValue='<%# Bind("Priority") %>' runat="server">                                    "High" Value="3">                                    "Medium" Value="2">                                    "Low" Value="1">                                                                                                                                                        "pri" Text='<%# FormatPriority(Eval("Priority")) %>' runat="server">                                                        "priority">                            "#003399">                            "priority">                                                                        "DUEDATE" itemStyle-HorizontalAlign="Center"                              HeaderText="Due Date" DataFormatString="{0:MM/dd/yyyy}"                            SortExpression="DUEDATE" >
                            "Center">                        
                        "COMPLETEDON" itemStyle-HorizontalAlign="Center"                              HeaderText="Completed On" DataFormatString="{0:MM/dd/yyyy}"                            SortExpression="COMPLETEDON" >
                            "Center">                        
                        "Done" itemStyle-HorizontalAlign="Center" SortExpression="IsComplete">                                                            "CheckBox1" runat="server" Checked='<%# Bind("IsComplete") %>'>                                                        "iscomplete">                            "iscomplete">                                                            "Done" Text='<%# FormatDone(Eval("IsComplete")) %>' runat="server">                                                                                           
                                                                                                "Button2" runat="server" CommandName="Something" DataTextField="LISTID" DataTextFormatString="{0} active items" text='<%# Eval("LISTID") %>'>                                                                                                                                                                                                  "Empty">No lists                                                                        "White">                        "#FFFFCC" Font-Size="Small" ForeColor="Black">                                                                                                                         "Div1">               "Up3" UpdateMode="Conditional"  runat="server">                                            Add New List: "AddItem2" cssclass="newitem"                     runat="server">                "Button1" runat="server" OnClick="AddListBtn_Click" Text="Add">                                "txtdate" runat="server">                                                        "Button1" EventName="Click">                                                                        "SqlDataSource1" runat="server"         ConnectionString="&lt;%$ ConnectionStrings:FastTrackConnectionString %>"         SelectCommand="SELECT * FROM [TODO]">     Here is the code behind page code. Protected Function IsDeleteBtnVisible() As Boolean        '        Return IIf(itemCount = 0, True, False)
        'This is for my gridview        Dim strConn2 As SqlConnection = New SqlConnection        Dim cmd2 As SqlCommand = New SqlCommand        Dim rs2 As SqlDataReader        Dim test1 As String        Dim test2 As String
        strConn2.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("FastTrackConnectionString2").ToString()        strConn2.Open()
        cmd2.Connection = strConn2        cmd2.CommandText = "SELECT LISTID, (SELECT COUNT(0) AS ItemCount FROM TODO_SUB_ITEMS WHERE(LISTID = TODO.LISTID) AND (ISCOMPLETE ='False')) AS ItemCount FROM TODO WHERE (ISCOMPLETE ='False')"
        rs2 = cmd2.ExecuteReader()
        rs2.Read()
        Dim testlistid As Integer = rs2(0)        Dim intholder As Integer = rs2(1)
        strConn2.Close()        cmd2 = Nothing        strConn2 = Nothing
        Return IIf(intholder = 0, True, False)
    End Function

View 7 Replies View Related

Confused About Using Lock Hints?

Mar 19, 2006

I noticed that the online books say the following:
Note  The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary.
Also, at another place in online books, it says:
The table hints are ignored if the table is not accessed by the query plan.
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?

View 24 Replies View Related

Xp_send Mail (Confused)!!

Jul 2, 2002

I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.

How can I use xp_send mail using a dabase other than Master?? Please Help.

View 3 Replies View Related

Very Confused - Unused Space

Mar 29, 2001

Hello,
I have a table which resides on a diff.filegroup.
The space allocated is 7700MB.
Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as
rows reserved data index Unused
at 1000hrs 629879 6777904 5068344 8 1709552
After an hour 637537 6780336 5070800 8 1709528
After 2hours 643883 6782560 5072904 0 1709656
After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb
after 3hrs 647239 6783712 5074056 0 KB 1709656 K
If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down.
What behavior is this?How can I find out how much space is left in this table residing on diff.file group?
TIA

View 3 Replies View Related

Query Optimization - Confused!

May 2, 2002

i have query similar to this:

select count(a.callid) from tbl1 as a
inner join tbl2 as b on a.calldefid=b.calldefid
where a.programid=175


select count(a.callid) from tbl1 as a
inner join tbl2 as b on a.calldefid=b.calldefid
where b.programid=175

callid - pk on tbl1
calldefid - nonclustered index on both tbl1 and tbl2
programid - nonclustered index on both tbl1 and tbl2
tbl2 is the smaller table

from my understanding, the second query will run faster because you reduce the records in the smaller table, then join to the larger table (tbl1).

but can you explain to me why limiting the rows on tbl1 first, then joining to tbl2 would take longer?

View 1 Replies View Related

Newbie Confused With Triggers

Oct 3, 2001

Hi gurus !

I have two tables. One is a parameter-table which has info of all allowed ID-values in different tables, ie
Table ID
----- ---
tbl_a 123
tbl_a 125
tbl_b 100
tbl_b 110
...

Then I have the data tables 'tbl_a' and 'tbl_b' which has following structure:
ID Datafields
--- -------- ---- ----
123 aaa
123 bbb
125 bbb

I want not to use unique-keys since there could be several rows with same ID (but the data is of course different).

I have tried to write a trigger to check that a correct key is inserted in data tables. The code I wrote is

CREATE TRIGGER trig1 ON tbl_a FOR INSERT, UPDATE AS
declare @a char(3) select @a = ID from inserted
if @ not in (select ID from Parameter where Table = 'tbl_a')
RAISERROR ('ID is false', 16,1)

When inserting the trigger works fine, but when updating with allowed value I get error "Key column information is insufficent or incorrect. Too many rows were affected by update" and when updating with not-allowed value the message is "Another user has modified the contents of this table or view; the database row you are modifuing no longer exists in the database. ID is false (ie my errortext).

This should be easy. What goes wrong? How to manage this situation? I'm using triggers since check-constraint cannot use values from different tables, I guess. I'm using SQL Server 2000

Thanks in advantage,
Ben

View 4 Replies View Related

Why Doesnt This Work............confused!?!?!

Mar 21, 2007

This is an insert statement i'm trying to run. I want it to only insert this recird if the corresponding EMPLOY_REF exists in the EMPLOYEE table. Heres my statement:


INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE)
VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000')
WHERE '8971' IN (SELECT EMPLOY_REF FROM EMPLOYEE)


This is the error message i'm getting.

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

Any help would be greatly appreciated. Thanks

View 14 Replies View Related

Confused With Linking Tables

Dec 13, 2007

Hi and thanks for taking the time to read this. I get really confused really quickly when it comes to table linking, and I think I am doing it the hard way. I am going to show my stupidity and ask if there is a better way than what I do, because it takes a LOT of querying and seems like a huge waste.

In reality, I have about 20 tables to join like this, but I'm going to use three for the example:

"Main" Table
AcctNoNameHeightWeight
1Bob13
2Phil21
3Jim15
4Lisa22

"Height" Table
HeightIDHeightName
1Giant
2Tall
3Medium
4Short

"Weight" Table
WeightIDW_Name
1Skinny
2Normal
3Overweight
4Obese


What I have been doing is:

SELECT
Main.AcctNo,
Main.Name,
Height.HeightName,
Weight.WeightName
FROM
Main
INNER JOIN Height ON
Height.HeightID = Main.Height
INNER JOIN Weight ON
Weight.WeightID = Main.Weight
WHERE Main.AcctNo < 3


My question: is doing 20 INNER JOINS really the best way to do this with 20 tables, or is there some other way to link Main.Height to Height.HeightID that is less intensive?

Thanks again for reading, and in advance for any information or insight you can provide.

View 1 Replies View Related

So Confused Need Help Regarding Error Logs And SQL

Jan 14, 2008

I was under the impression or i always got following entries in SQL Agent error log when server restart and there are some entries in sql error log as well which confirms that server restarted. but today when i was checking error log i saw same entreis in SQL agent error log but there were no entries in server error log. now i got really confused whether my server is restarted or not........... because if it is restarted then there must be some messages in server error log.....
can some one please let me know when and why following messages appear in SQL Agent Error Log



Information,[171] There are 9 alert(s) in the alert cache
Warning,[425] delay_between_response attribute (10 sec) of alert (9) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (8) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (7) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (6) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (5) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (4) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (3) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (1) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (2) is less than poll interval for this alert (20 sec)
Information,[170] Populating alert cache...
Information,[168] There are 3 job(s) [0 disabled] in the job cache
Information,[297] SQLServer Message: 0<c/> SQLServerAgent Monitor started successfully. [SQLSTATE 01000]
Information,[133] Support engine started
Information,[167] Populating job cache...
Information,[110] Starting SQLServerAgent Monitor using '' as the notification recipient...
Information,[193] Alert engine started (using Eventlog Events)
Information,[146] Request servicer engine started
Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
Information,[174] Job scheduler engine started (maximum worker threads: 800)
Information,[129] SQLAgent$XYZ starting under Windows NT service control
Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
Information,[124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'Merge' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 200)
Information,[124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 80)
Information,[124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 80)
Information,[124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 160)
Information,[432] There are 10 subsystems in the subsystems cache
Information,[431] Populating subsystems cache...
Information,[339] Local computer is ABC running Windows NT 5.2 (3790) Service Pack 1
Information,[310] ** processor(s) and **** MB RAM detected
Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is ServerName
Information,[102] SQL Server ODBC driver version 9.00.2047
Information,[101] SQL Server ABC version 9.00.2047 (0 connection limit)
Information,[100] Microsoft SQLServerAgent version 9.00.2047.00 (x86 unicode retail build) : Process ID 8100
Error,[298] SQLServer Error: 2812<c/> Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)

View 4 Replies View Related

Joins Or Subqueries - Confused

Mar 25, 2008

Hi all,

right now am using sub queries for the below query.. but is this a good way or do i need to use joins.. however if you see the "downloads" column i need to get a count of how many download does each user did on the files/url.

SELECT
UU_Title as Title,
FileName_Url = (Case When UU_Type = 'U' Then UU_url else UU_Filename End),
UU_UserID as UserID,
B.User_FirstName + ' ' + B.User_LastName AS Author,
UU_TimeStamp as DateCreated,
UU_LastModified as DateModified,
Downloads = (select count(*) from Vportaldevstats.[dbo].stats_download A where A.stat_AuID = UU_AssocAuID ),
Presentation_Associations = (Select Presentation_Association = (Case When au_subject is Null or au_subject != '' then au_title else au_title end) from VPortalDev.[dbo].au A where a.Au_id = UU_AssocAuID),
Community_Associations = (Select community_Association = (Case When au_subject is Null or au_subject != '' then au_title else au_title end) from VPortalDev.[dbo].au A where a.Au_id = UU_AssocCommunityID)
From Vportaldev.[dbo].user_upload A , Vportaldev.[dbo].[User] B
Where A.UU_UserID = B.User_ID
Order by UU_LastModified Desc

and i tried re-modifying the above query as joins.. just wanna know is this the best way to do it. (and i don't know how to handle count(*) downloads .. pls.help.

SELECT
UU_Title as Title,
FileName_Url = (Case When UU_Type = 'U' Then UU_url else UU_Filename End),
UU_UserID as UserID,
B.User_FirstName + ' ' + B.User_LastName AS Author,
UU_TimeStamp as DateCreated,
UU_LastModified as DateModified,
COUNT(*)

from
Vportaldev.[dbo].user_upload uu
Innerjoin Vportaldev.[dbo].[user] u
ON UU.UU_USERID = U.USER_ID
INNERJOIN Vportaldevstats.[dbo].stats_download SD
ON UU.UU_ASSOCAUID = SD.STAT_AUID
INNERJOIN VPortalDev.[dbo].au AU
ON AU.Au_id = UU.UU_ASSOCAUID


any help is much appreciated.

View 6 Replies View Related

SQL Query- Confused With Nesting...

Jul 20, 2005

It's me, one more time. My last request didn't quite filter all my records,I have one more field I need to evaluate in the script.DESCR TYPE SELL StartDate EndDate Number65048 04 Price A 4/21/2004 4/26/2004 3545665048 06 Price C 4/20/2004 4/27/2004 3545965048 08 Price B 4/22/2004 4/28/2004 3455965049 04 Price A 4/19/2004 4/24/2004 3859565049 06 Price B 4/22/2004 4/25/2004 3859465049 06 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 3811265050 06 Price B 4/18/2004 4/28/2004 3855065050 07 Price C 4/17/2004 4/29/2004 38110Descr, Type, Sell and Number are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with the followingcriteria:#1. "Date I Enter" >= Startdate#2. "Date I Enter" <= Enddate#3. Highest TYPE for each DESCR#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)Results for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/2004 3455965049 09 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 38112I REALLY appreciate the help!Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

View 1 Replies View Related

Goup By Clause Confused....

Jul 20, 2005

Hi NG,I have the following problem that I hope you can help me with (MS-SQL server2000)Imagine a statement like this:"select id, firstname, (select top 1 id from testdata) as testid, lastnamefrom nametable order by firstname"I would like to have this grouped by "lastname"...I assume that I have touse the "Group by" clause, but it keeps complaining about id, firstname, etcnot being in the clause...if I just inserts the "Group by lastname" in thestatement above.How do I group these data?--regards,Summa

View 11 Replies View Related

Very Confused About Query Designer

Apr 13, 2007

I am having a problem with what appears to be a very trivial task in the graphical query designer. When I enter =@County into the filter column quotes are automatically added like = '@County' which the makes the query string inaccurate (looking for a literal County = "@County" in the where clause).



I am using Studio 2005 and SQL Server 2005 with the newest service packs and retreiving data from an Oracle 10G database.



If I remove the quotes and run the query through the generic query designer in my SQL reporting project I get the error "ORA-00936: missing expression".



It is as if the SQL Reporting Project does not understand the meaning of the @ symbol for entering a parameter.



What am I missing here? I thought @ specified that this would be a parameter and you then just needed to supply the values for the parameter.



Any help or suggestions would be appreciated.



View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved