I'm trying to learn how to make and use aliases for two tables in in
this update statement:
ALTER PROCEDURE dbo.UpdateStatus
AS UPDATE dbo.npfields
SET Status = N'DROPPED'
FROM dbo.npfields NPF, dbo.importparsed IMP
LEFT JOIN IMP
ON (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 a
difference in the queries.
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
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.
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
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
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
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!
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.
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)
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:
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.
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
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?
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?
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?
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....
<!-- div id="RPCreated" style="display:none"> -----------------------------^
" which very peculiarly looks like something has gotten lost and confused over a non-breakable space? (" "). 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 ..."
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!
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.
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?
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 ?
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="<%$ 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
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?
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.
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
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?
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
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'.
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:
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.
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)
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
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! =-----
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
