Newbie question - Need count to return multiple values

Hi all,
This is probably a simple question but I still new enough that I can't
figure it out (this is only my second real query i'm so REALLY new).
I have a table that looks like this:
CustKey InvoiceDate
01 2006-05-19
02 2006-05-19
03 2006-05-19
04 2006-04-28
02 2006-05-19
03 2006-05-19
04 2006-05-19
04 2006-05-19
03 2006-05-19
I want my output to look like this:
CustKey Total for 2006-05-19
01 1
02 2
03 3
04 2
Basically I need a list that will tell me, by CustKey, how many Invoices
were done on a given day.
Now I can do a quick count that will tell me for a given customer and date
but I don't know how to have it check and return values for all 4 customers
based on date.
So far I have:
SELECT COUNT * FROM "Table1"
WHERE CustKey = '01'
AND "InvoiceDate" = ('2006-05-19')
Any help would be appreciated. I'm trying to save myself from having to do
a manual count of invoices on a wly basis.
Thanks in advance,
NancySELECT CustKey,COUNT( *) FROM Table1
WHERE InvoiceDate = ('2006-05-19')
GROUP BY CustKey
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis, I figured it would be something simple.
This works perfectly!
"SQL" wrote:

> SELECT CustKey,COUNT( *) FROM Table1
> WHERE InvoiceDate = ('2006-05-19')
> GROUP BY CustKey
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>sql

newbie question - multiple databases

Can a single SQL 2000 server run multiple databses
concurrently? Or is it just a one to one ratio? Thanks
Yes a single server can run multiple user database and system databases on a
single sql server
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Andy" <andrew@.eclise-micro.com> wrote in message
news:865801c43222$7ca65950$a301280a@.phx.gbl...
> Can a single SQL 2000 server run multiple databses
> concurrently? Or is it just a one to one ratio? Thanks
|||Thank you Greg
>--Original Message--
>Yes a single server can run multiple user database and
system databases on a
>single sql server
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Andy" <andrew@.eclise-micro.com> wrote in message
>news:865801c43222$7ca65950$a301280a@.phx.gbl...
>
>.
>

newbie question - multiple databases

Can a single SQL 2000 server run multiple databses
concurrently? Or is it just a one to one ratio? ThanksYes a single server can run multiple user database and system databases on a
single sql server
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Andy" <andrew@.eclise-micro.com> wrote in message
news:865801c43222$7ca65950$a301280a@.phx.gbl...
> Can a single SQL 2000 server run multiple databses
> concurrently? Or is it just a one to one ratio? Thanks|||Thank you Greg
>--Original Message--
>Yes a single server can run multiple user database and
system databases on a
>single sql server
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Andy" <andrew@.eclise-micro.com> wrote in message
>news:865801c43222$7ca65950$a301280a@.phx.gbl...
>> Can a single SQL 2000 server run multiple databses
>> concurrently? Or is it just a one to one ratio? Thanks
>
>.
>

newbie question - multiple databases

Can a single SQL 2000 server run multiple databses
concurrently? Or is it just a one to one ratio? ThanksYes a single server can run multiple user database and system databases on a
single sql server
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Andy" <andrew@.eclise-micro.com> wrote in message
news:865801c43222$7ca65950$a301280a@.phx.gbl...
> Can a single SQL 2000 server run multiple databses
> concurrently? Or is it just a one to one ratio? Thanks|||Thank you Greg
>--Original Message--
>Yes a single server can run multiple user database and
system databases on a
>single sql server
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Andy" <andrew@.eclise-micro.com> wrote in message
>news:865801c43222$7ca65950$a301280a@.phx.gbl...
>
>.
>

Newbie Query Problem

I want to use the same field in one table and return multiple columns for
different criteria. In other words...
First column
SUM(Sales.NetSales) as 'Total Sales').
Then I want a second column as
SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
Is this reasonable? I am sure that it is a simple thing that I am just
ignorant of.
Thanks.
ChuckChuck,
Try:
--rows
SELECT CategoryID, SUM(Sales) AS 'Total Sales'
FROM NetSales
GROUP BY CategoryID
--or
--columns
SELECT 'Category 1' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 1),
'Category 2' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 2)
HTH
Jerry
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:94FD5CDE-C7BE-43CF-857C-2A847675A1EF@.microsoft.com...
>I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||SELECT
(SELECT SUM(NetSales) FROM Sales) as TotalSales,
(SELECT SUM(NetSales) FROM Sales WHERE categoryid = '02') as Category02
Chuck wrote:
> I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||On Mon, 19 Sep 2005 11:58:06 -0700, Chuck wrote:

