How to create sql server login for workgroup computer?

How can I add a new sql server login using windows authentication where the login id is from a user belonging in a workgroup?

No domain connection. Just a workgroup account will have a login in the sql server using windows authentication. Thanks
Thats not possible, WIndows authentication can be used if the server can prove your identity. I a (non-trusted) workgroup, this is not possible, because everyone can join the workgoup and could spoof another ones identity.

You can only use the local accounts of the SQL Server to do so. In those enviroments you can impersonate the user with a remote account on the SQL Server machine programmatically (like in C# to run under a remote account of the SQL Server box).

There is another way of choosing the same name and the same password on the SQL Server as well as on the client bix, but I don′t think and stated that several times, that this is not a proficient solution.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to create reports dinamically?

Hello. Can I make a report for example with all the columns and make a
program in asp.net in which the user can select the columns he wants to see?
Thanks.Yes of course, unless RDL is a open Language you can stick your parts
together as the User wants. But I would keep in mind that there are already
thrid party tools to do that, therefore looking at these will eventually
save you time and money fordeveloping that on your own.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hello. Can I make a report for example with all the columns and make a
> program in asp.net in which the user can select the columns he wants to
> see?
> Thanks.
>|||Your page doesnt work
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> escribió
en el mensaje news:elyDct0dFHA.220@.TK2MSFTNGP12.phx.gbl...
> Yes of course, unless RDL is a open Language you can stick your parts
> together as the User wants. But I would keep in mind that there are
already
> thrid party tools to do that, therefore looking at these will eventually
> save you time and money fordeveloping that on your own.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
> news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> > Hello. Can I make a report for example with all the columns and make a
> > program in asp.net in which the user can select the columns he wants to
> > see?
> >
> > Thanks.
> >
> >
>sql

how to create parameterised views?

I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.

For my previous reports i've used views . But for this functionality i need a parameterized view, which i cant create.

Please help me how to create a parameterized view...

Thanks in Advance...

Use a stored procedure.

|||

Given a date and if you need to find the first day of the month/last day of the month here's how you can do it.

DECLARE @.DdatetimeSET @.D =getdate()SELECT ,DATEADD(dd, -DATEPART(dd,GETDATE()) + 1,GETDATE())'First day of the month' ,DATEADD(d, -DAY(DATEADD(m,1,@.Date)),DATEADD(m,1,@.Date))AS'Last day of the month'

You can use the same in a view as follows:

SELECTFROM dbo.vwSomeViewWHERE DatecolumnBETWEEN @.begDateAND @.EndDate

|||

You can't create a parameterized view. You can create a view and then filter it. Or you can create a table valued function, which is pretty close to a parameterized view.

how to create pages on-the-fly

Hi,
Its a simple requirement but i have failed to figure it out.
I have a Report.rpt file which is printing some text inputted by the user and it is going to print the number of copy of the pages (like "Page N of M" in crystal report) which is also given by the user.

i.e. if the user inputs 3 as the number of copy it is going to print, the Report needs to be build with creating 3 pages and following text needs to be there :
Page 1 of 3
Page 2 of 3
Page 3 of 3

The problem is to create these 3 pages in the report.

There is no group inserted and connection with any source. Only the text value and the value for the number of pages send from VB6.

I could have send the report to the printer M# of times, depending on the value of M given by the user But it is not a good one and i also need to give the option to choose the printer to the user (crxRpt.PrinterSetup 0 ). And that solution is going to ask to choose the printer for each page.

May be its a dump question but i am in pain. Please think for a while to help me out.

Regards
-JoyshimaWhy do you want to do this?
I dont think this is possible
Can you explain more?|||Thanks for your responce. I was waiting for you.

I am doing it for a nursing home and they need to take print out of some kind of header card. Lets say they need to print 3 header card and they will be as follows :

------
Header 1 of 3
XXXXXX
------

------
Header 2 of 3
XXXXXX
------

------
Header 3 of 3
XXXXXX
------

So the input is the total number of the header card and the text they want to be printed on it. Also I need to give them the option to choose the printer before printing and number of copies they want.

Please suggest me something for it.|||Instead of Page 1 of 3 do you want to show it as header 1 of 3?|||"Page 1 of 3" or "Header 1 of 3" that text is not the problem. The problem is to create that 3 pages according to users input.

I have created two parameter on which i am sending (from VB6) the value for the text "XXXXXX" and the value that the user inputs (i.e. for the example it is 3).

Now with this information i need to create the pages (i.e. 3 page) and print the text and the header/Page text.

Any idea? how can i?|||You need to have a table having these information
Text
Header

When user inputs those say "XXXXXXX" and 3, insert three records to that table having
XXXXXXX header 1 of 3
XXXXXXX header 2 of 3
XXXXXXX header 3 of 3

Now design a report based on this table
Group it by first column
Right click on Group header and select Section Expert
Check the check box, "New Page After"

Try it and see what happens|||Yes Madhi, I have done this to give the solution, but couldn't like that. For a small amount of data you need to carry a database and table - too much heavy. And also it is simple task to print some text on page.

Is it possible to create recordset without any table from VB6? If i can put the data as three record inside that recordset and send it to the report file (rpt file), i may be able to have three pages from it.

Therefore the three record on the recordset would be :
XXXXXXX header 1 of 3
XXXXXXX header 2 of 3
XXXXXXX header 3 of 3
This is only my idea. Let me know your opinion.
-Joyshima|||I am not sure about your point

See if this supports your point
http://support.businessobjects.com/library/kbase/articles/c2011950.asp

How to create objects under dbo without DDL admin rights??

Does someone have any suggestions on what rights to grant a user to allow
them to only create stored procedures, functions under dbo without granting
DDL admin?Hi,
Open Database properties and the under permissions you can set for user to
create and alter objects.
Danijel Novak
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||That I know, but I want a user to be able to create a new procedure, but
create it as dbo.procedure. As far as I know, you cannot do this without DDL
Admin rights.
"Danijel Novak" wrote:

> Hi,
> Open Database properties and the under permissions you can set for user to
> create and alter objects.
> --
> Danijel Novak
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>|||In SQL 2000 and earlier versions, the user must be either:
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member
Of course, membership in these role provides considerably more permissions
as well.
Note that the ability to create dbo-owned procs and functions essentially
allows users to retrieve and manipulate data in all dbo-owned tables. This
is one reason why one typically allows only DBAs to create dbo-owned
objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||Hi,
true said by Dan , that's why ! we only do it with only
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member , so in shore please don't permit it to not dbo
user.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Dan Guzman" wrote:

> In SQL 2000 and earlier versions, the user must be either:
> - a db_ddladmin role member
> - a db_owner role member
> - the database owner
> - a sysadmin role member
> Of course, membership in these role provides considerably more permissions
> as well.
> Note that the ability to create dbo-owned procs and functions essentially
> allows users to retrieve and manipulate data in all dbo-owned tables. Thi
s
> is one reason why one typically allows only DBAs to create dbo-owned
> objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>

How to create new user?

Hi,

i'm using SQL server 2005 and I need to create new login/user. Previously I used MySQL where user management is much simplier, so I need a help with SQL server. I run following script:

EXEC sp_addlogin 'uzivatele', @.passwd = 'xyz', @.defdb = 'master', @.deflanguage = 'Czech'
GO

EXEC sp_addsrvrolemember 'uzivatele', 'sysadmin'
GO

GRANT CONNECT SQL TO [uzivatele]
GO

CREATE USER [jirka]
FOR LOGIN [uzivatele]
GO

But when I try to connect o database "master" as user "jirka" with password "xyz", it fails with message "Login failed for user 'jirka'". What do I wrong?

thanks

Jiri Matejka

Hi Jiri,

You need to login as "uzivatele" with password of "xyz" - this will then map the login to the user "jirka" on the master database.

Cheers,
Rob

how to create my Validate Login SP IN sql 2000 ?

hi,all:

I'm new to Sql 2000,now I have a login asp.net page and I used the sql 2000 database.

my login page included a user id and password need user inputed. if the user input the correct userid

and password ,IE will transfer to main page,or there will show eorror message in login page.

my SP like this:

CREATE PROCEDURE dbo.Usp_Accounts_ValidateLogin
@.userid char(4) ,
@.EncPassword binary
AS
if (select count(*) from hhmxUserData whereUserid=@.userid andUserPWD=@.EncPassword) >0

return 1

else

return 0
GO
my asp.net code like this:

dim result As Integer

dim rowsAffected as integer

myConnection.Open()
Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters)
rowsAffected = command.ExecuteNonQuery
result = CInt(command.Parameters("ReturnValue").Value)
myConnection.Close()

