Creating an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

Creating a view from a linked server

Hi
i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following
select * from openquery (AccessLinkedServer,'select * from mytable')
i press run and i see the data ok .but when i try to save the view i get
the following
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].
*** Sent via Developersdex http://www.examnotes.net ***try creating the view via query analyzer. there are all kinds of funky
issues with em.
-oj
"tolisss" <nospam@.devdex.com> wrote in message
news:e7H8X6ZRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
> Hi
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: [DataDirect][ODBC dBase driver]Optional feature not
> implemented.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
> [OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
> 0x8004d00a].
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a view from a linked server

Hi

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedServer,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].

*** Sent via Developersdex http://www.developersdex.com ***tolisss (nospam@.devdex.com) writes:
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.

Try creating the view from Query Analyzer. I seem to recall
that when you create a view from Enterprise Manager, it starts a
transaction. Later when you come to create the view itself, the OLE DB
provider cannot upgrade the transaction to a distributed one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.
If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with som
e
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>sql

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:
> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

creating a user with dbo access

Hi,

I need to create a user with dbo access to a specific database using sql. I created a login and added a user to the login. How do i grant dbo privilege to this user (using sql)? Is there any system stored procedure for this? Please help.

Thanks in advance

Hi. Try

sp_changeowner [@.loginame=]'login'

Changes the owner of the current database.

sql

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATEPROCEDURE test

AS

BEGIN

print'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATE PROCEDURE test

AS

BEGIN

print 'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.

If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.

Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.

Many thanks.

In SQL Server Management Studio Express, when you login to the SQL Express Server, you'll see "Databases" as an item in the Object Explorer. If you expand that, you'll see all of your databases listed. Select the database you want and expand that, then you'll see Security as one of the items. Expand that one more time and you'll see Users. You can create a new user there, as well as right click on a user and select Properties to fine tune the permissions of that user.

You can also create global users (Logins) by going to the Security item at the top level list in the Object Explorer. Once you've created a Login, you can map it to certain users within a database.

Thanks< MJ

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.
If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.
Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.
Many thanks.
hi Andrew,
Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
> Many thanks.
please verify your SQLExpress instance accepts standard SQL Server logins,
enabling mixed security.. your connection string changed to ...Integrated
Security=SSPI implies a trusted connection...
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
GRANT {database permission}
http://msdn2.microsoft.com/en-us/library/ms178569.aspx
GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea: Thanks you for your detailed reply.
I understand the concptual difference between Windows and SQL authentication
and thought I had done all the necessary steps, which is why I am puzzled.
As I mentioned, with SQL 2000 I used SQL Enterprise manager and have the
login working. I have tried to map the steps I took in SQL2000's SQL EM to
those in the new SQL Server Mgt. Studio Express.
1) I created a login under the server->security folder.
I did not give this Login any Roles. It is just a plain ol' user (public).
2) I went to the server instance name at the top and GRANTed "Connect SQL"
permission to the new Login just created;
3) I went to the database and granted "Connect" permission to the Login.
After these steps I get the error reported in my earlier post.
I should add that I am not an SQL Server expert and first looked at the SQL
Server Mgt. Studio Express BOL.They are absolutely useless.
Hope the above hels.
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ...Integrated
> Security=SSPI implies a trusted connection...
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||I don't understand the following stement:
> database access, but a (database) user must be mapped to the corresponding
> login..
what is the difference between a Login and a User?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).
it's not a user, it's a login and there's no "public" server role... it's
just a login which is not member of server roles...

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;
yep... the login has been created, enabled, and granted access to the
instance..

> 3) I went to the database and granted "Connect" permission to the
> Login.
ok, you granted db access to the login, which is now member of th public
database role..

> After these steps I get the error reported in my earlier post.
I reproduced your steps and I can log on to SQLExpress as a standard SQL
Server Login, and access a db the login has been mapped to a corresponding
user.. I did it via SSMSExpress and not via ASP..

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
:D
download the full BOL, december released..