>I want to use the same field in one table and return multiple columns for
>different criteria. In other words...
>First column
>SUM(Sales.NetSales) as 'Total Sales').
>Then I want a second column as
>SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
>Is this reasonable? I am sure that it is a simple thing that I am just
>ignorant of.
>Thanks.
>Chuck
Hi Chuck,
Here's a way that requires only one pass over the table:
SELECT SUM(NetSales) AS 'Total Sales',
SUM(CASE WHEN categoryid = '02' THEN NetSales ELSE NULL END) AS
'Category 02'
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie Q: Executing StoredProc?

hi all,
i just wonder if below storedproc will update record correctly if executed
concurrently by multiple user using
varying parameter value or it containe logic error.
CREATE PROCEDURE UpdateQTY @.QTY int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
GO
Hi,
The Syntax of the Storeprocedure (SP) shows that it would update all the
records in the Product table.
Is this what you want to achieve ?
If not, then add a where clause in the Update statement, where it would
contain one or more columns, that would together select a distinct row.
for example
CREATE PROCEDURE UpdateQTY @.QTY int,@.ProductID int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
where productid = @.productid
GO
The above example would find a row with the matching Productid and update
the Qty field for it.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I missed one point in your question.
There will be no problems if multiple users call the SP simultaneously to
update the Product Table.
One more thing, is your QTY as integer or a numeric column. If it requires
to store decimal, then setting the parameter as int would make it to loose
its accuracy. So just make sure that the datatype of QTY matches that in
the table definition.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thanks for the response

newbie needs help with @@identity

I have a form that submits to multiple tables. After insertion into the first table I need to access the identity key from the record and use is to associate a record in another table. The form element I'm inserting into the second table however, is not a required field so I think I need to check IS NOT NULL first. In my code below I have copied the insert statement for the first table and the conditional and subsequent insert into the 2nd table. I am uncertain where and how I get and use @.@.identity. The error I'm getting when I run the Check Syntax button is: 'incorrect syntax near @.@.identity.'

I appreciate someone telling me how to correct my syntax.

INSERT INTO GPRA_Activities
(
SubmitDate,
StaffId,
GPRAId,
FreedomID,
DocumentDesc,
ActivityTitle,
ActivityDesc

)
VALUES
(
getDate(),
@.StaffId,
@.GPRAId,
@.FreedomID,
@.DocumentDesc,
@.ActivityTitle,
@.ActivityDesc

SELECT @.@.identity
)

if @.KeywordId1 IS NOT NULL

@.@.identity smallint,

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.@.identity,
@.KeywordId1
)

GO

You need to get the value of @.@.IDENTITY Into a local variable and use it. You cannot use the @.@.IDENTITY by itself.

Declare @.valintINSERT INTO GPRA_Activities(SubmitDate,StaffId,GPRAId,FreedomID,DocumentDesc,ActivityTitle,ActivityDesc)VALUES (getDate(),@.StaffId,@.GPRAId,@.FreedomID,@.DocumentDesc,@.ActivityTitle,@.ActivityDesc)SELECT @.val = SCOPE_IDENTITY()if @.KeywordId1ISNOT NULL-- @.@.identity smallint, I dont know what you are trying to do hereINSERT INTO GPRA_KeywordsUsed ( ActivityId, KeywordId )VALUES ( @.val, @.KeywordId1 )GO

|||

I finally got the SQL code below not to error (though I haven't been able to submit my form yet. Keep getting error message about expected number of parameters. That one will be my nemesis.

What is the difference between @.@.identy and SCOPE_IDENTITY?

DECLARE
@.ActivityId smallint
SELECT @.ActivityId = @.@.Identity

if @.KeywordId1 IS NOT NULL

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.ActivityId,
@.KeywordId1
)

