Creating a View from ASP.Net page (Problems)

Hello,

I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work.

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

Here is an example (or as close as I can remember at this point) of some code I tried:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1, 2"
myCommand.ExecuteNonQuery()
myCommand.Close()

Any help would be appreciated.

Thanks,
StuporMan

I dont think that is a valid SQL statement.|||

You are correct, but it can be made correct by changing the statement to:

"Create View TestView as Select 1 as test1, 2 as test2"

(you can verify this by running in SQL Server management studio).

Andthis does not change the ASP.net error message.

You can do thefollowing test to show what I mean.

Create an ASP.net page with 2buttons.

One button click runs this code:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

(I have left the definitions and SQL connection string up to you)

On the other button click run this:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

And you should produce the same error message as I am getting.

Thanks,

StuporMan

|||

StuporMan:

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

I've tried to run your command in SQL Server Management Studio:

Create View TestView as Select 1, 2

and it generates this error message:

Msg 4511, Level 16, State 1, Procedure TestView, Line 1
Create View or Function failed because no column name was specified for column 1.

|||

Ok, I had mistyped the SQL I was using as I had to do it from memoryat the time. Here is a valid Create View statement that produces thesame error in ASP.net:

"Create View TestView as Select 1 as test1, 2 as test2"

This should run in SQL Management studio. If it does not try"Create View TestView as Select * from <insert table name here>",which should also be valid. In either case, ASP.net should produce thesame error message.

Thanks,

StuporMan

|||

I am still having problems even after correcting the SQL string.Tryany valid CREATE VIEW statement from ASP.net and see if it will work. Ihave not been able to get any to work to date.

|||Presumably whatever context your web application is under doesn't have the needed security. Are you sure you elevated the permissions of the correct account? (Just guessing here, as I've never tried creating views from a web application myself.)|||

Thanks for the response, you prompted me to again check mypermissions. The user I was logging into the SQL server with DIDhave the correct permission set. The ASPNET user did not. Giving ASPNETthe permission did the trick.

Thanks,

StuporMan

Creating a VB.net event handler for a Stored Procedure

Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.

My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.You can script a custom trace that captures the execution of this task that can instantiate a COM object which in turn can do whatever you want it to do.|||I'm new to developement is there anywhere I can learn to do this.|||I don't know of any way to call code in a VB app running on another machine, but you can create a simple COM object and then use sp_OACreate (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp) and the related procedures to launch your COM object on the server.

Will this do what you want?

-PatP|||There both running on the same machine so with a bit of luck I'll be able to get it working.

Thanks

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 table from .NET

I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @.tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.

Here is my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@.tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END

What went wrong?

Thank you.

DECLARE @.SQL VARCHAR(500)

SET @.SQL = 'CREATE TABLE ' + @.TableName + ' (bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)'

EXEC @.Sql

-- It should be noted that you SHOULD SANITIZE your parameter and make sure that a user does not put anything non-alphanumeric and _ because a malicious user could potentially execute an sql injection if you did not.

If you search for sp_execsql I believe you will find tons of postings.

|||

To be honest, I wouldn't expect the SProc you've listed to work the way you've described in SQL Server either. What you're looking for is dynamic SQL, which has been discussed a number of times in the past weeks in this forum. I suggest you read the information at http://www.sommarskog.se/dynamic_sql.html before you proceed to ensure that you understand the security implications of using SPs with dynamic SQL before implement it.

If the possibility of SQL Injection is not an issue for you, or you've figure out how to mediate it, that same document also has some recomendations on possible implementations.

Mike

|||Thank you both, marcD and Mike.|||Hi,

you should better use the SMO classes which expose an interface (.NET API) for a developer to manage SQL Server objects. You don′t need to care about syntax or semantics, as SMO is object oriented and can be easily used within C# and Visual Studio (with Intellisense) to produce a SQL-injectionfree code (if used the right way :-) ) The API is the successor of the DMO classes, formerly used in SQL Server 2000 and below.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

Creating a report

Hi,
Im new to dot net and CR.
I would like to create a a report based on an SQL query at run time.
How do i do it.
Tnx
PapsHave you tried doing a search on this forum or on Google or Crystal Report's website? There's tons of information on the many different ways to do Crystal Reports, you just have to a bit of digging!

Crystal Reports:
http://support.businessobjects.com/search/advsearch.asp

Google:
http://www.google.com

Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff Smyrski
Hi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a DTS package programaticaly