> what is the difference between a Login and a User?
a Login is the very first part of the 2 phases security policy enforcement
of SQL Server... I repeat what I already wrote..
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created in order to
grant access to the SQL Server instance... at the server level a login can
be made member of none, 1 or all of the fixed server roles, which include
"sysadmin" role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires credential
becouse it's directly provided by Windows via the logins' ID (sid), which
authenticate (Windows) user's login at the windows login step... SQL Server
only needs to verify that the corresponding login and/or group is granted to
log on the instance (checking the granted Login's existence in the SQL
Server instance)...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
The phrase:
> the login has been mapped to a corresponding user..
Can you explain that.
How did it happen?
Who is the user?
Is it correct to say that the user is an instance of utilizing a login? I.e.
"users" have logins. Some may use the same logins as others (for example
several instances of an application). In which case each application trying
to use SQL server is a user and their name/password pair correspond to their
login?
Thanks.
|||I now have a role named 'fred' and a user named 'fred' of the database that
I want the application to access. Is the use of the same name the problem?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||OK, fixed it, though mainly through luck than knowhow.
Deleted user "fred" and login "fred".
Recreated login Fred but made sure that I went to 'User Mapping' and checked
the database that I wanted this Login to be able to access;
Login was created and, like magic, a user appeared, also named 'fred', under
the database.
I still don't understand this.
Nonetheless, thanks for your help,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> The phrase:
> Can you explain that.
> How did it happen?
> Who is the user?
perhaps my english is not as good as I thought.. :D
perhaps here,
http://msdn.microsoft.com/library/de...rity_4fol.asp,
the architectural design is more clear..
a SQL Server Login (both standard SQL Server login or a WinNT login) is just
the very first brick in the wall in order to access a SQL Server instance...
without that you can not log in and connect to a specified instance... it
has a server wide range...
a User is just a database user (can have the same name as the corresponding
Login [Login is now somehaw obsolete, to be replaced by principals] but this
is not mandatory even if it's recommended in order to minimize troubles :D)
that, at creation time, is mapped via it's sid column
(dbname.sys.sysusers.sid) to an existing login
(master.sys.server_principals.sid)
SELECT u.name AS [Name in DB], u.hasdbaccess ,
p.name AS [Login Name]
FROM master.sys.server_principals p JOIN sys.sysusers u
ON p.sid = u.sid
Principals
http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
DB Users
http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.
Logins can be granted db access as database users, but they can even not..

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?
an application is not a user or a login... in certain case an application
role can represent this scenario
(http://msdn2.microsoft.com/en-us/library/ms190998.aspx)

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.
it's correct... you created a login... then you granted that login db access
via a database user (in the defined database) mapping it to the original
login ...
if you only create a login without grating him/her access to your desired
database(s), that login will only access it if the predefined "guest"
database user is available (and usually that db user is not available for
security reason)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That URL does not use the work 'Login', only 'User'.
What is the difference between a login and a user?
Thanks,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40dk3lF198jigU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/de...rity_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
> Logins can be granted db access as database users, but they can even not..
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Creating a unique index on a table

Hi,
I'm trying to create an index on a newly created table. I want the index to
be on the field ref. I tried running:
CREATE INDEX ref ON U_segment (ref)
but it's just sitting there! The ref is unique anyway, and there are about
700,000 records.
Any help, or advice would be appreaciated.
Regards
Rob
Does it run and stop, or what?
I wonder if having the index name and the column name the same is the
issue...try
CREATE INDEX IX_ref ON U_segment (ref)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
|||Did you look to see if you are being blocked?
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
sql

Creating a System.Management assembly in order for my own Assembly to work?

Hi

I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.

I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.

in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.

namespace inlineCLRsql{

public static class test{

public static void PrintMessage(){

inLineLib.Queue q = new inLineLib.Queue();

int i = q.queueId ;

Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());

}

}

}

to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.

so what I did is (and this is where I need to know if I just ruined sql server or not):

1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.

2- CREATE ASSEMBLY SystemManagement

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

3- CREATE ASSEMBLY inLineLibMaster

FROM 'D:\inLine\Server\inLineLib\bin\Debug\inLineLib.dll'

WITH PERMISSION_SET = unsafe

4- and finally

CREATE ASSEMBLY inLineLib

FROM 'D:\inLine\Server\CLRSQL\inlineCLRsql\bin\Debug\inlineCLRsql.dll'

WITH PERMISSION_SET = SAFE

Everything works after those steps (which took some trial and error). I can create a sproc like:

CREATE PROC sp_test AS

EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage

and it returns the Queue ID

Is there anything unadvisable about the steps above?

Thanks for your help

M

System.Management is not supported by CLR integration in SQL Server 2005. What this means is that it has not been tested for reliability and security to work perfectly under a hosted environment. Some (or all) parts of this assembly might work just fine and some (or none) might not work reliably under conditions like memory pressure, stack overflow, stress etc. You need to test your scenario well (for reliablilty and security) and ensure that it works for you. The list of supported .NET framework assemblies is following:

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

Creating a suscription on a parameterized report

I have a report that I just created that has a month and year parameter.
this report is needed at the first of every month to show the activity of
previous month. I would like to create a subscription that executes this
report automatically. Can I set up reporting services to automatically run
the subscription based on the previous month and year each first of the
month?This can be achieved by setting the parameters non-queried default values to
something liek this
=Today.AddMonths(-1)
This will grab todays date (execution date) and add a negative month. you
can do AddDays and AddYears...
for the start date and
"Aaronous" <aaronous@.hotmail.com> wrote in message
news:%230afyX%23DFHA.3928@.TK2MSFTNGP15.phx.gbl...
>I have a report that I just created that has a month and year parameter.
>this report is needed at the first of every month to show the activity of
>previous month. I would like to create a subscription that executes this
>report automatically. Can I set up reporting services to automatically run
>the subscription based on the previous month and year each first of the
>month?
>|||I have been using:
=Today.AddDays(-1)
The result is on the first day of the month, the default parameter is the
previous month. After that, it uses the current month. When I first started
doing this, I used iif(Day(Today()=1) & Hour(Now())<6, Today.AddDays(-1),
Today()). This assumed that the monthly subscriptions ran before 6am on the
1st of the month. I now assume that if somebody wants to look at a monthly
report on the 1st, they really want to see the last month.
"Joseph Scalise" wrote:
> This can be achieved by setting the parameters non-queried default values to
> something liek this
> =Today.AddMonths(-1)
> This will grab todays date (execution date) and add a negative month. you
> can do AddDays and AddYears...
> for the start date and
> "Aaronous" <aaronous@.hotmail.com> wrote in message
> news:%230afyX%23DFHA.3928@.TK2MSFTNGP15.phx.gbl...
> >I have a report that I just created that has a month and year parameter.
> >this report is needed at the first of every month to show the activity of
> >previous month. I would like to create a subscription that executes this
> >report automatically. Can I set up reporting services to automatically run
> >the subscription based on the previous month and year each first of the
> >month?
> >
>
>

Creating a script file from a diagram

Hi there,

I have created a diagram on my development SQL2005 database and want to transfer this diagram to my live environment. How do I go about creating a script to do this?

thx

Dawid

There is no way to script out diagrams in SQL Server 2005. The binary data in the diagram is tied to your SID (a unique identifier for security principals) when you created the diagram, so I can't even give you foolproof instructions on how to hack the tables to copy the diagram data.

I'm afraid you'll have to recreate your diagram on your live system. If this is a feature you'd like to see in the next version of SQL Server, please file a suggestion on http://connect.microsoft.com/SqlServer. Customer feedback is a very important criterion when we choose which features will make it into the next release.

Thanks,

Steve

|||

I highly recommend using Visio (or one of the other robust diagramming tools) to create your database diagrams.

Printouts are easier. Documentation is easier. Scripts are easy. Changes to the diagrams can be saved WITHOUT changing your database. (Great for development environments.) Multiple step Un-Do/Re-Do!!

Creating a Report to PDF in a folder?

We have created a report called "update" that we want to run every hour and place in a directory called d:\info\update.pdf

We would like to call the report from a procedure and have the proc generate the report in PDF format in the directory d:\info

Does anyone know how to do this?Try the subscription feature of Reporting Services? It will do exactly what you describe.|||Use the file share delivery method. Either create a special share with appropriate security settings, or use can also use the administrative share like this: \\localhost\d$\info, but this would required administrator credentials in the subscription.

Creating a report model based on related entities

I created a report model that has two entities. Both entities, in the SQL
terminology, can have an inner join. Now the two tables do not have any
foreign key relationship or anything. ..
Now when i open the report builder and select a field from one of the entity
the other one just dissapears away. What do i need to do to be able to select
fields from both entities ?The above question is for designing ad hoc reports through the report model.|||It is not visible because when you select an entity then that becomes a
primary entity and remaining is not visible. Now the entities related to the
selected or the primary will be visible. As you said that there is no
relation so it doesn't appear on the list and it wont appear. So you need to
have a relation to be able to select other fields as well.
Amarnath
"Tk_Neo" wrote:
> The above question is for designing ad hoc reports through the report model.|||I have a smilar problem. I have a single data source view which contains 3
tables. Each table is a completely denormalized (like a spreadsheet) dataset
from a particular business system. Within the datasource view editor I
created primary keys on each table and established relationships between each
table. However, when I try to create a report in report builder, as soon as
add an entity from one table to my report, the other 2 tables disappear.
What do I have to do to prevent the tables from disappearing? Please be
specific since I am fairly new to the Ad Hoc component of reporting services.
Thanks.
"Amarnath" wrote:
> It is not visible because when you select an entity then that becomes a
> primary entity and remaining is not visible. Now the entities related to the
> selected or the primary will be visible. As you said that there is no
> relation so it doesn't appear on the list and it wont appear. So you need to
> have a relation to be able to select other fields as well.
> Amarnath
> "Tk_Neo" wrote:
> > The above question is for designing ad hoc reports through the report model.

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

Creating a Param Subscription

When i attempt to create a new e-mail subscription to a report i get
the following error message:
"Subscriptions cannot be created because the credentials used to run
the report are not stored, the report is using user-defined parameter
values, or if a linked report, the link is no longer valid."
I have attempted everything to get this resolved. It works when i try
subscribing to a report that does not use any parameters. The
credentials are "stored securely in the report server" and i have the
default values set for my two parameters.
How do i get around this message in order to create a subscription?Does the report use the User object? If so then you can not subscribe to
it.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1114004323.057963.57760@.f14g2000cwb.googlegroups.com...
> When i attempt to create a new e-mail subscription to a report i get
> the following error message:
> "Subscriptions cannot be created because the credentials used to run
> the report are not stored, the report is using user-defined parameter
> values, or if a linked report, the link is no longer valid."
> I have attempted everything to get this resolved. It works when i try
> subscribing to a report that does not use any parameters. The
> credentials are "stored securely in the report server" and i have the
> default values set for my two parameters.
> How do i get around this message in order to create a subscription?
>|||I do not use the User Object.

Creating a package with data flow task programmatically

I have created a simple package with 1 data flow task:
Source: data reader (to sql mobile)
Destination: oledb (to sql server)
And the aim is to do a "Select * from table_name" from source and move everything to destination database.

I have followed the steps in documentation, but the package fails! An exception is thrown with message: "Exception from HRESULT: 0xC0202072"

Since its the basic functionality of SSIS that I am implementing, it should work! I have spent 2 whole days trying to make this work and now its high time to ask for help. Can you please have a look!

Thanks,
Pragya

P.S. Any pointers to samples implementing a data flow task programmatically will help too!

Code:

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

//Add a SQL Mobile connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("SQLMOBILE");
cm.Name = "SQL Mobile ConnectionManager";
cm.ConnectionString = "Data Source =D:\\Program Files\\Microsoft Visual Studio 8\\SmartDevices\\SDK\\SQL Server\\Mobile\\v3.0\\Northwind.sdf";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "SQL Server Connection Manager";
cm1.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

//Adding source component for SQL Mobile.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "ADONETSource";
component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, blicKeyToken=89845dcd8080cc91";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
instance.SetComponentProperty("SqlCommand", "Select * from Employees");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData90 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.SqlServerDestination.1";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
instance1.SetComponentProperty("BulkInsertKeepNulls", true);

//set path between components
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// WORKS FINE TILL HERE -

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Well, 2 things I notice just from a first look is:

1. You don't set the ConnectionManagerID just the ConnectionManager, but both need to be set.

2. You don't specify a table or sqlstatement for your destination so how does the destination know what table to reinitialize its metadata for?

HTH,
Matt|||1. ConnectionManager.Id is a read only property. How to set it?
2. How can I do that?

Thanks|||

Ok! I got through one step. I added the following line:

instance1.SetComponentProperty("BulkInsertTableName","[Employees]");

to solve this.

However I have related questions,

1. Now the metadata can be initialized, but no rows are being transferred. Something is still amiss...

2. I need to manually create the employees table currently. How can i code the 'create table/view' option so that the table can be generated via code? Do I need to first compile the create table statement with column type information and then execute it using sql client? Is there a better way?

Thanks!

|||

Any information on this would help.

Thanks