Creating Queries Is Hard! Help Needed!
Jan 22, 2008
I am using a poker application that imports all played hands into a PostgreSQL database. I'm trying to write some queries for that database so I can make good use of the data. I figure PostgreSQL and SQL server probably have similar language. And I know these forums are great. So I'm posting here for help.
This is a working code snippet that retreives three properties of a player:
Code Snippet
SELECT sum(totalhands) AS HANDS, sum(vpiphands) AS VPIPHANDS, sum(pfrhands) AS PFRHANDS
FROM compiledplayerresults WHERE compiledplayerresults_id in (
SELECT compiledplayerresults_id FROM compiledresults WHERE play
SELECT player_id FROM players WHERE playername='PLAYERNAME'));
This is all I can do with my skills right now. However I very much want to learn to query for more things:
- I'd like to be able to make a query for several players at the same time
- I'd like to be able to get the aggregate properties for all players who are not PLAYERNAME1 or PLAYERNAME2
- I'd like to be able to get the aggregate vpiphands property from all players whose totalhandsis > 100 and < 1000.
I hope you can help me with that. If you can, that will make me very happy and grateful! Thanks!
View 4 Replies
ADVERTISEMENT
Sep 2, 2007
Hi there,
I am using this stored procedure in sql.
I have 6 tables. One is called employees. This is what I need to be able to do. A user enters a new employee into a winform, picks a role, division, manager, technicalskill set and applications from the drop down lists and hits save. The employee table should be the only one updated and has these columns only.( firstname, lastname, dvisionid, managerid, roleid,techskillsid, and appID). At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null. It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought? If I try to replace the role with roleid etc, it will just tell me I can't convert string to int which is understandable. How do I do this?
CREATE PROCEDURE sp_InsertEmployee
@Firstname nvarchar(50),
@Lastname nvarchar(50),
@Role nvarchar(50),
@Manager nvarchar(50),
@Division nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME) VALUES (@FIRSTNAME, @LASTNAME)
INSERT INTO [ROLE] ([ROLE]) VALUES (@ROLE)
INSERT INTO MANAGER (MANAGER) VALUES (@MANAGER)
INSERT INTO DIVISION(DIVISION) VALUES (@DIVISION)
END
GO
My C# code is like this:
SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection);
sqlC.CommandType = CommandType.StoredProcedure;
sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 50, "Firstname"));
sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname"));
sqlC.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int, 50, "RoleID"));
sqlC.Parameters.Add(new SqlParameter("@ManagerID", SqlDbType.Int, 50, "ManagerID"));
sqlC.Parameters.Add(new SqlParameter("@DivisionID", SqlDbType.Int, 50, "DivisionID"));
//sqlC.Parameters[0].Value = 4;
sqlC.Parameters[0].Value = FirstnameText.Text;
sqlC.Parameters[1].Value = Lastnametext.Text;
sqlC.Parameters[2].Value = RolecomboBoxTest.Text;
sqlC.Parameters[3].Value = ManagercomboBox1.Text;
sqlC.Parameters[4].Value = DivisioncomboBox2.Text;
int i = sqlC.ExecuteNonQuery();
//sqlC.ExecuteNonQuery();
Sorry for pasting so much.
View 2 Replies
View Related
Sep 7, 2007
priceStockID productID supplierID price
1 1 1 100
2 1 2 110
3 2 1 10
4 2 3 20
5 3 1 30
6 3 2 20
7 3 3 15
8 4 1 40
how can I get the priceStockID for the lowest price of each product in the most efficient way
the result table should be like below
priceStockID productID supplierID price
1 1 1 100
3 2 1 10
7 3 3 15
8 4 1 40
View 2 Replies
View Related
Mar 23, 2008
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString
Dim Description As String = DescriptionTB.Text
Dim PlaceName As String = PlaceNameTB.Text
Dim Location As String = LocationTB.SelectedValue
Dim PropertyType As String = LocationTB.SelectedValue
Dim Price As Integer = PriceTB.Text
Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?"
Dim cmd As New SqlCommand(Sql, con)
cmd.Parameters.AddWithValue("Description", Description)
cmd.Parameters.AddWithValue("PlaceName", PlaceName)
cmd.Parameters.AddWithValue("Location", Location)
cmd.Parameters.AddWithValue("PropertyType", PropertyType)
cmd.Parameters.AddWithValue("Price", Price)
Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
View 4 Replies
View Related
Sep 2, 2004
Hi
Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records
item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete
my view should be
itemgroup status
1 incomplete
2 complete
All the Statuses are not predefined...they get added as and when required........
Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.
Thanks
View 6 Replies
View Related
May 14, 2008
I know the method where you select each and every table and manually create foreign key relationships to other tables in the database visually or by sql. Is there any way in SQL server 2005, to create these relationships using wizard, where it checks for the same column names in different tables and creates the relationships. If yes, please let me know how to do it in SQL server 2005.
View 3 Replies
View Related
Feb 16, 2007
I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
View 9 Replies
View Related
Mar 20, 2007
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies
View Related
Feb 21, 2006
Can someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:
StartJob
Begin Sql execution
Info
Executing 01.00.00.SqlDataProvider
StartJob
Start Sql execution: 01.00.00.SqlDataProvider file
Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob
End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* -------------------------------------------------------------------------------------
/ ListTAS_Journal
/ ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@PortalID int,
@SortOrder tinyint = NULL,
@Str_Title varchar(100) = '',
@Str_Text varchar(100) = ''
AS
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
This SP works on the test site.
Any help would be creatly apreciated
Mark
View 2 Replies
View Related
Mar 13, 2006
Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.
So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.
Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.
Thanks Steve
View 7 Replies
View Related
Jul 20, 2005
Hi all,Sorry for HTML, there is a lot of code & comments I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :( Please help me figure out what stops it mid way? I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*): Here is my stored procedure:CREATE PROCEDURE csp_AuthorAccountInfo@CandidateID int,AS DECLARE @ScriptsNo int, @ManuscriptID int SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @CandidateID/* this is where it stops all the time :(Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/SELECT c.*, l.LocationID, @ManuscriptID=m.ManuscriptID, l.State, cn.Country FROM Candidates c INNER JOINManuscripts m ONc.CandidateID = m.CandidateID INNER JOINLocations l ON c.LocationID = l.LocationID INNER JOINcn ON l.CountryCode = cn.CountryCodeWHERE c.CandidateID = @CandidateID/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.CommentsFROM Manuscripts m INNER JOINManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOINManuscriptStageNames msn ON ms.StageNameID = msn.StageNameIDWHERE m.ManuscriptID = @ManuscriptIDORDER BY ms.DatePosted DESCGO
View 2 Replies
View Related
Mar 23, 2008
Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
View 3 Replies
View Related
Jan 23, 2008
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters.
the variables will be: @lastmonth, @curryear and @id
@lastmonth should inherit Session variable intlastmonth
@curryear should inherit Session variable intCurrYear
@id should inherit Session id
One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id")
The rest of the queries will be similar and use all 3 variables as well.
How can I go about this and how will queries be seperated.
View 2 Replies
View Related
Nov 16, 2014
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
View 9 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
Apr 5, 2005
Hi,
I need to use a top and a join in the same sql. To get 10 top refnr from orders_refnr. That works fine to I use this:
SQL = "SELECT TOP 10 refnr, antal = COUNT(refnr) FROM orders_refnr INNER JOIN produkter ON (orders_refnr.refnr = produkter.referensnummer) GROUP BY refnr ORDER BY antal DESC"
But I need to be able to get information from more fields than the field refnr. How can I specify more fields? I need to get other fields from produkter.
Please helt I´m really stucked.
View 4 Replies
View Related
May 17, 2007
I have gotten some criticism from coworkers regarding this test and just wanted to see what you guys think. I realize the wording could use improvement and any criticism towards making it easier to understand is much appreciated.FWIW - I had to solve this problem on the job so I feel it is a real-world test that helps me understand how people think and if they try to find alternate solutions.Thanks!~~~~~~~~~~~~~~~~~~~~Given a table that has over 100,000 records…SUBSIDIARY=========PARENT_IDINTCHILD_IDINTULTIMATE_PARENT_IDINTCLEANUP_INDBIT…where each PARENT_ID can have multiple CHILD_ID values, but the PARENT_ID should not equal the CHILD_ID. After an initial data load, the ULTIMATE_PARENT_ID and CLEANUP_IND columns contain NULL values (see page 2 for sample data).ULTIMATE_PARENT_ID is defined as the topmost parent in the chain for the particular CHILD_ID record, so if the chain was only 2-level’s deep the ULTIMATE_PARENT_ID is the CHILD_ID’s PARENT_ID’s PARENT_ID.Please write an answer for all three questions below:A)Which of the following queries should you run first?B)Write an optimized query to identify the ULTIMATE_PARENT_ID for each CHILD_ID and set its value into the ULTIMATE_PARENT_ID column.C)Write a query to identify ALL of the circular references and mark each record that is a circular reference by updating the CLEANUP_IND column to 1.~~~~~~~~~ Page 2 ~~~~~~~~~ Sample Data, remember though this table has over 100,000 records and the parent-child chain can go n-levels deep – where n is not known.PARENT_IDCHILD_IDULTIMATE_PARENT_IDCLEANUP_IND1024512NULLNULL362300NULLNULL887541NULLNULL10221024NULLNULL546887NULLNULL5122305NULLNULL112967NULLNULL697123NULLNULL901452NULLNULL2300666NULLNULL334445NULLNULL512903NULLNULL884554NULLNULL313313NULLNULL554884NULLNULL112119NULLNULL967555NULLNULL2305333NULLNULL33336NULLNULL541546NULLNULL10301020NULLNULL112999NULLNULL
View 14 Replies
View Related
Feb 13, 2006
hello
i have a proplem in query
i have 2 tables in my sql db one named stuednt include fields(id,name)
and one table named stu_cources include fields(id,course_name)
ok
i want to query the student that have courses EX. mcse
the result that i want from 2 tables
ID | NAME | Coures_NAME
in MSHFLEXGRID1
any one help me plz ...
View 4 Replies
View Related
Nov 14, 2000
hi, I have NT server which has drive c: 500 MB and drive d has 44 GB.
I know that the person who set up this server did not give enough space to the c drive, here is the problem. I am running sql server 7.0 which has 30 GB of data in the d drive. I need to reconfigure the NT hard drive so I can allocate 2 GB for C drive and 42 GB for D drive.
What is the best, safe method to accomplish this task.
Ahmed
View 3 Replies
View Related
Sep 27, 1999
After experiencing a hard drive failure i have reinstaled MSSQL7 on one drive and have a database which I need to recover on separate physical drive. How can I go about doing this?
View 1 Replies
View Related
Jul 17, 2003
Hi,
I have ran
1. xp_fixeddrives and got the result
drive MB free
----- -----------
C 1708
D 16311
2. I ran Backup Wizard in EM and able to see only above drives
3. But if ran backup in EM able to see more than 10 Drives(like C,D,H,I,J,M,N and etc).
Why I can able to see those difference?.
How do I find out exactly how many drives are there in this server without directly going to that server?.
I appreciated your valuable answere.
Thanks,
Ravi
View 8 Replies
View Related
Jul 30, 2001
Hi, I'm looking for a way to check the free space left on the hard drives and then if needed send an alert to notify when we need to free up some space. I played around with the performance monitor and realized I could do it that way but I think you would have to leave the performance monitor running all the time and I'm not sure if I want to do that. I also read about the xp_fixeddrives proc that displays how much free space is available but then I don't know what to do from there? Does anyone have any recommendations for the best way to do this.
View 3 Replies
View Related
Feb 19, 2007
Right, I have this database that I need to sort, I'll give you an example:
ID Name Value
2312 Sega 200
5678 Blizzard 215
3412 Bullfrog 210
6798 Nintendo 195
Now, what I need to do is to sort it, perform calculations on and I need the list to be sorted with a predefined post as the top result, say like this one time:
ID Name Value
3412 Bullfrog 200
2312 Sega 210
5678 Alizzard 215
6798 Nintendo 195
as you can see sorting it alphabetically would lead to
5678 Alizzard 215
3412 Bullfrog 210
6798 Nintendo 195
2312 Sega 200
(or the other way around if you play with asc/desc)
by id would be
2312 Sega 200
3412 Bullfrog 210
5678 Alizzard 215
6798 Nintendo 195
There aren't any top or bottom values sort of speak for the posts I want to be on top, so...how to sort this like this?
3412 Bullfrog 200
2312 Sega 210
5678 Alizzard 215
6798 Nintendo 195
the order after the top one is irrelevant.
Now...I know I could sort this by doing something like
"Select * FROM blablabla WHERE Name = 'Bullfrog'"
and then doing "Select * FROM blablabla" and then just bypassing that post in asp/php code or whatever, but that would be a pain for me to do as I have to perform some massive calculations and the code would be alot larger then needed be
Its a brain teaser allright...can you help me out?
View 5 Replies
View Related
Apr 7, 2004
Hello, everyone:
Does any one have an idea for hard drive configuration on SQL Server 2K? How to determine the RAID level for datafile and log file? Thanks
ZYT
View 2 Replies
View Related
May 18, 2004
I was wondering if anyone played around with changing the allocation unit size when formatting the hard drive the SQL server is running on. I would think that setting it higher to account for the larger size of the database files would help, but I'm not sure.
Anyone have any thoughts?
View 3 Replies
View Related
May 29, 2007
Hello!
I have 2 harddisk in my computer and I have SQL 2005 Express on 1 of them (let's say C:), however, my C: is going to be full soon! Once it is full, is it possible to create a table on my other harddisk which the server can recognise?
Thanks!
OB
View 4 Replies
View Related
Oct 18, 2006
Hello, I am experimenting with indexes and hope people can shed lighton some of my problems.I am using SLQ 2000 on Win 2000 Server. Using the following query fordiscussion;--------------------------------SELECT TOP 1000000E.EUN_Numeric, -- Primary KeyE.EUN_CODE, -- VarCharE.[timestamp] --,--E.Model -- Computed column (substring of EUN_CODE)FROM dbo.Z1_EUNCHK E--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND-- E.[timestamp]< DATEADD ( wk , -4, getdate() )ORDER BY E.[timestamp] DESC-----------------------------------Problem 1) If I set up a single Index on the TimeStamp (plus the PK onEUN_Numeric) then there is not improvement in performance.It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Codethen I get a good improvement. This is also thecase with the "where" clause added. I am using query analyser. Theimprovement is 14 secs to 3 secs (mainly with the removal of the sortprocess)Why?My expectation is that if my query uses [timestamp] column then surelyan index only on this is adequate.Problem 2) Introducing the simple computed column into the query takesthe time to 15 secs (with Sort processes involved).Why does revert back to sorting process when previous the index wasused ?Regards JC......
View 6 Replies
View Related
Jul 16, 2007
I have a 75 GB hard drive and a 300 GB. I want to mirror the 75 to the 300 and use the extra space as data storage. Is this possible if I partition the 300 and then mirror the hard drives.
Thanks
View 5 Replies
View Related
Oct 5, 2007
How can I do this with vbscript, or C# ?
- Copy backup files down from a network share, into the data directory of my local sql 2005 instance
- perform a restore using the files copied from above
- Execute a dts package
More info:
Our databases are scripted and exist on the typical development, and testing enviroments. So as I get ready to start a new application, I want my local sql instance to be updated based on structure changes as well as data. So I have to apply the changes from the scripted sources and pull over the data. I would naturally like to automate this.
ideas / suggestions welcome
View 6 Replies
View Related
Nov 8, 2007
I have a new server.
It was shipped with a 76 gig drive setup in RAID 1 (2 disk) and a 400 gig drive setup in RAID 5 (4 disk).
I would like to determine what is the best way to setup up the partitions. What size and what should be placed on each.
Like the C: Drive...Should I just put Windows on there and nothing else? Do I stand to gain something from not using part of that 76 gigs as a D: drive for my apps?
View 1 Replies
View Related
Feb 8, 2007
Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?
Cheers
Darren
View 7 Replies
View Related
Jun 6, 2007
Me.SqlConnection1.ConnectionString = "workstation id=""XXXXXXXX"";packet size=4096;user id=XX;data source=""XXXXXXX"";persi" & _
"st security info=True;initial catalog=manufacturing;password=XXXXX"
What excatly is this statement doing it looks to be creating a trusted connection to a certain workstation?
View 3 Replies
View Related
Feb 12, 2008
Hi!I'm desperating here!!! Two questions:1º Is line 13 realy selecting all the records with the username samurai (in this case)?!2º How do I fill the Boolean SeExiste var with a value from the record?1 Dim UserIDParameters As New Parameter
2
3 UserIDParameters.Name = "ProdUserID"
4
5 UserIDParameters.DefaultValue = "samurai"
6
7 Dim LoginSource As New SqlDataSource()
8
9 LoginSource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString()
10
11 LoginSource.SelectCommandType = SqlDataSourceCommandType.Text
12
13 LoginSource.SelectCommand = "SELECT FROM aspnet_Users (FirstTime) VALUES (@UserIDParameters) "
14
15 Dim SeExiste As Boolean
16
17 SeExiste = LoginSource.SelectParameters("FirstTime").DefaultValue
I'm a newbye and despite this simple thing that in normal ASP is very easy to do!!! Please help me!Thanks in advance!
View 4 Replies
View Related