Hi,
I want to create a DTS package programatically (preferably in
C#.net),which will copy all my tables from a oracle database to my
sql-server database.
Can anybody help me doing this?
Thanks
PatnayakBOL documents all of the DTS API:
http://msdn.microsoft.com/library/e...spapps_21rn.asp

Assuming you are using SQL2000 there's an easy way to familiarise yourself
with the basics of DTS programming. Create a sample DTS package (either
using the Wizard or the Designer), open it up in the Designer and choose
Package/Save As... from the menu. Select "Visual Basic File" from the
Location dropdown and specify a file name. This will generate the Visual
Basic code to create and execute your package. You can then dissect, edit
and extend the code as required.

Inevitably there will be some work involved if you want to move the
generated code to C# but the example of how to manipulate the DTS objects
should give you a helpful start.

--
David Portas
----
Please reply only to the newsgroup
--|||To add to David's response, you can find a cookbook and examples for SQL
Server 2000 DTS with .NET at http://www.sqldev.net/dts/DotNETCookBook.htm.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pattnayak" <dpatnayak@.hotmail.com> wrote in message
news:a57a07c8.0401010301.3d175545@.posting.google.c om...
> Hi,
> I want to create a DTS package programatically (preferably in
> C#.net),which will copy all my tables from a oracle database to my
> sql-server database.
> Can anybody help me doing this?
> Thanks
> Patnayak

Creating a database with a GUI

Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.
-Daticus

Try the link below and download the Express Manager. Hope this helps.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx#EBAA|||I think this is what your looking for...
Andrea Montanari
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
You can also use an Access Project to build an SQL database graphically
Good Luck
Simon

Creating a custom resolver with VB.NET

Someone posted the question "Can anyone point me towards a source code
listing for a working replication custom resolver written in .Net?" Here is
an example that I have written.
Custom resolvers are created by adding a reference to the Microsoft SQL
Replication Conflict Resolver Library, replrec.dll. Unfortunately, the .NET
type library importer incorrectly defines the buffer parameter of methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to be the
address of an Object. For COM interfaces, which is what replrec.dll is
supposed to be, that implies a COM-VARIANT parameter passing mechanism, but
this is not what Replrec passes back. Replrec is simply passing back the
address of a buffer containing the column value which you have to decode.
So the solution involves correcting the method definitions so that a buffer
address can be passed and using the .NET Marshal routines to create the
buffer and move bytes out of the buffer.
Here are the steps involved, followed by a code example that resolves
conflicts by using the column values from the row with the latest date in
user defined column ModifyDate.
1. Open a Visual Studio .NET 2003 Command Prompt
2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
/OUT:SQLResolver_import.dll
3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
int32 pvBuffer"
5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
6. Create a new .NET Windows Control Library project
7. Remove the wizard generated control
8. Use "Add New Item..." to add a new COM class
9. Add reference to SQLResolver.dll created in step #5
Imports System.Text
Imports SQLResolver
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.MarshalAsAttribute
Imports SQLResolver.REPOLE_CHANGE_TYPE
Imports SQLResolver.REPOLE_COLSTATUS_TYPE
<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1
Implements SQLResolver.IVBCustomResolver
Private Const MAX_BUFFER_SIZE As Integer = 1048576
Private Const MAX_NAME_LENGTH As Integer = 128
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
Public Const InterfaceId As String =
"CECFBB8F-584F-4733-9373-B69AFA6F117F"
Public Const EventsId As String = "5D55BA40-A438-4FD4-BA8B-05095DC89948"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
IVBCustomResolver.GetHandledStates
ResolverBm = REPOLEUpdateConflicts
End Sub
Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
Integer, ByVal pvReserved As IReplRowChange) Implements
IVBCustomResolver.Reconcile
Dim cntColumns As Integer
Dim intColumn As Integer
Dim strColumnName As String
Dim strLogMessage As String
Dim WinningData As Object
Dim blnSourceIsWinner As Boolean
Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
Dim intBufferLenActual As Integer
Dim intBufferLen As Integer
Dim strDestinationDateTime As String
Dim strSourceDateTime As String
Dim strDestinationUser As String
Dim strSourceUser As String
Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
Dim strMsg As String
'If Not Debugger.IsAttached Then
' Debugger.Launch()
'Else
' Debugger.Break()
'End If
Call pRowChange.GetNumColumns(cntColumns)
For intColumn = 1 To cntColumns
strColumnName = " ".PadRight(MAX_NAME_LENGTH)
' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
Call pRowChange.GetColumnName(intColumn, strColumnName, MAX_NAME_LENGTH)
' strColumnName.TrimEnd(Chr(vbNull))
strColumnName = strColumnName.TrimEnd()
If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationDateTime = ConvertBufferToDateString(myBuffer)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceDateTime = ConvertBufferToDateString(myBuffer)
If strSourceDateTime > strDestinationDateTime Then
blnSourceIsWinner = True
Else
blnSourceIsWinner = False
End If
End If
If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
End If
Next intColumn
For intColumn = 1 To cntColumns
'Get the column status of each column
pRowChange.GetColumnStatus(intColumn, ColStatus)
' If the column has been updated at both the Publisher and Subscriber
If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
If blnSourceIsWinner Then
pRowChange.CopyColumnFromSource(intColumn)
End If
' For columns that have been updated without any changes, copy column
values from source
ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
pRowChange.CopyColumnFromSource(intColumn)
' For columns that have not been updated - do nothing.
ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
End If
Next intColumn
' Log conflict and call the UpdateRow method to commit all the column value
changes.
'
If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0 Then
If blnSourceIsWinner Then
strMsg = "Losing update(s) made by " & strDestinationUser
Else
strMsg = "Losing update(s) made by " & strSourceUser
End If
End If
pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
Call pRowChange.UpdateRow()
Marshal.FreeHGlobal(myBuffer)
End Sub
Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
Dim s As String = String.Empty
Dim i(7) As Short
Dim j As Integer
Marshal.Copy(p, i, 0, i.Length)
s = i(0).ToString '4 digit Year
For j = 1 To i.GetUpperBound(0)
s &= i(j).ToString.PadLeft(2, "0"c)
Next
ConvertBufferToDateString = s
End Function
Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
intBufferLenActual As Integer) As String
Dim i As Integer
Dim s As String = String.Empty
For i = 0 To intBufferLenActual - 1
s &= Chr(Marshal.ReadByte(p, i))
Next
ConvertBufferToString = s
End Function
Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
String, ByVal inMsg As String)
s = s & ";" & inMsg
If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
End Sub
End Class
u rock man!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Douglas Arterburn" <darterburn@.precisdev.com> wrote in message
news:uzhwyd7iFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Someone posted the question "Can anyone point me towards a source code
> listing for a working replication custom resolver written in .Net?" Here
> is an example that I have written.
> Custom resolvers are created by adding a reference to the Microsoft SQL
> Replication Conflict Resolver Library, replrec.dll. Unfortunately, the
> .NET type library importer incorrectly defines the buffer parameter of
> methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to
> be the address of an Object. For COM interfaces, which is what
> replrec.dll is supposed to be, that implies a COM-VARIANT parameter
> passing mechanism, but this is not what Replrec passes back. Replrec is
> simply passing back the address of a buffer containing the column value
> which you have to decode.
> So the solution involves correcting the method definitions so that a
> buffer address can be passed and using the .NET Marshal routines to create
> the buffer and move bytes out of the buffer.
> Here are the steps involved, followed by a code example that resolves
> conflicts by using the column values from the row with the latest date in
> user defined column ModifyDate.
> 1. Open a Visual Studio .NET 2003 Command Prompt
> 2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
> /OUT:SQLResolver_import.dll
> 3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
> 4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
> GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
> int32 pvBuffer"
> 5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
> 6. Create a new .NET Windows Control Library project
> 7. Remove the wizard generated control
> 8. Use "Add New Item..." to add a new COM class
> 9. Add reference to SQLResolver.dll created in step #5
> Imports System.Text
> Imports SQLResolver
> Imports System.Runtime.InteropServices
> Imports System.Runtime.InteropServices.MarshalAsAttribute
> Imports SQLResolver.REPOLE_CHANGE_TYPE
> Imports SQLResolver.REPOLE_COLSTATUS_TYPE
> <ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
> Public Class ComClass1
> Implements SQLResolver.IVBCustomResolver
> Private Const MAX_BUFFER_SIZE As Integer = 1048576
> Private Const MAX_NAME_LENGTH As Integer = 128
> #Region "COM GUIDs"
> ' These GUIDs provide the COM identity for this class
> ' and its COM interfaces. If you change them, existing
> ' clients will no longer be able to access the class.
> Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
> Public Const InterfaceId As String =
> "CECFBB8F-584F-4733-9373-B69AFA6F117F"
> Public Const EventsId As String =
> "5D55BA40-A438-4FD4-BA8B-05095DC89948"
> #End Region
> ' A creatable COM class must have a Public Sub New()
> ' with no parameters, otherwise, the class will not be
> ' registered in the COM registry and cannot be created
> ' via CreateObject.
> Public Sub New()
> MyBase.New()
> End Sub
> Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
> IVBCustomResolver.GetHandledStates
> ResolverBm = REPOLEUpdateConflicts
> End Sub
> Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
> Integer, ByVal pvReserved As IReplRowChange) Implements
> IVBCustomResolver.Reconcile
> Dim cntColumns As Integer
> Dim intColumn As Integer
> Dim strColumnName As String
> Dim strLogMessage As String
> Dim WinningData As Object
> Dim blnSourceIsWinner As Boolean
> Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
> Dim intBufferLenActual As Integer
> Dim intBufferLen As Integer
> Dim strDestinationDateTime As String
> Dim strSourceDateTime As String
> Dim strDestinationUser As String
> Dim strSourceUser As String
> Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
> Dim strMsg As String
> 'If Not Debugger.IsAttached Then
> ' Debugger.Launch()
> 'Else
> ' Debugger.Break()
> 'End If
> Call pRowChange.GetNumColumns(cntColumns)
> For intColumn = 1 To cntColumns
> strColumnName = " ".PadRight(MAX_NAME_LENGTH)
> ' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
> Call pRowChange.GetColumnName(intColumn, strColumnName,
> MAX_NAME_LENGTH)
> ' strColumnName.TrimEnd(Chr(vbNull))
> strColumnName = strColumnName.TrimEnd()
> If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationDateTime = ConvertBufferToDateString(myBuffer)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceDateTime = ConvertBufferToDateString(myBuffer)
> If strSourceDateTime > strDestinationDateTime Then
> blnSourceIsWinner = True
> Else
> blnSourceIsWinner = False
> End If
> End If
> If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> End If
> Next intColumn
> For intColumn = 1 To cntColumns
> 'Get the column status of each column
> pRowChange.GetColumnStatus(intColumn, ColStatus)
> ' If the column has been updated at both the Publisher and Subscriber
> If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
> If blnSourceIsWinner Then
> pRowChange.CopyColumnFromSource(intColumn)
> End If
> ' For columns that have been updated without any changes, copy column
> values from source
> ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
> pRowChange.CopyColumnFromSource(intColumn)
> ' For columns that have not been updated - do nothing.
> ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
> End If
> Next intColumn
> ' Log conflict and call the UpdateRow method to commit all the column
> value changes.
> '
> If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0
> Then
> If blnSourceIsWinner Then
> strMsg = "Losing update(s) made by " & strDestinationUser
> Else
> strMsg = "Losing update(s) made by " & strSourceUser
> End If
> End If
> pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
> REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
> REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
> Call pRowChange.UpdateRow()
> Marshal.FreeHGlobal(myBuffer)
>
> End Sub
> Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
> Dim s As String = String.Empty
> Dim i(7) As Short
> Dim j As Integer
> Marshal.Copy(p, i, 0, i.Length)
> s = i(0).ToString '4 digit Year
> For j = 1 To i.GetUpperBound(0)
> s &= i(j).ToString.PadLeft(2, "0"c)
> Next
> ConvertBufferToDateString = s
> End Function
> Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
> intBufferLenActual As Integer) As String
> Dim i As Integer
> Dim s As String = String.Empty
> For i = 0 To intBufferLenActual - 1
> s &= Chr(Marshal.ReadByte(p, i))
> Next
> ConvertBufferToString = s
> End Function
> Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
> String, ByVal inMsg As String)
> s = s & ";" & inMsg
> If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
> End Sub
> End Class
>