|||

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

I'm new at this so please forgive my ignorance.

So, if I use @.@.identity and there are multiple users of the application at once, could the wrong identity get "grabbed"?

|||

Possible. HEre's some info from Books on line:

For example, there are two tables,T1 andT2, and an INSERT trigger is defined onT1. When a row is inserted toT1, the trigger fires and inserts a row inT2. This scenario illustrates two scopes: the insert onT1, and the insert onT2 by the trigger.

Assuming that bothT1 andT2 have identity columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement onT1. @.@.IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted inT2. SCOPE_IDENTITY() will return the IDENTITY value inserted inT1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

|||thank you. I'll change it.

Newbie looking for direction

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

Without rewriting the stored procedure to do batch processing, you can use an OLE DB Command transformation in your data flow to execute that stored procedure.|||

kbutterly wrote:

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

You can execute SQL code (i.e. stored procedures) from the pipeline using the OLE DB Command component.

Try that first and see how you get on with it.

-Jamie

|||

kbutterly wrote:

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

You could load data to a staging table - and run the Stored procedure on that

IMHO - OLE DB Command task performs too slow - so SQL based solution might be best bet performance wise|||

ViewMaster,

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

Thanks for helping a newbie,

Kathryn

|||

Jamie and Phil,

thanks for pointing out the OLE DB Command. I will look into it.

Kathryn

|||

See if this helps:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1139922&SiteID=1

|||

kbutterly wrote:

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

What is "transaction services"?

|||

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

|||

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB. My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

|||

kbutterly wrote:

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

Oh OK. Well in answer to your question "can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?", the answer is "Yes, absolutely".

-Jamie

|||

kbutterly wrote:

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB.

not true. The input is whatever is in the pipeline. The OLE DB Connection Manager that you define is whatever relational db you are going to execute the SQLagainst.

kbutterly wrote:

My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Use an Excel Source Adapter.

kbutterly wrote:

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

I don't know of any tutorial buts there's got to be something out there somewhere. Google turned up these:

http://www.developer.com/db/article.php/10920_3497511_2

http://msdn2.microsoft.com/en-us/library/ms141138.aspx

-Jamie

Newbie looking for direction

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

Without rewriting the stored procedure to do batch processing, you can use an OLE DB Command transformation in your data flow to execute that stored procedure.|||

kbutterly wrote:

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

You can execute SQL code (i.e. stored procedures) from the pipeline using the OLE DB Command component.

Try that first and see how you get on with it.

-Jamie

|||

kbutterly wrote:

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

You could load data to a staging table - and run the Stored procedure on that

IMHO - OLE DB Command task performs too slow - so SQL based solution might be best bet performance wise|||

ViewMaster,

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

Thanks for helping a newbie,

Kathryn

|||

Jamie and Phil,

thanks for pointing out the OLE DB Command. I will look into it.

Kathryn

|||

See if this helps:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1139922&SiteID=1

|||

kbutterly wrote:

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

What is "transaction services"?

|||

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

|||

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB. My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

|||

kbutterly wrote:

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

Oh OK. Well in answer to your question "can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?", the answer is "Yes, absolutely".

-Jamie

|||

kbutterly wrote:

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB.

not true. The input is whatever is in the pipeline. The OLE DB Connection Manager that you define is whatever relational db you are going to execute the SQLagainst.

kbutterly wrote:

My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Use an Excel Source Adapter.

kbutterly wrote:

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

I don't know of any tutorial buts there's got to be something out there somewhere. Google turned up these:

http://www.developer.com/db/article.php/10920_3497511_2

http://msdn2.microsoft.com/en-us/library/ms141138.aspx

-Jamie

Newbie like question

Hi

How do I run Multiple Likes in one query? Do I need to run each Like
seperately?

Thanks for any help.Sorry. I don't think that was clear. What I meant was I need something that
is similar to:

Select * from Security..SecUser
where UserId in (123, 456, 789)

The following query does not work:

Select * from Security..SecUser
where UserId like ('%123%', '%456%', '%789%')

Is there a query that can do this?

Thanks again|||You can use logical operators to join two tables; put the patterns you
want to match in one table, and join it to the source table. Here's an
example:

--Table with data that I want to look for a pattern in
DECLARE @.Root TABLE (KeyVal int,
Root varchar(10))

INSERT INTO @.Root
SELECT 1, 'ABCDEF'
UNION ALL
SELECT 2, 'DEFGHI'
UNION ALL
SELECT 3, '123ABC'
UNION ALL
SELECT 4, '123DEF'

--Table of patterns
DECLARE @.LikeTest TABLE (LikeTest varchar(10))
INSERT INTO @.LikeTest
SELECT 'ABC'
UNION ALL
SELECT 'DEF'
UNION ALL
SELECT 'XYZ'

--Results; note that I used DISTINCT to return a single value for each
match.
SELECT DISTINCT r.KeyVal, r.Root
FROM @.Root r JOIN @.LikeTest l ON r.Root LIKE '%' + l.LikeTest + '%'

HTH,
Stu|||Alternatively:

Select * from Security..SecUser
where
UserId like '%123%'
or UserId like '%456%'
or UserId like '%789%'

Obviously, if you have a ton of them, it'll be a pain to type them all
out.|||Thanks for the help, guys. That was much appreciated!!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1

Newbie Authentication

Have Server2003, IIS6 with multiple websites, sql2000, and just installed
reporting services. After making my first simple report with graph to see how
this works (XP pro), I published to the server. But, I was the only one able
to see it using the server name. So, I changed the permissions on the
reportServer and reports under IIS Default website to allow IUSR_serverName
to read/execute and enabled anonymous access with IUSR_serverName.
Now, if I want to deploy update to report to web I have to take off the new
IUSR permissions and then put them back. And, only folks within the company
could get to the report - dns is done at the firewall.
So, I made a virtual directory under IIS within the web called Reports, and
made sure the permissions on the virtual directory were set to read/execute.
Now, if I use a dial-up and a url similar to below, I'm told the user
doesn't have permissions to view the page.
http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
I've probably done a thousand things wrong - anybody willing to enlighten me?
Thanks so much.Have you found the answer to the issue? I'm interested to hear what
your status is because I'm having the same problem as well.
Thanks
janetb <janetb@.discussions.microsoft.com> wrote in message news:<16B90326-C1D2-44E6-AD5B-C64CC21373FE@.microsoft.com>...
> Have Server2003, IIS6 with multiple websites, sql2000, and just installed
> reporting services. After making my first simple report with graph to see how
> this works (XP pro), I published to the server. But, I was the only one able
> to see it using the server name. So, I changed the permissions on the
> reportServer and reports under IIS Default website to allow IUSR_serverName
> to read/execute and enabled anonymous access with IUSR_serverName.
> Now, if I want to deploy update to report to web I have to take off the new
> IUSR permissions and then put them back. And, only folks within the company
> could get to the report - dns is done at the firewall.
> So, I made a virtual directory under IIS within the web called Reports, and
> made sure the permissions on the virtual directory were set to read/execute.
> Now, if I use a dial-up and a url similar to below, I'm told the user
> doesn't have permissions to view the page.
> http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
> I've probably done a thousand things wrong - anybody willing to enlighten me?
> Thanks so much.|||Angela,
Nope. I've tried a bunch of different settings at this point. Including
the
<ReportServerExternalURL>http://www.domain.org/ReportServer</ReportServerExternalURL>
setting, but nothing has worked. I've posted at: aspfree; sqlteam,
sqljunkies, ms, sqlmagazine - nobody is answering. Even watched a few more
ms webcams. Frankly, I probably won't be able to use the product if I can't
find a solution.
I'll do my best to come back and let you know if somebody helps me out.
jb
"Angella" wrote:
> Have you found the answer to the issue? I'm interested to hear what
> your status is because I'm having the same problem as well.
> Thanks
> janetb <janetb@.discussions.microsoft.com> wrote in message news:<16B90326-C1D2-44E6-AD5B-C64CC21373FE@.microsoft.com>...
> > Have Server2003, IIS6 with multiple websites, sql2000, and just installed
> > reporting services. After making my first simple report with graph to see how
> > this works (XP pro), I published to the server. But, I was the only one able
> > to see it using the server name. So, I changed the permissions on the
> > reportServer and reports under IIS Default website to allow IUSR_serverName
> > to read/execute and enabled anonymous access with IUSR_serverName.
> >
> > Now, if I want to deploy update to report to web I have to take off the new
> > IUSR permissions and then put them back. And, only folks within the company
> > could get to the report - dns is done at the firewall.
> >
> > So, I made a virtual directory under IIS within the web called Reports, and
> > made sure the permissions on the virtual directory were set to read/execute.
> >
> > Now, if I use a dial-up and a url similar to below, I'm told the user
> > doesn't have permissions to view the page.
> >
> > http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
> >
> > I've probably done a thousand things wrong - anybody willing to enlighten me?
> >
> > Thanks so much.
>|||"janetb" <janetb@.discussions.microsoft.com> wrote in message
news:16B90326-C1D2-44E6-AD5B-C64CC21373FE@.microsoft.com...
> Have Server2003, IIS6 with multiple websites, sql2000, and just installed
> reporting services. After making my first simple report with graph to see
how
> this works (XP pro), I published to the server. But, I was the only one
able
> to see it using the server name. So, I changed the permissions on the
> reportServer and reports under IIS Default website to allow
IUSR_serverName
> to read/execute and enabled anonymous access with IUSR_serverName.
> Now, if I want to deploy update to report to web I have to take off the
new
> IUSR permissions and then put them back. And, only folks within the
company
> could get to the report - dns is done at the firewall.
> So, I made a virtual directory under IIS within the web called Reports,
and
> made sure the permissions on the virtual directory were set to
read/execute.
> Now, if I use a dial-up and a url similar to below, I'm told the user
> doesn't have permissions to view the page.
>
http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
> I've probably done a thousand things wrong - anybody willing to enlighten
me?
> Thanks so much.
>
Having the same problem, too. Any updates?
Thanks!
Ken|||Ken,
Nothing concrete yet, but I'm at least talking to folks on the Minsasi
website and on SQL magazine. If I get anything concrete, I'll post here for
both you and Angela.
Thanks,
Janet
"Ken" wrote:
> "janetb" <janetb@.discussions.microsoft.com> wrote in message
> news:16B90326-C1D2-44E6-AD5B-C64CC21373FE@.microsoft.com...
> > Have Server2003, IIS6 with multiple websites, sql2000, and just installed
> > reporting services. After making my first simple report with graph to see
> how
> > this works (XP pro), I published to the server. But, I was the only one
> able
> > to see it using the server name. So, I changed the permissions on the
> > reportServer and reports under IIS Default website to allow
> IUSR_serverName
> > to read/execute and enabled anonymous access with IUSR_serverName.
> >
> > Now, if I want to deploy update to report to web I have to take off the
> new
> > IUSR permissions and then put them back. And, only folks within the
> company
> > could get to the report - dns is done at the firewall.
> >
> > So, I made a virtual directory under IIS within the web called Reports,
> and
> > made sure the permissions on the virtual directory were set to
> read/execute.
> >
> > Now, if I use a dial-up and a url similar to below, I'm told the user
> > doesn't have permissions to view the page.
> >
> >
> http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
> >
> > I've probably done a thousand things wrong - anybody willing to enlighten
> me?
> >
> > Thanks so much.
> >
> Having the same problem, too. Any updates?
> Thanks!
> Ken
>
>|||Great! I'll certainly do the same. Thanks so much, Janet!
Ken
"janetb" <janetb@.discussions.microsoft.com> wrote in message
news:3FAAD6BF-4273-46B9-BF2D-285C92824312@.microsoft.com...
> Ken,
> Nothing concrete yet, but I'm at least talking to folks on the Minsasi
> website and on SQL magazine. If I get anything concrete, I'll post here
for
> both you and Angela.
> Thanks,
> Janet
> "Ken" wrote:
> > "janetb" <janetb@.discussions.microsoft.com> wrote in message
> > news:16B90326-C1D2-44E6-AD5B-C64CC21373FE@.microsoft.com...
> > > Have Server2003, IIS6 with multiple websites, sql2000, and just
installed
> > > reporting services. After making my first simple report with graph to
see
> > how
> > > this works (XP pro), I published to the server. But, I was the only
one
> > able
> > > to see it using the server name. So, I changed the permissions on the
> > > reportServer and reports under IIS Default website to allow
> > IUSR_serverName
> > > to read/execute and enabled anonymous access with IUSR_serverName.
> > >
> > > Now, if I want to deploy update to report to web I have to take off
the
> > new
> > > IUSR permissions and then put them back. And, only folks within the
> > company
> > > could get to the report - dns is done at the firewall.
> > >
> > > So, I made a virtual directory under IIS within the web called
Reports,
> > and
> > > made sure the permissions on the virtual directory were set to
> > read/execute.
> > >
> > > Now, if I use a dial-up and a url similar to below, I'm told the user
> > > doesn't have permissions to view the page.
> > >
> > >
> >
http://www.mydomainname.com/reports?%2freports%2fwnchngrads&rs:command=render
> > >
> > > I've probably done a thousand things wrong - anybody willing to
enlighten
> > me?
> > >
> > > Thanks so much.
> > >
> >
> > Having the same problem, too. Any updates?
> >
> > Thanks!
> > Ken
> >
> >
> >

