Noob: Why does this Query work?

I'm wondering how/why this query works. Trying to get my head wrapped
around SQL. Basically the Query deletes from the Import table all
records that are already in FooStrings so that when I do an insert from
the FooStringsImport table into the FooStrings table, then I won't get
primary key violations.

DELETE FROM FooStringsImport
WHERE EXISTS
(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

It seems to work fine, but I'm wondering about how the EXISTS keyword
works.

(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
This part is going to return only records from FooStrings correct? Or
does it do a cartesian product since I've specified more than one table
in the WHERE statement?

I wonder if it only returns records in FooStrings, then I don't see how
a record from FooStringsImport would "EXISTS" in the records returned
from FooStrings.

The reason I wondered about the cartesian product is because, if only
FooStrings is specified in the FROM part of the SELECT statement, then
I was thinking it is only going to return FooString records. These
records would then be returned by the select statement to the WHERE
EXISTS, which would look for FooStringImport records, but would find
none because the select statement only returned FooString records.

I'm guessing maybe because it has to do a cartesian product to evaluate
the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
ALL the fields, and not just those in FooStrings.

FooStrings and FooStringsImport are identically structured tables,
where the FooKey is set as the primary key in each table:

CREATE TABLE [dbo].[FooStrings] (
[FooKey] [bigint] NOT NULL ,
[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks in advance. I'm so appreciative of the help I've gotten here,
as I've been able to write several very useful queries on my own now
after everyones help and plus lots of reading on my own.On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

> I'm guessing maybe because it has to do a cartesian product to evaluate
> the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
> ALL the fields, and not just those in FooStrings.

Close. The "WHERE EXISTS" is processed such that *no* columns will be
returned by the SELECT * inside it. Instead, it just notes whether any row
was returned and assigns true or false. You could get the same behavior by
replacing SELECT * with SELECT 1, or
SQRT(LEN(FooStringsImport.FooKey))*3.1415926535, or even SELECT NULL.
All of them will do exactly the same thing: completely ignore what comes
after the word SELECT.

Conceptually, the query processor is doing a separate "SELECT *" statement
for each and every row in FooStrings. If that separate statement returns a
row, it deletes the corresponding row from FooStrings; if it doesn't, then
it doesn't.

In actuality, the query optimizer doesn't do that. It rewrites the query as
if it were a JOIN (aka cartesian product, as you stated) and uses that to
decide which rows to delete from FooStringsImport.|||It's called a Correlated Subquery. Theoretically you can assume that the
subquery is executed once for every row in the main (outer) part of the
statement. The correlation part is the reference to the outer table
(FooStringsImport.FooKey). In other words EXISTS returns true for any
particular row in FooStringsImport if there is at least one row in
FooStrings that matches FooStringsImport.FooKey.

This is a Selection operation, not a Join and a DELETE statement rather than
a query, so I don't see that it helps much to think in terms of a Cartesian
Product. In fact SQL Server may use join operations to execute statements
involving subqueries but the server is not going to join every row to every
row before deciding which rows to delete.

See also Joe Celko's narrative on how queries work:
http://www.google.co.uk/groups?selm...ooglegroups.com

By the way, NOT EXISTS is also possible. So rather than do a DELETE followed
by an INSERT why not just filter the INSERT statement in a similar way:

INSERT INTO FooStrings (...)
SELECT ...
FROM FooStringsImport
WHERE NOT EXISTS
(SELECT *
FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

--
David Portas
SQL Server MVP
--|||On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

>I'm wondering how/why this query works. Trying to get my head wrapped
>around SQL. Basically the Query deletes from the Import table all
>records that are already in FooStrings so that when I do an insert from
>the FooStringsImport table into the FooStrings table, then I won't get
>primary key violations.
>DELETE FROM FooStringsImport
>WHERE EXISTS
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>It seems to work fine, but I'm wondering about how the EXISTS keyword
>works.
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>This part is going to return only records from FooStrings correct? Or
>does it do a cartesian product since I've specified more than one table
>in the WHERE statement?
(snip)

Hi Shumaker,

This is called a correlated subquery. Correlated, because the
"FooStringsImport.FooKey" correlates it to the part of the query outside
the subquery.

The way that this will be executed (in theory - the optimizer usually has
some nifty trcks to get the same result faster, but the slow version is
easier to understand):

- For the DELETE statement, start with the first row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that first row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the first row of FooStringImport; if it's false,
retain it.
- Move to the second row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that second row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the second row of FooStringImport; if it's false,
retain it.
- Move to the third row of FooStringsImport;
(etc etc etc -- until all rows of FooStringImport have been processed).

(Note - the terms "first", "second", etc are used very loosely here, as a
table has no intrinsic order)

The main difference between correlated and non-correlated subqueries, is
that a non-correlated subquery only has to be evaluated once, and the
result set can be retained and reused during the complete query
evaluation, whereas ta correlated subquery has to be recomputed for each
row in the "surrounding" layer of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks everyone! This is great info.sql

Noob: SQLEXPRESS_ADV Full-Text Disabled

Some background:
I initially installed SQLEXPRESS (without advanced services) as my default
instance. I read that Full-Text was not supported in that version, so I
disabled the Service, and downloaded and installed Advanced as a named
instance.
The "Use full-text indexing" checkbox on the Files tab of the Database
Properties dialog is disabled.
Attempting to create a new database is no different, the same disabled
checkbox on the Files tab.
Attempting to use SQL results in the error:
CREATE FULLTEXT CATALOG catFullText
Msg 7609, Level 17, State 100, Line 1
Full-Text Search is not installed, or a full-text component cannot be
loaded.
Any ideas? Should I completely uninstall everything and start over
correctly?
~Todd
Hi Todd, can you check to see if the MSFETSQL is installed and running?
Check it from a command prompt by typing net start and look for something
like this
SQL Server FullText Search (SQLEXPRESS)
Where SQLExpress is the name of your SQL Express instance.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Todd Bollack" <webdevtool@.gmail.com> wrote in message
news:OWiBUTUCHHA.4292@.TK2MSFTNGP06.phx.gbl...
> Some background:
> I initially installed SQLEXPRESS (without advanced services) as my default
> instance. I read that Full-Text was not supported in that version, so I
> disabled the Service, and downloaded and installed Advanced as a named
> instance.
> The "Use full-text indexing" checkbox on the Files tab of the Database
> Properties dialog is disabled.
> Attempting to create a new database is no different, the same disabled
> checkbox on the Files tab.
> Attempting to use SQL results in the error:
> CREATE FULLTEXT CATALOG catFullText
> Msg 7609, Level 17, State 100, Line 1
> Full-Text Search is not installed, or a full-text component cannot be
> loaded.
>
> Any ideas? Should I completely uninstall everything and start over
> correctly?
> ~Todd
>

noob: How do i run pl/sql on ms sql?

Hi experts,

I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.

I created a new stored procedure within the built-in Northwind database. I pasted the following inside:

--------------------
declare

vname Employees.firstname%TYPE;

begin

SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';

dbms_output.put_line ('Name is ' || vname);

EXCEPTION

when NO_DATA_FOUND then
dbms_output.put_line ('no data found');

when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');

END;

--------------------

I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option

Any idea?

Thanks..You've got some serious reading to do...there is a major methodolgy switch you'll have to understand (not to mention sytax) between the two...

Oracle has a lot of nice "built in" features, and at the same time is extremely painful...

For example, ylucan not use %TYPE...

YOu must explicitlety declare EVERYTHING...

This is how you write what your example is trying to do...

CREATE PROC mySproc99
AS

BEGIN

DECLARE @.vname varchar(40), @.Rowcount int, @.Error int

SELECT @.vname = Lastname
FROM Employees
WHERE firstname = 'Nancy'

SELECT @.RowCount = @.@.ROWCOUNT, @.Error = @.@.Error

IF @.Error <> 0
BEGIN
PRINT 'Error Condition ' + CONVERT(varchar(5),@.Error)
Return @.Error
END

IF @.RowCount <> 1
BEGIN
PRINT 'Multiple Rows Found Error'
Return 2
END

IF @.RowCount = 1
BEGIN
PRINT 'Name is '+ @.vname
Return 0
END
END

GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

Noob to VWD 2005 - Insert from textbox into SQL DB

I have followed all the tutorials and I think I have a pretty good grasp on how to get info from the SQL database running on my machine in the App Data directory. My question is simple and probably the answer is nested in the 148 pages of this very informative forum.

How do I take the 15 textbox fields worth of data and insert them into the SQL db? I am pretty familiar with general sql concepts (mysql, mssql, postgresql), but I have only been in this IDE for about a week now and I need to get my project up and running quickly. I am just unfamiliar with VB in general, but I do understand the object.method concept.

I gathered that I need to make the insert statement part of the button click "event". I cannot find any good "starter" documentation on getting this process to work. I understand the grid and form and detail view really well and have tested my 2nd app with success, but it requires the db to be populated by a web front end. I have read a few MSDN links but they are not helping much, they just keep linking me back to grid and detal and form views.

Help? Please? Pretty Please?

- Chris

roninuta:

I have followed all the tutorials and I think I have a pretty good grasp on how to get info from the SQL database running on my machine in the App Data directory. My question is simple and probably the answer is nested in the 148 pages of this very informative forum.

How do I take the 15 textbox fields worth of data and insert them into the SQL db? I am pretty familiar with general sql concepts (mysql, mssql, postgresql), but I have only been in this IDE for about a week now and I need to get my project up and running quickly. I am just unfamiliar with VB in general, but I do understand the object.method concept.

I gathered that I need to make the insert statement part of the button click "event". I cannot find any good "starter" documentation on getting this process to work. I understand the grid and form and detail view really well and have tested my 2nd app with success, but it requires the db to be populated by a web front end. I have read a few MSDN links but they are not helping much, they just keep linking me back to grid and detal and form views.

Help? Please? Pretty Please?

- Chris

This link...

http://www.WebLogicArts.com/DemoSqlServerDatabase01.aspx

...is a working sample with source code that shows one simple way to CreateOne/ UpdateOne/ DeleteOne/ RetrieveOne/ RetrieveAll using ASP.NET and SQL Sever 2000.

HTH.

Thank you.

-- Mark Kamoski

|||

Ok, there are two very simple ways you can do this.

1) Using the SQLDataSource object and after creating the connection using the GUI. You can find the SqlDataSource control in the toolbox. Although easy to use I don't like it too much, prefer writing SLQ statements on my own allows for more flexibility. However, to use the SqlDataSource object you are forced to create an SQL statement using the GUI, but this can be countermanded. So what I do is use the SQLDataSource object's SelectCommand and use its SelectCommand method to write my own SQL statement. Basically SQLDataSource1.SelectCommand = "Select * from whatever". To include textboxes, like in a search your SQL statement should look like this

"Select * from Table WHERE Table.Column = '" & textbox1.text & "'"

You can extrapolate to Insert and Update.

2) Another way of using SQL in VWD2005E is to call ADODB objects. I find this the best method to do inserts and such. First you have to make a reference to the DLL, to do this you go to Website->Add Reference click on the COM tab and find Microsoft ActiveX Data Objects, pick one and click OK. To use ADODB you first declare it then set the connection string (I usually copy it from the SQLDataSource Object), and execute your sql command.

Dim Conn as ADODB.Connection

Conn.ConnectionString = "Provider..."

Conn.Execute(InsertCommand)

Holler at me for any clarifications

|||I forgot, for 2 after Dim Conn as ADODB.Connection you have to put Conn = new ADODB.Connection|||You can try this one at your button submit click event using text1 as your control.

Dim datasourceAsNew SqlDataSource()

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ToString()

datasource.InsertCommandType = SqlDataSourceCommandType.Text

datasource.InsertCommand ="INSERT INTO YourTable (YourTableField) VALUES (@.YourTableField)"

datasource.InsertParameters.Add(

"YourTableField", Text1.Text)

|||You can try this one at your button submit click event using text1 as your control.

Dim datasourceAsNew SqlDataSource()

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ToString()

datasource.InsertCommandType = SqlDataSourceCommandType.Text

datasource.InsertCommand ="INSERT INTO YourTable (YourTableField) VALUES (@.YourTableField)"

datasource.InsertParameters.Add(

"YourTableField", Text1.Text)

|||

this last example was awesome. It was implistic and really helped. I understand much more now. I new that I was going to have to use the parameters.add functionality. I think I am messing up on my connection string though and it is probably just not set correctly. I get an unhandled exception here is the error message:

System.NullReferenceException was unhandled by user code
Message="Object reference not set to an instance of an object."
Source="App_Web_1cnoj4u7"
StackTrace:
at _Default.SendForm_Click(Object sender, EventArgs e) in D:\Documents and Settings\ronin\My Documents\Visual Studio 2005\WebSites\ADRequest\Default.aspx.vb:line 20
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Here is line 20 - the connection string:

datasource.ConnectionString = ConfigurationManager.ConnectionStrings("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\adrequest.mdf;Integrated Security=True;User Instance=True").ToString()

I am using a built in SQLDB file that comes with VWD2005E, and the file is called adrequest.mdf, what am I missing here.

TIA,

Chris

|||

You are wrong with declaring with your connection string. Your connection string name must be like in my part:

phiwugdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("phiwugConnectionString1").ToString()

To find the name of your connection string, try to do this, add a blank web form on your project, go to database explorer and go to the table of your database, click the table and drag and drop this to the blank web form. You will see that it will create a table on your blank web form and a control name SqlDataSource - SqlDataSource1. Click it then click the arrow next to it and you will see Configure Data Source and click it. Next a wizard will appear. The connection string name that will appear on the drop down list box is the connection string name you will write on it.

Be sure to mark as answer on the post you think that answers your question so that the poster will have credit for it. Don't hesitate to inquire.

Best regards,

|||

This may be a silly question but given this code:

Dim datasource As New SqlDataSource()
datasource.ConnectionString = ConfigurationManager.ConnectionStrings("connection2").ToString()
datasource.InsertCommandType = SqlDataSourceCommandType.Text
datasource.InsertCommand = "INSERT INTO ticket (sr) VALUES (@.srnum)"
datasource.InsertParameters.Add("srnum", TextBoxSRNum.Text)

is the data actually being posted to the DB or is the insert statement just being setup? Do I need to actually execute the statement through another line?

TIA - Chris

|||

Nevermind

I just added datasurce.Insert() to the end of the event - that fixed it - thanks everyone for helping out!

- Chris

|||

You can even add this one and put your datasource.insert() on a structured error handling procedure:

Dim

rowsaffectedAsIntegerTry

rowsaffected = datasource.Insert()

Catch exAs Exception'TODO: Probably write a windows error log

Server.Transfer(

"error.aspx") <--create a web form for error messages/siteFinally

datasource =

Nothing

EndTry

If rowsaffected <> 1Then

Server.Transfer(

"error.aspx") <--create a web form for error messages/siteElse

Server.Transfer(

"success.aspx") <--create a web form for success messages/site

EndIf

noob to SSIS package configure and deploy

Hi there

I am fairly new to SSIS, I got my package running fine in my development environment with the connection managers (connecting to 3 databases on the same server, with username and password, not Integrated security) and all.

The problem comes in when i want to deploy my package or execute it outside of the development environment.

When I execute the .dtsx file in my project it brings up the Execute Package Utility and I just click Execute, thinking it should work, but not, any ideas?

So I read some of the forums and got looking into the configuration file, suppose that it does not carry the connection manager settings thru, but dam that still doesn't work for me?

The whole idea is that I will be executing the package from my C# code, so does my package have to be just on the server in a folder or does it have to be installed in the db? which is best?

Hope anyone can help me!!

Thanks
Jamie

If the error you are getting is a failure to connect to the database, the problem is in the password not being stored in the package. If you search the forum for connection strings and configurations, you'll see a number of different solutions to this.

If that's not the problem, please include the error message that you are receiving.

noob syntax question

What am I doing wrong?:
DELETE FROM tblmainacs2 WHERE tblmainacs2.IDnum = Meter.id

I get this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Meter.id" could not be bound.

Is Meter a table? If so you do contain an explicit reference to it in the DELETE statement in the FROM clause or part of subquery for example. You can write it like below assuming you want to perform a join:

delete from tblmainacs2

where IDnum in (select id from Meter)

|||Thank you. That query did exaclty what I wanted. Could someone show me some different queries that do exactly the same thing, just for learning purposes. Thanks in advancesql

NooB Scripting Question How to use mailto

I am a noob to sql and asp programming. I am working on a db for the
gaming squad I am a member of. One of the pages I created is a roster
list of all the squad members. Part of this roster is listing each
member's email address. What several people have asked of me is to
make it so the email addresses can be clicked on to open their email
programs, just as html allows the mailto function to work.

Here is a copy of the coding I am currently using:
<td align="center"><font face="Arial"
color="#C0C0C0"><%=rst("email")%></font></td>
This will list the email address of the person, but how do I make it
so the line will function as a mailto command?
The page is saved as an .asp.

Thanks for any advice!

Davedavestrike (davestrike@.nventure.com) writes:
> Here is a copy of the coding I am currently using:
><td align="center"><font face="Arial"
> color="#C0C0C0"><%=rst("email")%></font></td>
> This will list the email address of the person, but how do I make it
> so the line will function as a mailto command?
> The page is saved as an .asp.

Actually, I don't have the slightest idea, nor do I feel to compelled
to have one, since this is an SQL Server forum, and not related to ASP.

But they say ASP has something to do with HTML, in which case I would
try with:

<td align="center"><font face="Arial" color="#C0C0C0">
<A HEF="mailto:%=rst("email")%"><%=rst("email")%></A>
</font></td
But if that does not work (and it probably doesn't) you should look
for an ASP forum.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Noob questions about Reporting Services

Sorry for the noobish questions, but I just loaded Reporting Services and am trying to learn about it.

1. Is there a way to manipulate the tool bar that appears with the report? The one that has the pages, percent view, find/next, format/export and refresh. I can't seem to find any properties to set for it. For example, when I created reports with the evaluation copy, a print button would be on the toolbar, but with the full version, the reports I create don't have the print button.

2. For report parameters, is there a way to have a text box that users can input into rather than using dropdowns?

3. Does using the refresh button on the report toolbar refresh the dataset used for the report? Or does the user have to exit the report and have it recreated?

4. How do I use a parameter in a report title? For example, if I wanted to have a report where the user selected a month for a report and the parameter name was MONTH, I'd want the title of the report to show up as MONTH Report.

Thanks for any help.

Addendum: Also, I've read through some of the threads in here and read references to a client report builder. Is there a separate client report builder or is it just the add-in to Visual Studio?

Hello jwilliams:
I've worked with this RS thing for 4 months. I don't have much experience but I'll try to answer:

1.- No, as far as I know
2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")
3.- Yes, clicking the refresh button is enough (as far as I know)
4.- I'll assume that you want to show the parameter value in a textbox. So, you want to concatenate an arbitray text and the parameter value. You can do this by typing something like this in the textbox:

="The value selected is: " & Parameters!ParameterName.Value

or, in the case you refer:
=Parameters!MONTH.Value & " Report"

I hope this helps.
Regards|||Thanks so much for your reply. Any idea on the client report builder? Is there one, or is there only the add-in for Visual Studio? Also, is there a way to add a column to your report once it's made? I used the wizard to generate my reports and on one, I wanted to add a column, but couldn't find where to do it.

Once again, thanks for your help.|||Well, I've just worked with the report designer, not with the report builder. See, Microsoft has just released some courses for free, and I guess you can find some helpful information there (in the bottom of the page there's a linkt to a free online RS course)

https://www.microsoftelearning.com/sqlserver2005/

Regards|||Thanks. As far as your answer to my 2nd question:

2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")

When I open the Report>Report Parameters and select the Non-Queried option, it brings up a Label and Value table to allow you to set up a drop down box, but I don't see anywhere that allows you just to specify a text box with a prompt for the user to input into. Am I missing something?

Also, I noticed that in another thread where someone is trying to pass a wildcard in a parameter--I'm trying to do that also, but can't figure out how. (I want the user to select a month or all to run the report on). You provided this if statement:

IF @.param = -5
BEGIN
SELECT *
FROM tbl_employee
END
ELSE
BEGIN
SELECT *
FROM tbl_employee
WHERE id_boss = @.param
END

Where does this statement go? In the table data filters property? That's where I put all my parameters for the report.|||OK, about the 2nd answer: just leave the table empty. This forces the user to write a parameter value in a textbox (if you fill the table then a dropdown list will appear in the report)

About the other one: my scenario is the following.
I had a parameterized report. There's only one parameter (called @.id_acm) and the values are taken from a query (in the report>report parameters... menu I choose "From Query" instead of "non-queried" and so a drop down list is shown to the user) The source query for the parameter values looked like this:

SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm

And the query used to get the information for the report (the one I wrote in the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm

I also did NOT specified any default value for the parameter.

When my boss executed the report he noticed that it showed anything until he selected a value from the drop down list. My boss told me that he wanted to see all the information by default, and if he needed information about a specific person he would select the person from the drop down list. Here comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:

SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm

And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:

IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END

Let me explain this: when the user selects "Everyone" the principal query returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In fact, if you have n parameters you'll have to nest n IF statemens and you'll get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.

Noob question: SELECT statement

Hi,
I'm very new to RS. I'm trying to find a single piece of data in a table. Do I need to create a seperate dataset for it or is there some way that I can insert a SELECT statement into a textbox? Maybe a sub query of an existing dataset?
Thanks in advance.
WOW!
To date this has had 111 views and ZERO posts. Can noone help me?
|||No you can't do this. Your select statement must populate a dataset and return the single value if thats what you want. You can also used a stored procedure to return the value into the dataset.

If its a value from an dataset that is already loaded you may beable to select which row in the dataset to take the value from. I am not 100% sure on how to do that one tough.

Hope that helps
Martin

Noob Question: How to get Reporting Services

I'm evaluating using SQL for running the back end of a medium sized business. We're looking at using reporting services to cull information from several dbs, some of which are owned by partner companies who may or may not be running SQL 2005.

What I'm trying to figure out is; can I just go online and download reporting services somewhere for pre 2005 sql installations? Does it cost anything? Browsing the MS download pages has given me no results thus far.

Thanks much

I found my own answer - I can get the functionality I need through the redistributable here: http://www.microsoft.com/downloads/details.aspx?familyid=8A166CAC-758D-45C8-B637-DD7726E61367&displaylang=en#Overview

Thanks

Noob question(s)

I am used to using SQL Server 2000 and am just learning about MSDE 2000.
I'm on XP Pro, latest everything
Using VS .Net 2003 (latest framework, et cetera)
I've installed (correctly) the MSDE Toolkit Release Candidate
I've built a test deployment app (from the readme.htm) and it works just
fine
Now, I've got some (probably stupid) basic questions.
(1)With SQL Server 2000 (which is also installed on my dev machine) I have
had to configure the service to auto start via the service manager. How
does this happen with MSDE? I mean, treating my machine as a test
deployment machine, do I need to trigger its startup or configure it (post
installation) with osql.exe commands>
(2)My instance name given to the setup application was MSDETestInstall and I
wanted to know how can I check for the service being correctly configured
and/or available? (Question 1 may answer this.)
(3)If not answered by question 1, does my application need to startup the
MSDE instance each time it is run (and should also shut it down as the
license stipulates that only my application(s) can make use of this instance
of MSDE)?
(4)Is there a subsection of the 'Books Online' docs that deals specifically
with MSDE because I've been unable to locate it. Perhaps there is
supplementary documentation elsewhere?
Thanks everyone, hope those questions weren't astonishingly stupid (I'd
settle for just plain stupid)
WTH
For #1 and #2 and #3, to auto start SQL, just go into Control Panel |
Administrative Tools | Services and set MSSQLServer (and MSSQLSERVERAgent)
to Automatic. It will start when the computer does. You can also assign it
to a service account. Multiple instances of SQL will show up as
MSSQL$instance name, and you should be able to connect it to as
computer_name\instance_name.
Most of MSDE is SQL 2000 so it will be covered by the books online at
http://www.microsoft.com/sql/downloads/default.asp. The limitations of MSDE
can be found at
http://www.microsoft.com/sql/msde/pr...o/features.asp
and from there you can link around to find licensing information and such.
Good luck.
************************************************** *****************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
************************************************** *****************
"WTH" <spamsucks@.Ih8it.com> wrote in message
news:usOJ4VgMEHA.2244@.tk2msftngp13.phx.gbl...
> I am used to using SQL Server 2000 and am just learning about MSDE 2000.
> I'm on XP Pro, latest everything
> Using VS .Net 2003 (latest framework, et cetera)
> I've installed (correctly) the MSDE Toolkit Release Candidate
> I've built a test deployment app (from the readme.htm) and it works just
> fine
> Now, I've got some (probably stupid) basic questions.
> (1)With SQL Server 2000 (which is also installed on my dev machine) I have
> had to configure the service to auto start via the service manager. How
> does this happen with MSDE? I mean, treating my machine as a test
> deployment machine, do I need to trigger its startup or configure it (post
> installation) with osql.exe commands>
> (2)My instance name given to the setup application was MSDETestInstall and
I
> wanted to know how can I check for the service being correctly configured
> and/or available? (Question 1 may answer this.)
> (3)If not answered by question 1, does my application need to startup the
> MSDE instance each time it is run (and should also shut it down as the
> license stipulates that only my application(s) can make use of this
instance
> of MSDE)?
> (4)Is there a subsection of the 'Books Online' docs that deals
specifically
> with MSDE because I've been unable to locate it. Perhaps there is
> supplementary documentation elsewhere?
> Thanks everyone, hope those questions weren't astonishingly stupid
(I'd
> settle for just plain stupid)
> WTH
>
sql

Noob question regarding "Data Warehouse"

Hi

i'm a total noob with the term "data warehouse"

i have 1 db with 1 big table in it.

i was asked from a guy who has no experience either with data warehousing to build a "data warehouse table - no redundancy"

i haven't got a clue how to do it or does this sentence even has a sence to it.

lots of tools on sql server 2005 yet, i don't know where to go in order to move this thing forward.

help would be appreciated.

Avi. :)

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx about DW operations guide on SQL Server 2000 which can give you more information, for SQL 2005 as of now you can depend upon books online in this case, also this is an question with open answers and unless you have specific need it is hard to pinpoint the solution.

noob question of how to combine two tables from two different servers.

I got two tables from 2 different server A and B.

I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"

I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"

i want to combine these two OLEDB sources with a resultset

"select ID, currencyNo, currencyName, exchangerate from table A , B

where A.currencyNo = B.CurrencyNo"

how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?

Running that kind of query is not something that SSIS is going to do magically for you. You have to ask your DBA to set link servers (assuming they are SQL Server).

Other option is to use 2 OLE DB source and then use a Merge Join transformation to merge both sources in a single output.

Both solutions may impact negatively the performance; but that will be really an issue depending in other factors like volume of data and hardware configuration.

A third option is to break work in 2 pieces; first extract data from each table and load it in a common database and then once both tables are in the same database you can use a single ole db source component.

As you can see you have several options

Rafael Salas

|||Just remember that if using a Merge Join transformation, your inputs need to be sorted on the key. Also, it is best to do the sort in your source SQL if possible, and avoid using the sort transformation in SSIS.

Phil|||

i got problems using the merge join....it say i need to sort the data....

|||That's what I said you needed to do in my previous post.|||Thanks, i solved my problem!!

noob question for msde2000

looks like a noob question but how can i manage msde2000...i can see sql is runnin but how can i create database , attach/detach database, write functions and so on...
thanx alot for the reply :)Originally posted by Schuldiner
looks like a noob question but how can i manage msde2000...i can see sql is runnin but how can i create database , attach/detach database, write functions and so on...

thanx alot for the reply :)