Creating a .NET Stored Procedure in Sql Server 2005 Express Edition

Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer? Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure?

Regards...

Shashi Kumar Nagulakonda.

I can't help you with the first question.

The answer to the second question is obviously it depends. As far as I know (and I know very little about .NET sps), given the same task, the T-SQL sp should execute faster. Now that doesn't preclude that yes, you can write a really bad T-SQL sp that performs worse than a well written .NET sp, but as far as I know the .NET sp's work on sets of data very similiarly to a cursor, and therefore will experience the same speed (or lack thereof). Not to mention the overhead of loading the .NET runtime into memory, and doing all the jitting. Then again, some things would be much easier in .NET because T-SQL isn't a full programming language, so the .NET sps have a much greater versitality.

Again, I have actually no experience with using, writing, or debugging .NET sp's. If someone more knowledgable wants to chime in, please do so.

creating / changing owner problem

I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.

//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();

//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;geoff (cakewalkr7@.hotmail.com) writes:
> I'm trying to create a new database and new login to allow a client
> (through my asp.net page) to create their own database, username and
> password. I've tried using the stored procedures I've found in BOL,
> but I can't get it to work right. It keeps saying that I can't assign
> this username as the db owner since it's already a user for the
> database. I then read other posts about reassigning the db owner to
> another dummy account and then trying to reassign it to the new one,
> but that isn't working either. Can anyone look at my code and tell me
> what I'm doing wrong? Thanks.