Newbie : Need Help in joining Multiple tables

I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_IdOriginally posted by ritz1975
I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_Id
You need to say:

AND job_rate.effective_date =
( SELECT MAX(jr.effective_date)
FROM job_rate jr
WHERE jr.effective_date <= Work_Detail.Wrkd_Work_Date
AND jr.job_id = job.job_id)

It is common to have a job_rate.end_date column to overcome this, so that the condition is simply:

AND Work_Detail.Wrkd_Work_Date BETWEEN job_rate.effective_date AND job_rate.end_date

This simplifies the query, but adds complication to the rate maintenance functionality.|||Thanks Andrewst .

The query has worked & I am satisfied after testing it .Thanks a lot for the help.

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad

Newbie - How to update multiple rows with select statment

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like

update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.

Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO

CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)

INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'

UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'

SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.

-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0

:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0

Thanks r937. It does answers my question.

Cheers

Newb: Managing multiple queries

I am currently using enterprise manager to run multiple queries on a
single table in a DB. I refresh these queries every few minutes. Due
to the huge number of them I was looking for a better way (or should I
just say "a way") to manage/save these queries so I can recall them
easier/faster for monitoring purposes. Suggestions?

TIA.Hi,

Maybe creating a stored procedure (or more, if the queries are logically
grouped) will help you. Then you would need just to execute the stored
procedures in Query Analyzer (which is the tool intended to run queries
anyway). Alternatively you can save the queries to a script file, and then
open and run in Query Analyzer.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Thu, 6 Dec 2007 10:38:06 -0800 (PST), Akhenaten
<jonkokko@.gmail.comwrote:

If I understand you correctly you need an application (Access ADP, or
..NET) to call your queries based on a timer or a button click. SQL
Server tools alone are probably not going to do the trick.

-Tom.

Quote:

Originally Posted by

>I am currently using enterprise manager to run multiple queries on a
>single table in a DB. I refresh these queries every few minutes. Due
>to the huge number of them I was looking for a better way (or should I
>just say "a way") to manage/save these queries so I can recall them
>easier/faster for monitoring purposes. Suggestions?
>
>TIA.

|||On Dec 6, 10:45 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Hi,
>
Maybe creating a stored procedure (or more, if the queries are logically
grouped) will help you. Then you would need just to execute the stored
procedures in Query Analyzer (which is the tool intended to run queries
anyway). Alternatively you can save the queries to a script file, and then
open and run in Query Analyzer.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Are there examples of "script files"? I have a few files that are used
for creating databases and tables, but somehow I think there is more
potential in using scripts.

Thanks|||Under "script files" I meant to save your frequently used SQL code to a
file, preferably with extension ".sql". Since you indicate you already save
your code to files, then you have your script files.

In SQL Server 2005, the SQL Server Management Studio adds a new capability
to organize scripts in projects (very similar to Visual Studio projects). It
is accessible via the File menu in SSMS (File -New - Project -SQL
Server Scripts template).

Script files are good because they can be easily added to a source control
system.

HTH,

Plamen Ratchev
http://www.SQLStudio.com