Return result

I test it in sql 2000,it's ok.but when I performed it and retrieve the "returnValue", it still return 0 .

so how can I create my correct SP ?

thanks so much.

Change your datatype of userid from char(4) to varchar(4). When you use CHAR, if your user enter the userid which is less than 4 characters in length spaces will be added at the end and your search query may not look like what is should be.

|||

Yes,my UserID is fixed 4 characters length (like: 1012,1008),I think it's not problem,the point is I'm always get the wrong returnValue in my asp.net code when I performed my SP.

So maybe my SP has problem ?

|||

I would recommend you use OUTPUT parameters. check out Books On Line for more info.

check the 2nd part inthis articleto retrieve the output from OUTPUT variable.

How to create multiple tables on the fly so that every user each has his/her own set of tables?

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

To be honest, this is not really a good idea. Why don′t you store the just in one table with an identifier for the actual user which the data belong to. You will additionally have less pain finding the right table for the user and therefore need less coding logic. The naming part as you mentioned can′be done in SQL Server 2005 unless you create a schema for every user who will have the availbility to create a table. (SQL Server changed the objectowner to the Schema identifier) So I would better use the first option mentioned above to do the work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The problem is, I need to create several tables per user this way, including a number of interrelated primary and foreign keys, for my webapplication. As mentioned, I can already do this for the dbo. using a stored procedure, but this would give the wrong behavior, because all users would then use the same set of tables. I want to give each user his/her own set of tables (dynamically) so that one user would not overwrite the stored work of another user...|||