msde does not come with tools like EM or QA but there are some tools available for database management in msde to name a few by microsoft are SQL Server Web Data Administrator SQL Server Web Data Administrator (http://www.microsoft.com/downloads/details.aspx?familyid=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en) and OSQL

for more info in detail check:
http://www.aspfaq.com/show.asp?id=2442|||thank u very much :)

Noob question about SQL Server Express used local and on server.

I have just started looking at using a database with my web site. If I have SQL Server Express with the Advanced Services on my local machine and I use it to make interactive pages, can I use the schema and tables on the actual web site on a remote server? If so does that server need to have SQL Server Express installed too?

A link to a tutorial or reference that goes into some detail would be awsome!

Thanks everyone!

Yes, you will need SQL Express and the Advanced Services installed on the server. Depending on how you have it setup, you should just be able to copy the contents of th App_Data folder over and it should work. This page should provide further information:http://msdn2.microsoft.com/en-us/library/ms165668.aspx

Noob question - Data warehouse

Hi

i'm have the experience of 2 days with the term "data warehouse"

and i was asked to build a "data warehouse table - no redundancy".

i have sql server 2005 64 bit.

windows 2003 server 64 bit.

I built 1 database with 1 big table in it.

yet,i haven't got a clue wheather that previous sentence even has a sence to it (all i got from online reading is cubes..dimensions.. etc..nothing about "a data warehouse table"....).

The guy who asked for that table doesn't have experience in that field also so we might be wasting our time with the wrong terms...

Help would be appreciated. :)