If you intend to make the login owner of the database, there is no
reason to create a user for him in the database, but just skip that
step. Once the login owns the database, he also has a user in it.

Note: A login in SQL Server is server-wide. A user is something in a
database, which can be connected to a login. The username and loginname
may be the same, but they don't have to.

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

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

CreateSubscription

Hi,
I am not an expert but I want to create a subscription. Where can I find informations? I don't understand all.
Moreover, I used Visual Stucio.NET. In my application, I have created a webservice. I want that my application sends via e-mail the report. Must I use CreateSubscription method? What are the stages? What must I do?I have a brief intro here:
http://www.odetocode.com/Articles/114.aspx

CreateDataDrivenSubscription

I try to use .NET to subscribe report which email address is retrived from Access databse. I follow the online example of CreateDataDrivenSubscription and the main difference is the mail list come from Access DB.
The code is as follow:
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
But when I run the report, the following error is shown:
rsMissingElement
400
The required field DataSourceDefinitionOrReference is missing from the input structure.
What's wrong with my code ?Can you post more of your code? From the looks of it perhaps you are not
setting the Item property on your DataRetrievalPlan to the
DataSourceDefinition that you defined below.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> I try to use .NET to subscribe report which email address is retrived from
Access databse. I follow the online example of CreateDataDrivenSubscription
and the main difference is the mail list come from Access DB.
> The code is as follow:
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> But when I run the report, the following error is shown:
> rsMissingElement
> 400
> The required field DataSourceDefinitionOrReference is missing from
the input structure.
> What's wrong with my code ?|||The code are as follows:
Dim delivery As New RptSvc.DataSource
delivery.Name = ""
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
dataSrcDefinition.Enabled = True
dataSrcDefinition.EnabledSpecified = True
dataSrcDefinition.Extension = "OLEDB"
dataSrcDefinition.ImpersonateUser = False
dataSrcDefinition.ImpersonateUserSpecified = False
dataSrcDefinition.UserName = "admin"
dataSrcDefinition.Password = ""
dataSrcDefinition.Prompt = Nothing
dataSrcDefinition.WindowsCredentials = False
delivery.Item = dataSrcDefinition
' Create the fields list.
Dim fieldsList(1) As RptSvc.Field
fieldsList(0) = New RptSvc.Field
fieldsList(0).Name = "EmailAddress"
fieldsList(0).Alias = "EmailAddress"
fieldsList(1) = New RptSvc.Field
fieldsList(1).Name = "EmplID"
fieldsList(1).Alias = "EmplID"
' Create the data set for the delivery query.
Dim dataSetDefinition As New RptSvc.DataSetDefinition
dataSetDefinition.AccentSensitivitySpecified = False
dataSetDefinition.CaseSensitivitySpecified = False
dataSetDefinition.KanatypeSensitivitySpecified = False
dataSetDefinition.WidthSensitivitySpecified = False
dataSetDefinition.Fields = fieldsList
Dim queryDefinition As New RptSvc.QueryDefinition
queryDefinition.CommandText = "Select * from MailList"
queryDefinition.CommandType = "Text"
queryDefinition.Timeout = 45
queryDefinition.TimeoutSpecified = True
dataSetDefinition.Query = queryDefinition
Dim results As New RptSvc.DataSetDefinition
Dim changed As Boolean
Try
results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
' Set the event type and match data for the delivery.
Dim dataRetrieval As New RptSvc.DataRetrievalPlan
dataRetrieval.DataSet = results
' Set the event type and match data for the delivery.
Dim eventType As String = "TimedSubscription"
Dim matchData As String = "<ScheduleDefinition>" & _
"<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
"<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
"<DaysOfWeek>" & _
"<Thursday>True</Thursday>" & _
"</DaysOfWeek></WeeklyRecurrence>" & _
"</ScheduleDefinition>"
' Set the report parameter values.
Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
empID.ParameterName = "EmplID"
empID.FieldAlias = "EmplID"
parameters(0) = empID
Dim VVD As New RptSvc.ParameterValue
VVD.Name = "VVD"
VVD.Value = "CB1345"
parameters(1) = VVD
Dim OPR As New RptSvc.ParameterValue
OPR.Name = "OPR"
OPR.Value = "*"
parameters(2) = OPR
Dim BillOpr As New RptSvc.ParameterValue
BillOpr.Name = "BILL_COMP"
BillOpr.Value = "*"
parameters(3) = BillOpr
Try
Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
Actually, the code is simliar as the example
Thanks a lot !!!
"Daniel Reib [MSFT]" wrote:
> Can you post more of your code? From the looks of it perhaps you are not
> setting the Item property on your DataRetrievalPlan to the
> DataSourceDefinition that you defined below.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > I try to use .NET to subscribe report which email address is retrived from
> Access databse. I follow the online example of CreateDataDrivenSubscription
> and the main difference is the mail list come from Access DB.
> >
> > The code is as follow:
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> >
> > But when I run the report, the following error is shown:
> > rsMissingElement
> > 400
> > The required field DataSourceDefinitionOrReference is missing from
> the input structure.
> >
> > What's wrong with my code ?
>
>|||I find a Microsoft document about the errors of sample code. As you say, I forget to set item property of dataRetrievel plan to dataSurceDefinition.
Thanks for your help !!!
"May Liu" wrote:
> The code are as follows:
> Dim delivery As New RptSvc.DataSource
> delivery.Name = ""
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
> dataSrcDefinition.Enabled = True
> dataSrcDefinition.EnabledSpecified = True
> dataSrcDefinition.Extension = "OLEDB"
> dataSrcDefinition.ImpersonateUser = False
> dataSrcDefinition.ImpersonateUserSpecified = False
> dataSrcDefinition.UserName = "admin"
> dataSrcDefinition.Password = ""
> dataSrcDefinition.Prompt = Nothing
> dataSrcDefinition.WindowsCredentials = False
> delivery.Item = dataSrcDefinition
> ' Create the fields list.
> Dim fieldsList(1) As RptSvc.Field
> fieldsList(0) = New RptSvc.Field
> fieldsList(0).Name = "EmailAddress"
> fieldsList(0).Alias = "EmailAddress"
> fieldsList(1) = New RptSvc.Field
> fieldsList(1).Name = "EmplID"
> fieldsList(1).Alias = "EmplID"
> ' Create the data set for the delivery query.
> Dim dataSetDefinition As New RptSvc.DataSetDefinition
> dataSetDefinition.AccentSensitivitySpecified = False
> dataSetDefinition.CaseSensitivitySpecified = False
> dataSetDefinition.KanatypeSensitivitySpecified = False
> dataSetDefinition.WidthSensitivitySpecified = False
> dataSetDefinition.Fields = fieldsList
> Dim queryDefinition As New RptSvc.QueryDefinition
> queryDefinition.CommandText = "Select * from MailList"
> queryDefinition.CommandType = "Text"
> queryDefinition.Timeout = 45
> queryDefinition.TimeoutSpecified = True
> dataSetDefinition.Query = queryDefinition
> Dim results As New RptSvc.DataSetDefinition
> Dim changed As Boolean
> Try
> results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> ' Set the event type and match data for the delivery.
> Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> dataRetrieval.DataSet = results
> ' Set the event type and match data for the delivery.
> Dim eventType As String = "TimedSubscription"
> Dim matchData As String = "<ScheduleDefinition>" & _
> "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
> "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> "<DaysOfWeek>" & _
> "<Thursday>True</Thursday>" & _
> "</DaysOfWeek></WeeklyRecurrence>" & _
> "</ScheduleDefinition>"
> ' Set the report parameter values.
> Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> empID.ParameterName = "EmplID"
> empID.FieldAlias = "EmplID"
> parameters(0) = empID
> Dim VVD As New RptSvc.ParameterValue
> VVD.Name = "VVD"
> VVD.Value = "CB1345"
> parameters(1) = VVD
> Dim OPR As New RptSvc.ParameterValue
> OPR.Name = "OPR"
> OPR.Value = "*"
> parameters(2) = OPR
> Dim BillOpr As New RptSvc.ParameterValue
> BillOpr.Name = "BILL_COMP"
> BillOpr.Value = "*"
> parameters(3) = BillOpr
> Try
> Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
> reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> Actually, the code is simliar as the example
> Thanks a lot !!!
> "Daniel Reib [MSFT]" wrote:
> > Can you post more of your code? From the looks of it perhaps you are not
> > setting the Item property on your DataRetrievalPlan to the
> > DataSourceDefinition that you defined below.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > I try to use .NET to subscribe report which email address is retrived from
> > Access databse. I follow the online example of CreateDataDrivenSubscription
> > and the main difference is the mail list come from Access DB.
> > >
> > > The code is as follow:
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > >
> > > But when I run the report, the following error is shown:
> > > rsMissingElement
> > > 400
> > > The required field DataSourceDefinitionOrReference is missing from
> > the input structure.
> > >
> > > What's wrong with my code ?
> >
> >
> >|||In case anyone is interested:
http://support.microsoft.com/?kbid=842854
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> I find a Microsoft document about the errors of sample code. As you say,
I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> Thanks for your help !!!
> "May Liu" wrote:
> > The code are as follows:
> >
> > Dim delivery As New RptSvc.DataSource
> > delivery.Name = ""
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> >
> > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
Security Info=False"
> > dataSrcDefinition.CredentialRetrieval =RptSvc.CredentialRetrievalEnum.Store
> > dataSrcDefinition.Enabled = True
> > dataSrcDefinition.EnabledSpecified = True
> > dataSrcDefinition.Extension = "OLEDB"
> > dataSrcDefinition.ImpersonateUser = False
> > dataSrcDefinition.ImpersonateUserSpecified = False
> > dataSrcDefinition.UserName = "admin"
> > dataSrcDefinition.Password = ""
> > dataSrcDefinition.Prompt = Nothing
> > dataSrcDefinition.WindowsCredentials = False
> >
> > delivery.Item = dataSrcDefinition
> >
> > ' Create the fields list.
> > Dim fieldsList(1) As RptSvc.Field
> > fieldsList(0) = New RptSvc.Field
> > fieldsList(0).Name = "EmailAddress"
> > fieldsList(0).Alias = "EmailAddress"
> > fieldsList(1) = New RptSvc.Field
> > fieldsList(1).Name = "EmplID"
> > fieldsList(1).Alias = "EmplID"
> >
> > ' Create the data set for the delivery query.
> > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > dataSetDefinition.AccentSensitivitySpecified = False
> > dataSetDefinition.CaseSensitivitySpecified = False
> > dataSetDefinition.KanatypeSensitivitySpecified = False
> > dataSetDefinition.WidthSensitivitySpecified = False
> > dataSetDefinition.Fields = fieldsList
> >
> > Dim queryDefinition As New RptSvc.QueryDefinition
> > queryDefinition.CommandText = "Select * from MailList"
> > queryDefinition.CommandType = "Text"
> > queryDefinition.Timeout = 45
> > queryDefinition.TimeoutSpecified = True
> > dataSetDefinition.Query = queryDefinition
> > Dim results As New RptSvc.DataSetDefinition
> > Dim changed As Boolean
> >
> > Try
> > results = rs.PrepareQuery(delivery, dataSetDefinition,
changed)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > ' Set the event type and match data for the delivery.
> > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > dataRetrieval.DataSet = results
> >
> > ' Set the event type and match data for the delivery.
> > Dim eventType As String = "TimedSubscription"
> > Dim matchData As String = "<ScheduleDefinition>" & _
> > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
_
> > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > "<DaysOfWeek>" & _
> > "<Thursday>True</Thursday>" & _
> > "</DaysOfWeek></WeeklyRecurrence>" & _
> > "</ScheduleDefinition>"
> >
> > ' Set the report parameter values.
> > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> >
> > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > empID.ParameterName = "EmplID"
> > empID.FieldAlias = "EmplID"
> > parameters(0) = empID
> >
> > Dim VVD As New RptSvc.ParameterValue
> > VVD.Name = "VVD"
> > VVD.Value = "CB1345"
> > parameters(1) = VVD
> >
> > Dim OPR As New RptSvc.ParameterValue
> > OPR.Name = "OPR"
> > OPR.Value = "*"
> > parameters(2) = OPR
> >
> > Dim BillOpr As New RptSvc.ParameterValue
> > BillOpr.Name = "BILL_COMP"
> > BillOpr.Value = "*"
> > parameters(3) = BillOpr
> >
> > Try
> > Dim subscriptionID As String =rs.CreateDataDrivenSubscription( _
> > reportPath, settings, dataRetrieval, sEmaiDesc,
eventType, matchData, parameters)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > Actually, the code is simliar as the example
> >
> > Thanks a lot !!!
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > Can you post more of your code? From the looks of it perhaps you are
not
> > > setting the Item property on your DataRetrievalPlan to the
> > > DataSourceDefinition that you defined below.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > >
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > I try to use .NET to subscribe report which email address is
retrived from
> > > Access databse. I follow the online example of
CreateDataDrivenSubscription
> > > and the main difference is the mail list come from Access DB.
> > > >
> > > > The code is as follow:
> > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > >
> > > > But when I run the report, the following error is shown:
> > > > rsMissingElement
> > > > 400
> > > > The required field DataSourceDefinitionOrReference is
missing from
> > > the input structure.
> > > >
> > > > What's wrong with my code ?
> > >
> > >
> > >|||I have two more question. Where is the physical location of generated file after the report is generated and email to user ? Does reporting service provide UI to let me to open this report ?
"Bryan Keller [MSFT]" wrote:
> In case anyone is interested:
> http://support.microsoft.com/?kbid=842854
> --
> Bryan Keller
> Developer Documentation
> SQL Server Reporting Services
> A friendly reminder that this posting is provided "AS IS" with no
> warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> > I find a Microsoft document about the errors of sample code. As you say,
> I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> >
> > Thanks for your help !!!
> >
> > "May Liu" wrote:
> >
> > > The code are as follows:
> > >
> > > Dim delivery As New RptSvc.DataSource
> > > delivery.Name = ""
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > >
> > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
> Security Info=False"
> > > dataSrcDefinition.CredentialRetrieval => RptSvc.CredentialRetrievalEnum.Store
> > > dataSrcDefinition.Enabled = True
> > > dataSrcDefinition.EnabledSpecified = True
> > > dataSrcDefinition.Extension = "OLEDB"
> > > dataSrcDefinition.ImpersonateUser = False
> > > dataSrcDefinition.ImpersonateUserSpecified = False
> > > dataSrcDefinition.UserName = "admin"
> > > dataSrcDefinition.Password = ""
> > > dataSrcDefinition.Prompt = Nothing
> > > dataSrcDefinition.WindowsCredentials = False
> > >
> > > delivery.Item = dataSrcDefinition
> > >
> > > ' Create the fields list.
> > > Dim fieldsList(1) As RptSvc.Field
> > > fieldsList(0) = New RptSvc.Field
> > > fieldsList(0).Name = "EmailAddress"
> > > fieldsList(0).Alias = "EmailAddress"
> > > fieldsList(1) = New RptSvc.Field
> > > fieldsList(1).Name = "EmplID"
> > > fieldsList(1).Alias = "EmplID"
> > >
> > > ' Create the data set for the delivery query.
> > > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > > dataSetDefinition.AccentSensitivitySpecified = False
> > > dataSetDefinition.CaseSensitivitySpecified = False
> > > dataSetDefinition.KanatypeSensitivitySpecified = False
> > > dataSetDefinition.WidthSensitivitySpecified = False
> > > dataSetDefinition.Fields = fieldsList
> > >
> > > Dim queryDefinition As New RptSvc.QueryDefinition
> > > queryDefinition.CommandText = "Select * from MailList"
> > > queryDefinition.CommandType = "Text"
> > > queryDefinition.Timeout = 45
> > > queryDefinition.TimeoutSpecified = True
> > > dataSetDefinition.Query = queryDefinition
> > > Dim results As New RptSvc.DataSetDefinition
> > > Dim changed As Boolean
> > >
> > > Try
> > > results = rs.PrepareQuery(delivery, dataSetDefinition,
> changed)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > > dataRetrieval.DataSet = results
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim eventType As String = "TimedSubscription"
> > > Dim matchData As String = "<ScheduleDefinition>" & _
> > > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
> _
> > > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > > "<DaysOfWeek>" & _
> > > "<Thursday>True</Thursday>" & _
> > > "</DaysOfWeek></WeeklyRecurrence>" & _
> > > "</ScheduleDefinition>"
> > >
> > > ' Set the report parameter values.
> > > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> > >
> > > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > > empID.ParameterName = "EmplID"
> > > empID.FieldAlias = "EmplID"
> > > parameters(0) = empID
> > >
> > > Dim VVD As New RptSvc.ParameterValue
> > > VVD.Name = "VVD"
> > > VVD.Value = "CB1345"
> > > parameters(1) = VVD
> > >
> > > Dim OPR As New RptSvc.ParameterValue
> > > OPR.Name = "OPR"
> > > OPR.Value = "*"
> > > parameters(2) = OPR
> > >
> > > Dim BillOpr As New RptSvc.ParameterValue
> > > BillOpr.Name = "BILL_COMP"
> > > BillOpr.Value = "*"
> > > parameters(3) = BillOpr
> > >
> > > Try
> > > Dim subscriptionID As String => rs.CreateDataDrivenSubscription( _
> > > reportPath, settings, dataRetrieval, sEmaiDesc,
> eventType, matchData, parameters)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > Actually, the code is simliar as the example
> > >
> > > Thanks a lot !!!
> > >
> > > "Daniel Reib [MSFT]" wrote:
> > >
> > > > Can you post more of your code? From the looks of it perhaps you are
> not
> > > > setting the Item property on your DataRetrievalPlan to the
> > > > DataSourceDefinition that you defined below.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > >
> > > >
> > > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > > I try to use .NET to subscribe report which email address is
> retrived from
> > > > Access databse. I follow the online example of
> CreateDataDrivenSubscription
> > > > and the main difference is the mail list come from Access DB.
> > > > >
> > > > > The code is as follow:
> > > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > > >
> > > > > But when I run the report, the following error is shown:
> > > > > rsMissingElement
> > > > > 400
> > > > > The required field DataSourceDefinitionOrReference is
> missing from
> > > > the input structure.
> > > > >
> > > > > What's wrong with my code ?
> > > >
> > > >
> > > >
>
>