|
Can someone help me with 2 issues that I have? My first issue is when I try to print out that the user account exist with the following line: PRINT 'The login ' + @login_name + ' already exists.'
I receive the following error: Server: Msg 446, Level 16, State 8, Line 45
Cannot resolve collation conflict for concatenation operation.
What can I do to solve this?
My second issue is I'm trying to have this procedure work for both Sql 2000 and Sql 2005. I currently using 2000 for testing but in 2005 to create a user login the line of code: --CREATE USER 'username' FOR LOGIN 'loginname' creates a syntax error. Is there anyway around this?
DECLARE @sqlVersion char(2)
DECLARE @login_name char(12)
DECLARE @loginpassword char(12)
DECLARE @username char(12)
DECLARE @dbase char(8)
SET @login_name = loginname
SET @loginpassword = password
SET @username = username
SET @dbase = password
SELECT @sqlVersion = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS char),1,1)
use database
IF NOT EXISTS(SELECT name FROM sysxlogins WHERE name = @login_name)
IF @sqlVersion = 8
BEGIN
EXEC sp_addlogin @login_name, @loginpassword, @dbase
EXEC sp_grantdbaccess @login_name, @username
EXEC sp_addrolemember 'db_datareader', @username
EXEC sp_addrolemember 'db_datawriter', @username
/* Grant the account access to the rms database */
use database
EXEC sp_grantdbaccess @login_name, @username
/* Grant rights to the STORED PROCEDURES */
BEGIN TRANSACTION
USE database
GRANT EXECUTE ON SP TO username
IF @@ERROR <> 0 GOTO err_handler
PRINT '----User was successfully created ----'
COMMIT TRANSACTION
END
ELSE
PRINT 'The login ' + @login_name + ' already exists.'
IF @sqlVersion = 9
BEGIN
EXEC sp_addlogin @login_name, loginpassword, @dbase
--CREATE USER 'opcenteruser' FOR LOGIN 'opcenteruser'
EXEC sp_addrolemember 'db_datareader', @username
EXEC sp_addrolemember 'db_datawriter', @username
use database
EXEC sp_grantdbaccess login_name, @username
EXEC sp_addrolemember 'db_datareader', @username
BEGIN TRANSACTION
/* Grant rights to the STORED PROCEDURES */
USE database
GRANT PERMISSIONS TO STORED PROCEDURES....
IF @@ERROR <> 0 GOTO err_handler
PRINT '----User was successfully created ----'
COMMIT TRANSACTION
END
RETURN
err_handler:
ROLLBACK TRANSACTION
RAISERROR ('Failed to commit transaction.', 16,1)
RETURN
GO
Terrance C.
|
|
|
|
|
Hi everybody,
I'm using MSSQL2005 to build a report system and I need to create some views in order to speed up the execution of queries, but I need these views created so that the WHERE clause contains dynamic values, like parameters or so. If I write this as a simple query, it would be as follows:
SELECT some_field FROM my_table WHERE another_field = @my_parameter
of course, the query I want turned into a view is much more complex, but the point here is the WHERE part and the use of a parameter.
So, I'm wondering if there's a way of using the view somehow that I can specify this parameter.
If anyone knows if this can be done and how to do it, please, let me know.
Thanks in advance
Cheers,
Kenia
|
|
|
|
|
SQL Server doesn't support parameterized views. However you can achieve the same result with a table-valued user-defined function. See this article[^] for more information.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi all
I have table like
Table1
ID Status Flaged
1 NotApproved 1
2 NotApproved 0
3 NotApproved NUll
4 NotApproved NULL
here i need to get the ID in which status=NotApproved and Flaged <>1
so the result should be like this
2 NotApproved 0
3 NotApproved NUll
4 NotApproved NULL
i should get these 3 records....
hw will write the query?...how to pass the parameters?
thanks
|
|
|
|
|
Hi,
You can write query as follows :
select * from Table1 where status='NotApproved' and Flaged NOT LIKE ('1')
assuming that both status and flaged field are of varchar data-type.
Regards,
Ujjaval Modi 
Manpower moves wrenches, horsepower moves cars, and the power of the mind moves the world.
|
|
|
|
|
Hi...
Thanku soo much for ur reply...
|
|
|
|
|
Hi,
that is working when am calling directly in the code....but here i need ta call that as storedprocedure....so i have created that as stored procedure to fill a dropdown...
my doubt is how to pass this 'flaged' as parameter...(second parameter)
here is my code:-
Sub FillRID()
Try
Initialize("StoredProcedure", "SelectRequestID1new")
_cmd.Parameters.Add(New SqlParameter("@ApprovedStatus", "NotApproved"))
_cmd.Parameters.Add(New SqlParameter("@Flaged", ""))
_dr = _cmd.ExecuteReader()
DropDownRID.DataSource = _dr
DropDownRID.DataTextField = "ID"
DropDownRID.DataValueField = "ID"
DropDownRID.DataBind()
DropDownRID.Items.Insert(0, "Select RequestID")
DropDownRID.SelectedIndex = 0
Catch ex As Exception
End Try
End Sub
|
|
|
|
|
sorry i have got the solution....thanks....
|
|
|
|
|
hello sir
In my project i have one table in that table i have feild
UserId,StockName,status,CreatedBy,CreateDate,AssignHistoryCode ....
i want to edit one record in that table and insert new same record only
CreatedBy,CreateDate,and status are different.
so how is it possible
pls help me
its urgent.
Gayatri
|
|
|
|
|
no thanks
i get answer.
thanku
Gayatri
|
|
|
|
|
Can anyone tell me the difference between using # and ## when creating temp tables
Cheers
Bino
www.codepal.co.in
|
|
|
|
|
One is a global temporary table (visible to all users) and one is a local temp table (visible to your user only). I leave it to you to do some research and find out which one is which yourself. BTW - this info is readily available in Google and in Books Online.
|
|
|
|
|
Hi, I need a select query to get the column names of a table in the database.. basically, what I'm trying to do is:
IF NOT EXISTS (SELECT * FROM TableColumns WHERE Column = 'missingcolumn' AND Table = 'Tab')
ALTER TABLE Tab ADD missingcolumn [...]
I just need the correct select query.
Thanks
|
|
|
|
|
Nevermind:
select * from information_schema.columns where table_name = 'yourtablenamehere'
|
|
|
|
|
Dear Friends,
i want functionality in my Database as to stored the information of changes in any field or table which already define so i can set track of it.
can it possible in SQL server 2000? if it possible please give me direction and i want to managed this using .Net framework (version 2.0) so which system namespace use for this?
thanks in advanced.
Sasmi
|
|
|
|
|
Hi,
I am accpting date from the user in a form.
And i want to display the data in crystal reports month wise, in which i want the month names to be the column.
Ex:
SNo Name Amount Date Jan Feb Mar Apr .....
Is there any way by which i can achive this output by sql query...?
Deepak Surana
|
|
|
|
|
deepaks3 wrote: I am accpting date from the user in a form.
And i want to display the data in crystal reports month wise, in which i want the month names to be the column.
Ex:
SNo Name Amount Date Jan Feb Mar Apr .....
Is there any way by which i can achive this output by sql query...?
Try using the GROUP BY clause on Month(YourDateFieldHere) in your SQL query
|
|
|
|
|
By Using Group BY i will get the data in the form of rows, but i want each month amount in the corresponding month.
Deepak Surana
|
|
|
|
|
deepaks3 wrote: By Using Group BY i will get the data in the form of rows, but i want each month amount in the corresponding month.
You might also want to look up Pivot Tables and their usage...
|
|
|
|
|
ya that will solve my problem i guess..
thanks a ton
Deepak Surana
|
|
|
|
|
I'm developing a C++ program that should check whether SQL 2k5, Exchange 2k7, Oracle 10g are installed on the system. Based on whether one of it is installed the program shall invoke a different C++ function. The check shall be made by probing for certain registry keys/values by the C++ main program.
I would like to know which keys/values are set by the installation of
SQL 2k5
Exchange 2k7
Oracle 10g.
Also are there registy keys/values probing is foolproof enough to guarantee the successfull calling of the appropriate function?
On a sidenote are there any registry keys/values that are common and same for all the above three databases irrespective of different flavours. The idea is that the same registry check works for all the different version of a database.
|
|
|
|
|
Better thing to do is to probably try to ping each of the programs (SQl, Exchange, and Oracle), and if you get a response that you can connect, then they are there.
tom groezer wrote: any registry keys/values that are common and same for all the above three databases
Doubt it. Looking at the registry key will probably require searching for keys of each product.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Hi,
i have to generate "invoice_number" i,e (autonumber) with
combination of chars&numerics i,e br001,br002,br003 like this
iam using (ms-access)as my database...
please confirm me is it poosible to generate invoice number like above
with combination of char & num and it should be auto-incremented.
murali krishna
|
|
|
|
|
avvaru.murali wrote: i have to generate "invoice_number" i,e (autonumber) with
combination of chars&numerics i,e br001,br002,br003 like this
iam using (ms-access)as my database...
please confirm me is it poosible to generate invoice number like above
with combination of char & num and it should be auto-incremented.
Not with a single column. Assuming that you are outputting the data using a separate application then couldn't you just append the appropriate prefix in the code?
|
|
|
|
|
When I try to run my report from the ReportServer, I get the above-mentioned error, but it runs fine from the ReportManager, any idea?
Thanks.
Here's the full thing:
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly 'ReportingServicesWebServer' or one of its dependencies. The system cannot find the file specified.
Source Error:
Line 33: <sessionState mode="Off" />
Line 34: <httpHandlers>
Line 35: <add verb="*" path="Reserved.ReportServer" type="Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler, ReportingServicesWebServer" />
Line 36: <add verb="*" path="Reserved.ReportViewerWebControl.axd" type = "Microsoft.Reporting.WebForms.HttpHandler, ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
Line 37: </httpHandlers>
Source File: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config Line: 35
Assembly Load Trace: The following information can be helpful to determine why the assembly 'ReportingServicesWebServer' could not be loaded.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
