I want to install the following on my machine:
MSDE 2000 Release A
SQL Server 2000 SP4
SQL Server 2005 Developer Edition with SP1
SQL Server 2000 Express with SP1
Management Studio
Management Studio Express
The only one that I'm concerning is whether Management Studio and Management Studio Express can coexist in one machine.
Thanks for any help,
Peter
NO. Products have been mutually exclusive since early betas.sqlbegin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:
begin tran
statement1if (@.@.error <> 0)
begin
rollback tran
return
endstatement2
if (@.@.error <> 0)
begin
rollback tran
return
endcommit tran
As an input parameter for stored procedure I have the following XML, its values are contained in both attributes and elements. For attributes we have to use flag = 1, and for attributes 2. Is there any flag or technique that we can retrieve both ?
Here is my script:
declare
@.xml xml,
@.handle int
set @.xml =
'
<Sortable>
<Field ord="1" type="asc">LastName</Field>
<Field ord="2" type="desc">CreateDate</Field>
<Field ord="3" type="asc">ProspectNum</Field>
</Sortable>
'
exec sp_xml_preparedocument @.handle output, @.xml
select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) --'./Field'
)
exec sp_xml_removedocument @.handle
I exepect it to return the foillowing result set:
ord type Field
-- - --
1 asc LastName
2 desc CreateDate
3 asc ProspectNum
Obviously it returns NULL for my 'Field' column.
Thanks
Either of these
select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) '.'
)
select r.value('@.ord','int') as ord,
r.value('@.type','varchar(4)') as type,
r.value('.','varchar(20)') as Field
from @.xml.nodes('/Sortable/Field') as D(r)
Hi all,
Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -
Parameter 1 - 'Select an Application' - List of applications that are available
Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.
Is this possible to do in SSRS?
Thanks,
Brian
Hi Brian,
Yes you can do that. Its called Cascaded parameters you need to write 2 store procedure. 1st one will be for "select an application" and 2nd one will be for "select a module" for the 2nd sp you need to pass the application id as a parameter and depending on the application Id modules will be fetched.
Once these SPs are done you need to set the report parameters make sure order of the parameters in the report parameter is correct.
Take a look at this example
http://msdn2.microsoft.com/en-us/library/aa337426.aspx
I hope this helps
Thanks
Ashwini
|||Hello Brian,
Yes, you can do this. Take a look at 'Cascading Parameters' in Books Online.
Hope this helps.
Jarret
sqlHello,
I am using the following connection string to connect to SQL Server 2005 Express Edition installed on my local PC.
string sqlstr = "Server=(local);Database=MyDB;User ID=sa;Password=sa;Trusted_Connection=False";
And it gives me the following error. The string works fine in computer with SQL Server 2000 instance but fails with 2005. The string also fails for "Server = localhost".
However the string works very fine if I use the name of Server 2005 i.e ./SQLExpress
===================================
Cannot connect to (local).
===================================
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
Error Number: 2
Severity: 20
State: 0
Now the main problem is that, of course I could use a named instance, but since I am embedding the Connection String in a dll file so that any user can use it to connect it to the local instance of the SQL Server 2005 installed in his/her computer, I need to use "server = localhost". Doesn't SQL 2005 support this. If yes, where could i find option to enable it?
I have enabled every option available in surface area configuration for SQL Server 2005, but nothing seems to help out. I even use local as well as remote connections using both TCP/IP and named pipes.
Can nyone help me out?
using vs2005 to build web pages of asp.net 2.0, The database is SQL Server 2000,.But I always fail to connect.
using the following configuration in web.config:
<appSettings>
<add key="DSN_student" value="server=(local);uid=admin;pwd=123456;database=network_course"/>
</appSettings>
I also failed to connect using the following configuration in web.config:
<add name="network_courseConnectionString1" connectionString="Data Source=(local);Initial Catalog=network_course;User ID=admin;Password=123456;"
providerName="System.Data.SqlClient" />
I am a beginner from China and eager to get answers! Thank you!
Can you post the error you got and the code?
Regards
|||To answer your question, yes. You can use VS2005 with SQL Server 2000. Looking at your second connection string seems like it should work. You might add "Integrated Security=false" in the connect string attributes, but I believe that is the default.SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle
FROM HomePageImage H
JOIN shortlist SL on H.StoryId = SL.id
(INNER JOIN category C on H.CategoryId = C.CategoryId)
order by date DESC
I'm not sure what you mean by "does not work" and you didn't post an error message.
However, it's possible you are using the reserved word Date in the order by clause.
Did you mean "order by PublishDate DESC"?
|||it saysIncorrect syntax near the keyword 'INNER'.|||Try to remove the brace that encloses Inner Join and your statement will work fine
Hope my suggestion helps
|||thanks
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
613151115052100x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.ianwr (ianwrigglesworth@.yahoo.co.uk) writes:
Quote:
Originally Posted by
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
Quote:
Originally Posted by
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
>
In sysprocess it showing the following :-
>
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
Quote:
Originally Posted by
Anyone have any ideas on what i can check on?
SELECT COUNT(*) FROM tbl (NOLOCK) WHERE col <expected value
will you some progress information.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for the view to use, unfortunately when i arrived this morning
the task had stopped and took about 29 hours to run.
Going to keep an eye on things and check out the san as well today,
Thanks for the info anyway, if it happens again i'll repost
Thanks
Ian,
Cant work out the following:
Say i have a table 'P_STABILITY_PROTOCOL' that has the following data:
REC_ID ** LAST_AUTHORISED_VERSION_I ** LINKED_PSP_ID_I
1 1 83
2 2 83
3 3 83
4 1 108
5 1 114
6 2 114
I need the max LAST_AUTHORISED_VERSION_I value for each LINKED_PSP_ID_I group so i'm using the following query which returns part of what i need:
*********************************************************************************************************************************
SELECT MAX(LAST_AUTHORISED_VERSION_I) AS LAST_AUTHORISED_VERSION_I,LINKED_PSP_ID_I FROM(
SELECT
P_STABILITY_PROTOCOL.*,
R_PROTOCOL_TYPE.DESCRIPTION_T AS PROTOCOL_TYPE_T,
Z_STATUS.DESCRIPTION_T AS STATUS_T
FROM P_STABILITY_PROTOCOL, Z_STATUS, R_PROTOCOL_TYPE
WHERE (P_STABILITY_PROTOCOL.ZS_ID_I = Z_STATUS.ZS_ID_I)
AND (P_STABILITY_PROTOCOL.RPT_ID_I = R_PROTOCOL_TYPE.RPT_ID_I)
AND (P_STABILITY_PROTOCOL.SSMP_ID_I = 37)
AND (P_STABILITY_PROTOCOL.RETIRED_FLAG_T = 'N')
AND (Z_STATUS.DESCRIPTION_T = 'Approved')
ORDER BY LINKED_PSP_ID_I, LAST_AUTHORISED_VERSION_I)
GROUP BY LINKED_PSP_ID_I
*********************************************************************************************************************************
This returns the correct values of:
LAST_AUTHORISED_VERSION_I ** LINKED_PSP_ID_I
3 83
1 108
2 114
This is i need the REC_ID value (and others) returning as well but obviously if i add it to the SELECT bit of the original query then the 'Group by' will all go to *** and it'll just return every value in the DB not just the highest authorised value for the group. Anyone know how i do this? Could i use a cursor and loop through the table? Tried using self joins but just cannot get it to return the same values as the original query. That LAST_AUTHORISED_VERSION_I and LINKED_PSP_ID_I value combined will always be particular to a single distinct record if thats any help?
Cheers.
Check if this gives you a direction:
Declare @.ttable (recidint, versionidint, pspidint)insert into @.tselect 1 , 1 , 83unionallselect 2 , 2 , 83unionallselect 3 , 3 , 83unionallselect 4 , 1 , 108unionallselect 5 , 1 , 114unionallselect 6 , 2 , 114select T1.recid, T2.MAxVerId, T1.pspidfrom @.t T1Join (Select max(versionid)as MAxVerId, pspidfrom @.tgroup by pspid) T2on T1.versionid = T2.MAxVerIdand T1.pspid = T2.pspidOrder by recid
@.EditorIdint,
@.CategoryIDint,@.ListTitlenvarchar(50),
@.Blurbnvarchar(250),@.FileNamenvarchar(50),
@.ByLinenvarchar(50),@.HTMLCopynvarchar(MAX),
@.MainStorybit,@.MainStoryImageFilenvarchar(50),
@.Publishbit,@.PublishDatesmalldatetime,
@.ListIdint OUTPUT
)
AS
-- Insert the record into the database
INSERT INTOshortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate)
VALUES(@.EditorID,@.CategoryID,@.ListTitle,@.Blurb, @.FileName, @.ByLine, @.HTMLCopy, @.MainStory, @.MainStoryImageFile,@.Publish,@.PublishDate)
-- Read the just-inserted ProductID into @.NewProductID
SET@.ListId =SCOPE_IDENTITY()
here is the sqlDataSource
<asp:SqlDataSource
id="srcShortList"
ConnectionString="<%$ ConnectionStrings:ShortList %>"
SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist"
InsertCommand="AddProductAndReturnNewProductIDValue"
SelectCommandType="StoredProcedure"UpdateCommand="UPDATE shortlist SET CategoryID=@.CategoryID,ListTitle=@.ListTitle,Blurb=@.Blurb,ByLine=@.ByLine,HTMLCopy=@.HTMLCopy,MainStory=@.MainStory,Publish=@.Publish,PublishDate=@.PublishDate WHERE Id=@.Id"
Runat="server">
<SelectParameters>
<asp:QueryStringParameter
Name="Id"
QueryStringField="Id"/>
</SelectParameters>
</asp:SqlDataSource>
Is there a section for your InsertParameters, if you've given everything there then it looks like the paramaters just aren't being created.
Test your stored procedure works by calling it from SQL Server Management Studio, i.e.
exec AddListAndReturnNewIDValue
@.EditorId=1,@.CategoryID = 1,@.ListTitle= 'Hello world',@.Blurb = 'This is a test',@.FileName ='testfile.txt',@.ByLine ='Me',@.HTMLCopy = '<html></html>',@.MainStory= 1,
@.MainStoryImageFile ='myfile.txt',@.Publish =0,@.PublishDate = '2007/09/07 12:00:00'
If that works the problem is in your code, and I'd guess it's the Insert parameters. Try creating the SQL Data Source again from scratch.
Thanks for help
SELECT table1.ColA, table1.ColB, (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
Sometimes the sub-query returns nulls which is fine.
I now want to modify the stored procedure so that it will optionally only return the rows where the sub-query returns non-null values. I've added an int parameter @.filter to the procedure. If @.filter = 0 I will return all rows; if @.filter = 1 I will filter the results.
The query now looks like this:
SELECT table1.ColA, table1.ColB, subquery = (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
WHERE (@.filter = 0 or not subquery is null).
However, I get an invalid column name 'subquery' error.
Have I simply got my syntax wrong or is it not possible to do this. I've played with a few variations such as '(SELECT ....) as subquery' but nothing seems to work.
I can imagine a solution where the sub-query is used to generate a table that table1 can be inner joined with, but the query is quite big (and has been tested) and I didn't want to start hacking it about if there was a trivial fix.
Any help appreciated.I have found the answer to my own question.
The SELECT parameters (including the sub-query) are not evaluated until after the WHERE so what I wanted to do is not possible.
I found the link below useful.
http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html|||i din get ur exact requirement , but yes the syntax is not correct....
read a bit abt 'corelated subqueries' from books online..u'll hopefuly get a solution...
if u dont post back , i'll try to put some example...|||
The SELECT list expressions can be evaluated before the WHERE clause in SQL Server. But that has nothing to do with the problem though. Column aliases are only part of the result set metadata that the client uses. As such it can be used only in the ORDER BY clause (in ANSI SQL, the ORDER BY belongs on the client-side & hence it is allowed in ORDER BY). You can use derived table to simplify complex SELECT statements and avoid repeating expressions like:
SELECT ColA, ColB, ColN
FROM (
SELECT table1.ColA, table1.ColB
, (SELECT TOP 1 colX from table2 WHERE etc. ) as ColN
FROM table1
) as t
WHERE (@.filter = 0 or ColN is not null)
It works now.
Hi there,
I'm trying to come up with the best way to build some C# Unit tests for an SSIS package I've built.
My C# code does the following
1. Creates a Transaction Scope using System.Transactions
2. Puts some source data for my package into a table that the package will read
3. Kicks off the Package using System.Diagnostics.ProcessStartInfo
Im getting a Transaction TimeOut execption which I beleive is caused because the Package can't read the source data I've inserted becuase the package has not joined the transaction.
So the question is - can you call a run a package and make it participate in a transaction that you have created outside of the package?
Thanks.
I don't believe you can do this, as having a look through the documentation there does not seem to be anywhere to pass in that existing transaction context as you load or execute a package.