i really dont know what you are up to but this is really a very, very bad idea.

please review database normalization first.

any way if you badly need it. here's a code to start with

use northwind
BEGIN TRANSACTION -- just in case you want to abort

--list the users

select IDENTITY(int, 1,1) AS ID_Num,name into #mytempx
from sysusers

create table modeltable change the table definition to the one you need
(
x varchar(10)
)

declare @.x int,@.MAX_ID INT,@.CMD NVARCHAR(200)
select @.x=0
SELECT @.MAX_ID=max(id_num)from #mytempX
while @.x<>@.MAX_ID loop thru the users and create each db
begin
select @.x=@.x+1
select @.CMD='SELECT * INTO [' + RTRIM(LTRIM( NAME)) + '] FROM MODELTABLE ' FROM #MYTEMPX
WHERE ID_NUM=@.X
SELECT @.CMD this is the create scripts
EXEC sp_executesql @.STATEMENT=@.CMD run the create scripts
end

drop table #mytempx
DROP TABLE MODELTABLE

SELECT * FROM GUEST -- test the create table

ROLLBACK TRANSACTION -- change to commit if you are very sure

regards,

joey

|||

OK, thanx for the info... I'm beginning to see that it would probably be easier to store the data I need as a BLOB (e.g. a serialized bunch of ArrayLists) instead of based on many tables per user. I then take the logic for the program away from the server (like it should be in a three tier solution, I guess), and that is probably your point as well...

Thanx for the suggestions nonetheless, it seems that this is far beyond my skills as a DB programmer...

Greetingz,

DJR

How to create multiple tables on the fly so that every user each has his/her own set of tabl

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

To be honest, this is not really a good idea. Why don′t you store the just in one table with an identifier for the actual user which the data belong to. You will additionally have less pain finding the right table for the user and therefore need less coding logic. The naming part as you mentioned can′be done in SQL Server 2005 unless you create a schema for every user who will have the availbility to create a table. (SQL Server changed the objectowner to the Schema identifier) So I would better use the first option mentioned above to do the work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The problem is, I need to create several tables per user this way, including a number of interrelated primary and foreign keys, for my webapplication. As mentioned, I can already do this for the dbo. using a stored procedure, but this would give the wrong behavior, because all users would then use the same set of tables. I want to give each user his/her own set of tables (dynamically) so that one user would not overwrite the stored work of another user...|||

i really dont know what you are up to but this is really a very, very bad idea.

please review database normalization first.

any way if you badly need it. here's a code to start with

use northwind
BEGIN TRANSACTION -- just in case you want to abort

--list the users

select IDENTITY(int, 1,1) AS ID_Num,name into #mytempx
from sysusers

create table modeltable change the table definition to the one you need
(
x varchar(10)
)