thx.

Hi,

Sounds to me that the guy just used a buzzword without knowing anything about it. Ask him what he really wants to acheive, and I would guess that he'll answer analyse the data more easily. In that case read Sql Server 2005 Analysis Services tutorial. An SSAS database will create the tables it needs for the dimensions of your cubes.
http://msdn2.microsoft.com/en-us/library/ms170208.aspx

By the way there is a contradiction in what you said above:

<quote>
no redundancy ... I built 1 database with 1 big table in it
</quote>

No redundancy means that the database is fully normalized, meaning lots of tables.

Where is your data currently located? Another Sql Server database? If not maybe what he wants is to move all the data to a database.

Good luck,
Charles

sql

Noob question

I've been working with SQL Server for a few years now, but I'm new to this
particular newsgroup (heh, to think I pretty much just used Usenet for
gaming discussions in the past). I hope to do my part to contribute as I
pick up a ton of useful information.
Anyway, I've noticed a couple of people here with similarly cryptic aliases
such as "examnotes". Is there some significance to these
names? Do they have something to do with some sort of text encoding scheme
or something?
Just curious.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin FranklinAre you using a web-based newsreader?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rumbledor" <Rumbledor@.hotspamsuxmail.com> wrote in message
news:Xns96308C9E3F4BRumbledorhotmailcom@.
204.127.199.17...
> I've been working with SQL Server for a few years now, but I'm new to this
> particular newsgroup (heh, to think I pretty much just used Usenet for
> gaming discussions in the past). I hope to do my part to contribute as I
> pick up a ton of useful information.
> Anyway, I've noticed a couple of people here with similarly cryptic
aliases
> such as "examnotes". Is there some significance to these
> names? Do they have something to do with some sort of text encoding scheme
> or something?
> Just curious.
> --
> Rumble
> "Write something worth reading, or do something worth writing."
> -- Benjamin Franklin|||On Wed, 06 Apr 2005 18:49:17 GMT, Rumbledor wrote:

>I've been working with SQL Server for a few years now, but I'm new to this
>particular newsgroup (heh, to think I pretty much just used Usenet for
>gaming discussions in the past). I hope to do my part to contribute as I
>pick up a ton of useful information.
>Anyway, I've noticed a couple of people here with similarly cryptic aliases
>such as "examnotes". Is there some significance to these
>names? Do they have something to do with some sort of text encoding scheme
>or something?
Hi Rumbledor,
I think that your newsreader isn't properly formatting some encoded
string. The first characters of the string you quote are =?Utf-8. UTF-8
is one of the unicode encoding schemes. So I guess that someone posted a
message with the name in UTF-8, but your newsreader didn't recognise it
as such.
FWIW, I've never seen names like that in this group, so either I missed
those messages (quite unlikely), or my news reader does format the UTF-8
encoded names correctly (quite likely).
More information on UTF-8: http://en.wikipedia.org/wiki/UTF-8
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Noob Query Problem -This will be easy for you

This is driving me crazy. I am using a simple ASP table editor to make changes to an Access table. The following command is causing me to get an error:

UPDATE rates Set IRItype='A2', IRIProductName='Stylish', SQfeet='803', Bathrooms=1, IRIunitprice='600',

This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The SQL command is automaticly generated by the ASP script. I do not know if that extra comma is the cause of the error.is that all there was to the UPDATE statement? if so, it produces an error because it ends with a dangling comma

