Confusion In AWE
Apr 26, 2007suppose i have enabled only AWE option in sql server but i didn't add 3
GB switch in boot.ini. in this case what will be the effect on physical memory?
ADVERTISEMENT
DTS Confusion...
Nov 28, 2001I'm using DTS for the first time and am having difficulty understanding some of its usage.
In essence, I have 70 tables which are on an ORACLE database. I've created the schema on SQLServer from an ERWin model. I now wish to use DTS to import the data into SQLServer. Of course there are referential integrity constraints. The DTS 'import wizard' seems oblivious of these and carries out the import in alphabetical order!
I'm now trying the DTS designer (which I don't fully understand yet). It appears that I have to create a workflow which moves the workflow from table to table in the order dictated by RI - this is not a trivial task! (I want point and click!!).
Are there any shortcuts I can take to have DTS automatically arrange the workflow in the correct order? Can I do it programaticaly? I don't want to have to fiddle with 140 'workflow' and 'success' links.
Cheers.
SQLExpress ASP.NET Confusion
Jul 19, 2006Hello,I am just getting reaquainted with programming and all. I work in GIS mapping and am having diffilculty using a ASP.net internet Map site and adding records to a needed database.I would like to be able to add records to a linked SQLExpress table when the user clicks on a map location. I adapted the following code snippet from an example that does what I would like only using an .mdb file.CODE: // create connection to database var connection; connection = Server.CreateObject("ADODB.Connection"); connection.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OutdoorsExp;Data Source=NOLOGOSQLEXPRESS;"); status = "Can't access 'FishSQL' table."; // create recordset var recordset; recordset = Server.CreateObject("ADODB.Recordset"); recordset.CursorType = 1 recordset.LockType = 3; recordset.Open("dbo.FishTable", connection); status = "Can't append new record."; // append record for clicked location recordset.AddNew(); recordset.Fields("LongitudeI").Value = xco; recordset.Fields("LatitudeI").Value = yco; recordset.Update(); recordset.Close(); // release the .mdb file connection.Close();However, this does not add anything to the SQLEx table. What am I missing? I am thinking that it might have something to with the primary key.I had a table with way more columns then just lat/longs (as above) but only coded it to add data to the latitude and longitude fields. Nothing got added of course. Would have to add data to every field that doesnt allow nulls.How do I generate a random, unique ID for a primary key on the fly and for example add it to the appropriate column.Thanks you in adavnce for any insights.
View 1 Replies View RelatedProc_problem Confusion
Nov 3, 2006hi
i'm trying to write a stored_proc which involves 3 tables. Product, Orders and OrderDetails
i want to return products that haven't sold for a particular month. So these products aren't in the OrderDetails table. When i try and run this i get some really random results. when i change the Orders.OrderDate value to different months, i get the same result when i shouldnt. but then when i add the Orders.OrderDate row to the select statement, i get 400 results and i dont have that many products. the extended amounts are just for my datagrid
ALTER PROCEDURE proc_Report_NoSales_Septasset nocount onSELECT Product.productID, Product.Title, Product.QtyOnHand, Product.Category, 0 as ExtendedAmount, Product.BuyPrice, 0 as ExtendedAmount2 from Orders, OrderDetails, Productwhere Orders.OrderDate > '2006-09-01' and Orders.OrderDate < '2006-10-01'and Orders.OrderID = OrderDetails.OrderID and Product.ProductID not in (Select distinct OrderDetails.ProductID from OrderDetails)/* Product.ProductID NOT EQUAL to OrderDetails.productID */and Product.productID <> OrderDetails.productIDGROUP BY Title, BuyPrice, Category, Product.ProductID, QtyOnHandORDER by TitleRETURN
any ideas
cheers!!!
Very Simple Confusion
Nov 7, 2006hay friends
scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,,
so plz tell me ho to do it.
By using data set ,,data table or what to use for this and how....
wll be waiting for ur coordination
Instance Confusion
Jul 26, 2007I have built an application in web developer that has a sql data base in the App_Data
folder. It works locally fine.
I have uploaded it to my web host client successfully and it all displays. The web host is enabled
for asp.net. But the sql data base is not working....no information is getting from the web application into
my instance of sql in App_Data.
My web host has given me an e:/webs/mywebsitename.com/data/nameofdb.mdf
to alter in my web config connection string which I have done, but still not working.
I have also tried every version of this in connection string without success
Im just confused about the e: part................how is that going to get to my App_Data?
Can someone give me a quick overview of what the problem might be
richard
DateTime Confusion.
Oct 25, 2007Hi All,I'm stumped. I've got a stored procedure with a number of input parameters, and working fine.I added two extra input parameters, FromDate datetime, ToDate datetime. I have not even included these in the SQL yet and just tried to execute the stored proc alone with manually inputting paramters, but I keep getting error: @FromDate: this input parameter cannot be converted.That surely means a formatting issue, so I copy and pasted a value directly from the database into this parameter field, and still get the same error. I've tried various formats, with single and double quotes, and without. But just dont know?And even when I populate these parameters in my code and call the stored proc, it returns no results either, even though I haven't included these new date paramters in the SQL select, so that means it was in error and no doubt a formatting issue on those date fields.format I used was: 17/07/2007 00:00:00 I tried to populate the parameters via the code as follows: Dim dt As DateTime DateFromTB1.Text = DateTime.Now DateTime.TryParse(DateFromTB1.Text, dt) SqlDataSource1.SelectParameters("FromDate").DefaultValue = dt I know there is an extra step for now (DateFromTB1.Text = DateTime.Now), this step will fall away and just parse the textbox.text field as entered by the user.Any help appreciated, thanks.
View 4 Replies View RelatedMy ISNULL Confusion
May 1, 2008I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
Rex
Connection Confusion
Apr 27, 2005When I try to run my application on our server I cannot connect to the SQL Server that's on the same machine (a named instance). I get the SqlException:Login failed for ("null") user. Not a trusted SQL Server connection. I CAN however, connect to this database from the IDE on my laptop--the app works just fine.
I have reconfigured the IIS, the default site and the directory for the app: I turned off anonymous access, turned it on, changed users, changed passwords, etc. I have done the same for the database, added users, changed permissions, added permissions, etc., you get the picture...none of it worked. (I changed the connection string to those users/pwds)
I saw on an MS site that this error can occur under heavy loads...but that's not the case here.
Please help! Thanks!!
Locking Confusion
Jun 15, 2000In Query Analyzer I have 2 connections active. In the first connection I have the following SQL Insert statement:
Begin Tran
Update Account
Set SomeField = 0
Where CustomerId >= 1000 And CustomerId <= 1101
-----------------
In the second connection I have the following SQL Insert statement
Begin Tran
Insert Into Account (Account,AccountName,AccountTypeRowId,CustomerId,U serId)
Values('MMM-989', 'Test Account', 1, 1098,207 )
-----------------
The query in the second connection will hang waiting for the query in the first connection to finish. The query in the first connection touches 767 records (out of a total of 9174).
If I change the Where statement in the first connection to "... And CustomerId <= 1100", the query in the second connection will execute immediately. The query in the first connection is now only touching 645 records instead of 767.
Could someone explain to me why this is happening?
Trans Log Confusion
Nov 5, 2002:confused:
Here's my situation (SQL2K)
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
Domain Confusion?
Feb 24, 2002I can map to a domain to the server where I have a sql Server database from my machine which is in another domin.
However, I cannot register the sql server with enterprise manager from the same machine. I am assuming that it is not a permission problem since I could not get the registration to work logging on as SA or with windows authorization. What should be looked at?
Query Confusion...
Dec 11, 2006Here's an example of two tables I need to get results from.
Client:
Number
1
2
3
Patient:
Number - Client - Hospitalized
50 - 1 - E
51 - 1 - E
52 - 2 - E
53 - 2 - ''
54 - 3 - ''
The only result I should be returning is Client '1' as they would be the only one where ALL patients have an 'E' under patient.hospitalized.
How would I go about running a query such as this?
Any help would be greatly appreciated!
Shawn
Performance Confusion
Jun 29, 2006Hi,
Can any of you folks take a minute to tell me why the results of the attached test are weird. I'm trying to come up with a method of allowing my business layer to interact with the data layer - specifically a nice, easy to maintain, but safe method of passing parameters without adversely impacting performance.
This is only a few of my sketchy ideas so far but I'm stuck because I cannot prove that using OR is slower than a straightforward = because each time I run the test the OR seems to perform better. Even changing the sequence in which the piece work still does alter the OR begin better than =.
And, of course, as that makes no sense whatsoever I cannto really progress.
Any thought would be appreciated. I hope I have altered the script enough that you can just run it straight in and see what I'm talking about. I only introduced the transactions to see if any of that stuff my be interfering.
Thanks.
Mirroring Confusion.
Aug 10, 2006I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?
TIA, ChrisR
Confusion About Indexes
Apr 10, 2008Hi guys,
Is it possible for a column in a table to have a clustered index & the same column to be a part of a non clustered covering index.
Please try this one.I am confused
Create table test
(
id int primary key,
name varchar(40),
age int
)
CREATE NONCLUSTERED INDEX testindex ON test (
id,
name,
age
)
sp_help test
After I do sp_help test it shows two indexes created, one on id & the other one on id,name& age as a part of covering index.
Dateadd Confusion
Aug 10, 2005I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.
No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.
One thing I don't understand is- how come I can't use an equal sign in my syntax?
For instance,
SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )
won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.
Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?
select dateadd(wk, -2, dbo.weekcommence(date_added))from clients
To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function).
The results are the last two MONTHS, not weeks. What am I misinterpreting?
I can also use this:
SELECT dbo.weekcommence(date_added) FROM clients
WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))
and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.
To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.
It seems like it should be straightforward, but I'm obviously missing something big. Any help?
Datediff Confusion
Jun 22, 2007I have a DELETE statement that is taking ages. I was asked to look into it and immediately noticed the use of function on the column. So its doing a scan even though there is an index on the datecolumn.
HEre's the original statement:
Delete someTable Where datediff(year, DateColumn, getdate()) > 1
I have rewritten it as
Delete someTable Where DateColumn < dateadd(year, -1, getdate())
When I did a count(*) the counts from previous query didnt match with the new one. Did i miss something?
Perhaps a fresh pair of eyes might help me
thanks,
don.
Login Confusion
Jul 23, 2005Hi,I am Just beginning to get to grips with SQL Server v 7 security on a standalone win 98 PC.I have created a login which only has access to the pubs database and when Itry and connect to any other database (while using this login) I amcorrectly told that I don't have access. However, Enterprise Manager doesnot request a login and therefore the log in seems to grant full access toevery database.How can I get Enterprise Manager to request a log in?Thanks.
View 2 Replies View RelatedConfusion Over ANY Keyword
Jul 23, 2005I am studying for the MSCE/MCDBA exam 70-229. In the book I am using("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000Database Design and Implementation Exam 70-229, Second Edition") I amlooking at the section on the ANY/ALL keyword.<QUOTE>USE PubsSELECT TitleFROM TitlesWHERE Advance > ANY(SELECT AdvanceFROM Publishers INNER JOIN TitlesON Titles.Pub_id = Publishers.Pub_idAND Pub_name = 'Algodata Infosystems')This statement finds the titles that received an advance larger thanthe minimum advance amount paid by Algodata Infosystems (which, in thiscase, is $5,000). The WHERE clause in the outer SELECT statementcontains a subquery that uses a join to retrieve advance amounts forAlgodata Infosystems. The minimum advanceamount is then used to determine which titles to retrieve from theTitles table.</QUOTE>I don't understand why this references the "minimum advance". If yourun the subquery on its own, it returns the following values:5000.00005000.00005000.00007000.00008000.0000NULL[color=blue]>From my limited understanding, the "ANY" keyword applies to at least[/color]one value, but which one? How is this determined?Any help gratefully received.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 4 Replies View RelatedMS SQL 2005 And PDC Confusion
Nov 3, 2006hello everyone, I am kind of confused with MS SQL 2005. I have friends who have been developing with MS SQL. They told me that in order to have a MS SQL Server, one need to have a PDC.
This is really a problem since I am behind a Unix box acting as a firewall/gateway. AFAIK, to install a PDC means that DHCP comes with it and DNS. This is really a problem...
Is it possible to have a MS SQL server without having to create a PDC? If so, how do I connect to my database on an MS SQL server. I develop in VB. I would appreciate any help/tips... TIA :D
Begginer Confusion
Jul 7, 2007Hey everyone,
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks
Security Confusion.....!
May 8, 2008Hi All,
one thing always bothers me in passwords with SQL Server. See i created a User with username and password for Crystal Report user to access READ ONLY Data. and permission are set accordingly for this Login at SQL Server end for database. now i have to tell the user the username and password so he or she can create ODBC connection. in such case, what is purpose of password here. no matter we keep the password simple or hard to remember or guess, it won't matter. i can not even enforce user to change the password at next login as Crystal report gives no UI to do that.
what is exact philosphy.
I understand how we can set permissions for users for accessing data and what is the purpose of enforcing rules on password like Expiration or next login change password etc, but how will it work in above senario......!
Many Thanks,
Confusion Between 2 Queries
Jul 30, 2007Hi
I was hoping that someone will be able to explain to me what the difference is between these two queries, because i get different results when i run them.
I'm trying to count the distinct unique email addresses
Code Snippet
SELECT DISTINCT Prefix_code, Email
FROM tmpmedschemereal
WHERE email like '%@%'
AND prefix_Code = 'UNW'
GROUP BY prefix_Code, email
return 1939 rows
Code Snippet
SELECT DISTINCT Prefix_code, COUNT(Email)
FROM tmpmedschemereal
WHERE email like '%@%'
AND prefix_Code = 'UNW'
GROUP BY prefix_Code, email
returns 2006 rows
Why is there a difference, as far as i know they are suppose to return the same amount of rows, but there must be something that i misunderstand.
Will someone please be able to clarify for me what it is?
WaitAny-WaitAll Confusion
Jul 24, 2007Hello,
When using Asynchronous processing with ADO.NET 2.0, I use BeginExecuteNonQuery() and the EndExecuteNonQuery();.
I would like to know what is the difference between the above two methods when working on a web page.
In case of the WaitAny, we will wait for any operation that finishes and then update some UI, in the other one, we wait until all operations are finished, and then update the UI.
But in both cases, the UI that was updated through code, will not be shown on the page, until all operations have finished.
I did my testing here and noticed this fact, should the WaitAny whenever it is finished, it updates the UI and the UI refreshes for the user? Am I missing something in here?
Thanks
Simple SQL Command Confusion
Jan 19, 2008Hey everyone,
I have been stuck on this for a few hours now and I think I am missing something simple.
I have 2 tables. A Groups table and a GroupMembership table.
Groups Table
GroupID (PK)
GroupName
GroupDescription
CreatedBy
GroupMembership Table
GMembershipID (PK)
GroupID (FK)
UserID(FK)
I want to pull all the GroupNames, GroupDescriptions, and CreatedBy for all the groups the user is NOT in.
Is this really simple and I am just missing it after hours of frustration? Or did I just set the database up badly?
Thanks,
Chris
Data Type Confusion...
May 22, 2004Which Datatype should i choose for Field "AGE"
Do i select "Int", "Char"?
SQL Server...in Table Desgin, The length for Int only can be max of 4 ?
Is this mean it can only store 4 digit ?
What if i want to store numbers but more then 4 digit ?
What will be the appropriate datatype to store numbers?
Enterprise Manager Confusion
Nov 3, 2005Ok i know that the main reason for the error that contains System.Data.OleDb.OleDbException: Login failed for user 'BEDROOMASPNET'.is because i do not have the ASPNET registered. From what i have been told i need Enterprise Manager.I HAVE NO CLUE WHERE TO GET IT OR HOW TO CONFIGURE IT OR ANYTHING OF ITsorry for the caps.I have search on here and google and nothing any helpthanks
View 3 Replies View RelatedSeroius Confusion With Databases
Mar 20, 2006I have been having trouble logging on to my site. thsi seems to be intermittant. Basically if a create a user through my registration form everything is fine. However, when i close VWD and shut down and then return i cant login.
I checked the database in VWD and the recently added user is there along with my admin user. I open the web site configuration tool and it suggests that there is no users in the database, and that it hasnt been configured to use a membership provider (which it has). I choose the default provider and it adds an extra connection to my web config.
I am very confused becuase when drag a data control on to my development window in VWD and connect via some of the connection strings listed(one i have already used), it appears that my ASPNETDB database is different to the one visable in the database explorer in VWD. I have added extra feilds and they dont show up when i try to build a new sql statment using VWD.
Where is the:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF database
It is stored in my ap_data folder. Would have anything to do with the fact that my website is stored in inetpub/wwwroot/websites/mywebsite folder. Confused about the project thing as well.
Cheers
SQL2000 IndexRebuild Confusion...
Feb 14, 2008Hi:
I think SQL2000 does not allow on-line indexRebuild without single user status.
1. But from one server with maintenance plan, it looks like it could to it without knocking out all users connections.
2. Also, I doubt its result: did it really dropped and recreated indexes?
-------------------------------------------------------
Job 'DB_Weekly_IndexRebuild' : Step 1, 'Step 1' : Began Executing 2008-02-07 01:00:02
output
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039Copyright (C) Microsoft Corporation, 1995 - 1998Logged on to SQL Server 'ZYNXDB2' as 'NT AUTHORITYSYSTEM'
Starting maintenance plan 'DB_Maintain_Weekly_IndexRebuild' on 2/7/2008 1:00:02 AM [3] Database CatalogAndVocabularyImport: Index Rebuild (leaving 10%% free space)... Rebuilding indexes for table 'Tbl_ABC'
------------------------------------------------------
thanks
David
SQL 2000 Connection Confusion
Feb 25, 2004Does SQL server 2000 always use TCP/IP and port 1433 to make connections?
More details related to the above question:
I've got MSSQL server 2000 running and we have a firewall. When I'm testing locally on the server I can telnet to localhost on 1433 and connect. However, when I'm testing remotely, I cannot connect on port 1433. However I can make a Query Analyzer connection remotely. !?!?!???
The reason why I'm asking this is I'm trying to set up a Linux connection to MSSQL using FreeTDS.
Thanks,
cudabean
Select Query Confusion
Apr 14, 2008The problem in short:
I have a select where I need to group by one column and sort by another column on the same table, without including the sort column in the result set.
The gorey details:
Allow me to illustrate. Table UserContent has three columns: Id (PK), UserId (FK), Rating (int), and DateCreated (datetime). From this table I need to select the top 3 UserId’s from records with the highest Rating values. The result set needs to also be distinct by UserId. So if the same user has the top 3 Ratings, he should only be shown once in the result set.
Additionally, if two or more of the top rating values are equal, it needs to select the one that was created first, hence the sorting on DateCreated.
I’m using SQL Server 2005. I’ve tried selecting the max(rating) and group by UserId, but then I can’t find any way to apply the sort.
Thanks!
Record Count Confusion
May 30, 2007I have been assigned to fix a script which i did not write and that is not in SQL, but calls to SQL to get it's results.
It reads from a payroll table in SQL with 943 total records (of which about 280 are active employees).
When i run the script, i receive a series of errors listing the field that the error is in (ie: Badge or PayRule) and the record # causing the error.
My problem is that the 1st record # that returns an error is #946. I get a total of 45 records that generate errors of this sort. (ie: #946-957 Badge, 1154-1159 Badge, 1482-1497 PayRule, 1562-1568 Badge, 2083-2086 PayRule).
IF i can figure out which employee those records belong to I will have a much better chance of finding the cause of the errors and correcting them.
When it gives a record #, i always thought it was the row in the table that was referred to, but since the record #'s i am receiving are larger than the number of rows in the table, i am utterly confused!
So...i guess my question is, how do I count the records if not by row?
thanks in advance, and i hope this makes sense!
ed c