declare @.x int,@.MAX_ID INT,@.CMD NVARCHAR(200)
select @.x=0
SELECT @.MAX_ID=max(id_num)from #mytempX
while @.x<>@.MAX_ID loop thru the users and create each db
begin
select @.x=@.x+1
select @.CMD='SELECT * INTO [' + RTRIM(LTRIM( NAME)) + '] FROM MODELTABLE ' FROM #MYTEMPX
WHERE ID_NUM=@.X
SELECT @.CMD this is the create scripts
EXEC sp_executesql @.STATEMENT=@.CMD run the create scripts
end

drop table #mytempx
DROP TABLE MODELTABLE

SELECT * FROM GUEST -- test the create table

ROLLBACK TRANSACTION -- change to commit if you are very sure

regards,

joey

|||

OK, thanx for the info... I'm beginning to see that it would probably be easier to store the data I need as a BLOB (e.g. a serialized bunch of ArrayLists) instead of based on many tables per user. I then take the logic for the program away from the server (like it should be in a three tier solution, I guess), and that is probably your point as well...

Thanx for the suggestions nonetheless, it seems that this is far beyond my skills as a DB programmer...

Greetingz,

DJR

How to create multiple personal tables on the fly for each registered user of a website using VW

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

In SQL Server you will need to create a schema for each user name to get the effect that you want, for example:

CREATE SCHEMA [user name]
AUTHORIZATION [user name];

CREATE TABLE [user name].Table1
(id int);

Then you can do things like:

SELECT * FROM [user name].Table1;

There are a number of variations of how you might do this, but this is a simple example of the basics.

Dan

|||

OK, doing some extra research in Server Books Online 2005 Express I found the same info. But here's my problem: How to do it dynamically.

Here's the full scenario:

1) I create a user in my web application using the default database ASPNETDB.mdf created by using the login controls in ASP.NET 2.0 (all goes well so far)

2) I then want to create the complete set of tables dynamically for the user that was just created, in a different database (e.g. APPDB.mdf)

So far, I think I have to tackle it this way, but I don't seem to be able to figure out how to actually make it work (or find out if it's really possible at all):

- Use the username for the user just created to create a USER in SQL Server. I would like to do this using a stored procedure, so I can put in the username as a parameter and have the USER created automatically as soon as the user is created in ASP.NET

- Then, I would have to create the SCHEMA in SQL Server, so that the tables I create in this SCHEMA (seems one can create the tables at the same time as you create the SCHEMA, according to Server Books Online) are named [username].Table1 etc.

- After this, there would have to be a way to make sure that when the user logs in each time he gains control over his/her own tables in the database, i.e. the Database Username would have to be linked to the ASPNET Username.

Any Thoughts? All are wellcome...

DJR

sql

How to create multiple personal tables on the fly for each registered user of a website usin

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

In SQL Server you will need to create a schema for each user name to get the effect that you want, for example:

CREATE SCHEMA [user name]
AUTHORIZATION [user name];

CREATE TABLE [user name].Table1
(id int);

Then you can do things like:

SELECT * FROM [user name].Table1;

There are a number of variations of how you might do this, but this is a simple example of the basics.

Dan

|||

OK, doing some extra research in Server Books Online 2005 Express I found the same info. But here's my problem: How to do it dynamically.

Here's the full scenario:

1) I create a user in my web application using the default database ASPNETDB.mdf created by using the login controls in ASP.NET 2.0 (all goes well so far)

2) I then want to create the complete set of tables dynamically for the user that was just created, in a different database (e.g. APPDB.mdf)

So far, I think I have to tackle it this way, but I don't seem to be able to figure out how to actually make it work (or find out if it's really possible at all):

- Use the username for the user just created to create a USER in SQL Server. I would like to do this using a stored procedure, so I can put in the username as a parameter and have the USER created automatically as soon as the user is created in ASP.NET

- Then, I would have to create the SCHEMA in SQL Server, so that the tables I create in this SCHEMA (seems one can create the tables at the same time as you create the SCHEMA, according to Server Books Online) are named [username].Table1 etc.

- After this, there would have to be a way to make sure that when the user logs in each time he gains control over his/her own tables in the database, i.e. the Database Username would have to be linked to the ASPNET Username.

Any Thoughts? All are wellcome...

DJR

How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to create automatic restore Prod DB backup to Test DB

The USER in question is forcing me to Restore the Production Data base backup from Sunday Night overtop of the

Test database early EVERY Monday Morning before they come in.

I can do this by hand

They do NOT want it done via replication or hand Export of production and hand Import of data. They want this to be a scheduled task.

I have gone into the DTS wizard and I am able to drop all the TEST data, objects etc.