typically, you would have a WHERE clause, unless your intention is to update all rows to those values

so yeah, look into the ASP script|||The extra comma is certainly a problem. Also, make sure SQfeet and IRIunitprice are text values in the table. If they are numeric, then you wouldn't want the single quote surrounding the values.

Noob need help with recursion

I created a table called MyTable with the following fields :

Id int
Name char(200)
Parent int

I also created the following stored procedure named Test, which is supposed to list all records and sub-records.

CREATE PROCEDURE Test
@.Id int
AS

DECLARE @.Name char(200)
DECLARE @.Parent int

DECLARE curLevel CURSOR LOCAL FOR
SELECT * FROM MyTable WHERE Parent = @.Id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Id AS Id, @.Name AS Name, @.Parent AS Parent
EXEC Test @.Id
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
END

CLOSE curLevel
DEALLOCATE curLevel
GO

I added a MxDataGrid and DataSourceControl.
SelectCommand property of the DataSourceControl = EXEC Test 0

When I run the aspx page, it only shows 1 record. I tried to change the parameter to 1, 2, 3 but it always shows only 1 record (not the same tho).

Is there something wrong with the stored procedure ?Ok, I made some modifications to make it work properly but there is some limitations. I have to store the results in a temp table. Can I do something similar but without the temp table ?

(Note : I changed some field/table names)

-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
AS
DELETE FROM TmpNodesAndSubNodes
EXEC GetNodesAndSubNodesRecursive @.NodeId
SELECT * FROM TmpNodesAndSubNodes
GO
-- ----------------------
-- Fill the temp table with the id's of the nodes and it's sub nodes
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodesRecursive
@.NodeId INT
AS
-- Store the node id into the temp table
INSERT INTO TmpNodesAndSubNodes VALUES(@.NodeId)
-- Declare a local cursor to seek in the nodes table
-- and some variables to retreive the values
DECLARE @.CurrentNodeId INT
DECLARE @.CurrentParentNodeId INT
DECLARE curLevel CURSOR LOCAL FOR
 SELECT Id FROM Nodes WHERE ParentNodeId=@.NodeId
-- Fetchs the records and call the recursive method for each node id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
WHILE @.@.FETCH_STATUS = 0
BEGIN
 EXEC GetNodesAndSubNodesRecursive @.CurrentNodeId
 FETCH NEXT FROM curLevel INTO @.CurrentNodeId
END
-- Clean up
CLOSE curLevel
DEALLOCATE curLevel
GO

|||Aaarg :)

I decided to work on a non recursive solution.

Noob here, dumb, but quick question

Ok, I know a LITTLE about SQL 2000. I am just starting to get my feet wet in the area. I know how to install SQL, backup DB's and Restore them. Can even do them over the network now. YAY ME! Anyway, here is my question. I have a production server with 4 RAID arrays. I have one for my OS, one for the Main SQL DB's (heavy transaction DB's), one for my log files, and one that is supposed to be for my temp DB. Well when I installed SQL it asked me for the default data path, which would be where my main SQL db's go. I dont remember it asking me where I want the temp DB to go. How do I change the location of the tempdb.mdf and tempdb.ldf to the drive arrays I want them to go to, even though I have already installed SQL.

Thanks. And sorry ahead of time for the noobiness of the question. I did do a search first, but didnt really see anything that would help me.To change where the data files are located for a given database, just right-click the database and change the Data Files and Transaction Log 'Properties'.

With that said, I don't remember if you can change the data file location for 'system' databases. So you might want to check into that...even so, I wouldn't expect any speed benefit from move the tempdb to a different location.

BTW, good question with some solid background for someone claiming to be noob.

Late,

Alexander|||We use this script for DR to relocate the tempdb to an array large enough to allow it to grow as needed. Change the paths and size to fit your situation, and don't forget to stop and restart the sever after altering the database.


use tempdb
alter database tempdb modify file (name = tempdev,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf', size = 10000 MB)
alter database tempdb modify file (name = templog,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

As for me .. this link is permanently in my IE favourites folder ...