And I can restore the PRODUCTION db to TEST, but only by hand.

Is there a way to have the DTS package read a backed up file on the disk and restore it to the TEST db.

I know this sounds lame, but that is their desire, and I'm new to MS-SQL...running MS-SQL 2000

if anyone know how to I would appreciate it, the DTS is NOT very intuitive IMO, and a bit confusing.

Sonoma

This can be done as a step on a job.
First, create a backup command and restore command in Query Analyser and verify it works.
Second, put in 2 steps on a job scheduled when you want.Sure, if first step is succes then next to second.

In other hand simplify the problem choosing a same name for name of database backup (Sure if the name is bult differently you can consider using global variable for parameter in a package , but be simple )|||

OK, Think I got it.

1. go into query analyser, create a step a./1 = Backing up the database in question: actually this step is already done, so doing a 'IF EXISTS' dir function might solve step 1/a.

2. in analyser, create step 2/b = Restoring the database but with a different name [?] or with a move function [?] to a different file in the same directory. This latter is a bit of choice I guess.

3. Make sure step 2 is dependent on step 1 success.

4. schedule job to run at desired time.

sound correct.

thanks for your help. BTW

rik

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels
sql

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels

how to create ASPNET user in SQL Server 2000

Respected Members,

I have a question, when our aspx page is trying to access the SQL Server 2000 database than before this do we have to creaete a user with the name of ASPNET in SQL Server 2000, or without creating this paticular user(its the default user of ASP.NET account) our aspx pages can also access the SQL Server 2000,I have already SQL Server 2000 running in "SQL Server Authentication Mode" so while creating the user do I have to create it in "Windows Authentication" or "SQL Server Authentication", can you provide some link where I could find complete steps for craeting the ASPNET user or if some one can tell all the steps in details here.

This site has some good video tutorials for handling security in both ASP and SQL Server.

http://www.asp.net/learn/videos/default.aspx?tabid=63

|||Thanks for your reply, the site was really good,but I did'nt found any thing relevant to my question on this web site, do you have some idea about any other resources.|||

It seemed to me, perhaps I'm wrong, that the following video tutorials at that site are relevant to your task. ( realize that you indicated you are using SQL Server 2000 (which is a mistake -you should be using SQL Server 2005), but the concepts are basically the same.

And of course, you could refer to Books Online, the definitive source for SQL Server information.

From the “How Do I?” with ASP.NET section:

#7 | How Do I: Secure my Site using Membership and Roles?#9 | How Do I: Create a Full-Featured Customer Login Portal?

From the SQL Server 2005 Express for Beginners section:#7 | Understanding Security and Network Connectivity#8 | Connecting your Web Application to SQL Server 2005 Express Edition

From the Videos for ASP.NET 2.0 Beginners section:#9 | Lesson 9: Securing your Web Site with Membership and Login Controls

how to create ASPNET user in SQL Server 2000

Respected Members,

I have a question, when our aspx page is trying to access the SQL Server 2000 database than before this do we have to creaete a user with the name of ASPNET in SQL Server 2000, or without creating this paticular user(its the default user of ASP.NET account) our aspx pages can also access the SQL Server 2000,I have already SQL Server 2000 running in "SQL Server Authentication Mode" so while creating the user do I have to create it in "Windows Authentication" or "SQL Server Authentication", can you provide some link where I could find complete steps for craeting the ASPNET user or if some one can tell all the steps in details here.

Maybe this link will help:http://msdn2.microsoft.com/en-us/library/ms998300.aspx

How to create an object without being the owner of the schema

Hi

I have a schema called Accounts owned by fred

User bob has create procedure permission as follows:

grant create procedure to bob

bob would like to create a procedure in schema Accounts.

When he issues create proc Accounts.sp_proc.... it fails with:

Msg 2760, Level 16, State 1, Procedure sp_proc, Line 3
The specified schema name "Accounts" either does not exist or you do not have permission to use it.

What permission do I need to grant bob in order to allow this?

Thanks

The answer is in Books Online; look for the Permissions section in the CREATE PROCEDURE article:

http://msdn2.microsoft.com/en-us/library/ms187926(SQL.90).aspx

Thanks
Laurentiu

|||

Thanks Laurentiu, I couldn't see the wood for the trees.

In case anyone else is interested I needed to run:

GRANT ALTER ON SCHEMA::Accounts TO bob

sql

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

how to create an copy of a certain record except one specific column that must be different

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it