how to make new database sql server 2005!

i had no problems working whith sql server 2000
but whith 2005 i am unable to make new database!
question : HOW?
respond asap

You have two main options you can do it through the commad line tools sqlcmd (You should look at the books online, or run the sqlcmd /? for information on the syntax), Or you could install the sql server management tools for the SQL Express system. This is a free download that you can get from the MSDN Downloads site, or from the SQL Express home page on MSDN.

Of course if you do have the Visual Studio Express IDE's (any of them) Installed you can create database in them by using user instances. To do this go to your project and add a database file from the add item dialogs.

how to make Enforce Password Policy unchecked by default

I am trying to create a login using my application.

The application creates the user and password itself .

when working with sql server 2000 it works fine.

when i try to install my application with sql server 2005 it is displaying the error .

'Password does not meet windows policy requirements

because it is not complex enough'

I want to keep the same password.

Is there any way to disable/uncheck this option by default?

Prashant

You could modify the login creation script to specify CHECK_POLICY=OFF

USE [master]

GO

CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

|||

This is good but for this i need to make changes in my code .My application integrates with sql server 2000,MSAccess ,Oracle.

the code for creating login is same .So can it be possible at sql server level to make Check_policy =OFF as default option.

|||I would be very interested to know this as well. I have a similar issue. If your SQL 2005 server is not part of a domain that enforces group security policies, one option you do have is to relax the default Local Security Policy on the 2003 server upon which your SQL 2005 is running. Using the "Local Security Policy" editor found in Administive Tools you can select "Password Policy" under Account Policies. From there you can change the settings for history, age, length, complexity requirements, and encryption. You will want to turn off the "Password must meet complexity requirements". You might have to change some of the other settings to meet your specific application needs.|||

I would be interested in this as well.

We have a third party Application that the client says works with SQL Server 2005, but the user logon creation they have built in dos not pass a correct password, and turning the poicy off may fix the problem.

|||No, many features which are implemeted in the engine cannot be changed by default:

CREATE LOGIN(...)

CHECK_POLICY = { ON | OFF }

Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

Maybe you post a bug / feature request on the connect forums to make it happen in further versions.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

How to make corrupted DB working

Hi all!
About a month ago I had problems with SQL server, had to stop the sql server
process. After starting it I had to recover master DB. Everything was
looking good. But today I tried to grant DB access and found
sp_grantdbaccess missing.
After some experiments I found out a strange problem: I cannot create ANY
procedure on master DB (I have enough rights :) ).
Example: running script
CREATE PROC xxx AS SELECT 1
returns "There is already an object named 'xxx' in the database.", xxx is
any name.
This happens even on another SQL Server on db, that we backup/restored from
corrupted master db.
Any suggestions are welcomeThe correct way is to restore the last valid backup. If you don't have it,
you can try to rebuild the master database using Rebuildm.Exe command propt
utility (check for the syntax in Books OnLine). Then you will have to
recreate logins (maybe you can still generate scripts, so this task can be
easier?), attach all of the databases (check the sp_attach_db system SP) and
potentialy map logins to database users again (check the
sp_change_users_login for SQL logins and
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q298/8/97.asp&NoWebContent=1
for Windows logins).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:e7v2x8teDHA.1820@.TK2MSFTNGP10.phx.gbl...
> Hi all!
> About a month ago I had problems with SQL server, had to stop the sql
server
> process. After starting it I had to recover master DB. Everything was
> looking good. But today I tried to grant DB access and found
> sp_grantdbaccess missing.
> After some experiments I found out a strange problem: I cannot create ANY
> procedure on master DB (I have enough rights :) ).
> Example: running script
> CREATE PROC xxx AS SELECT 1
> returns "There is already an object named 'xxx' in the database.", xxx is
> any name.
> This happens even on another SQL Server on db, that we backup/restored
from
> corrupted master db.
> Any suggestions are welcome
>

how to make an alias here ?

Hi experts,
i am working with SQL-Server 2000 and have a special problem in one of my
stored procedures. To be as fast as possible i use a temp table defined as
variable. And i want to calculate a value in this table depending on values
in the same table. But i can't make it work. Here my problem:
-- can be copied into QueryAnalizer --
declare @.tbl table (
id1 int,
id2 int,
w1 int,
w2 int )
insert into @.tbl values (1, 2, 12, null)
insert into @.tbl values (2, 3, 10, null)
insert into @.tbl values (3, null, 7, null)
-- this is my wish. It doesn't work, because alias t1 is not valid, but i
think you can see what i want to do:
-- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0) from
@.tbl t2 where t2.id1 = t1.id2),0)
-- and this i all i can do, but with wrong result:
update @.tbl set w2 = w1 - IsNull((select IsNull(w1,0) from @.tbl t2 where
t2.id1 = id2),0)
select * from @.tbl
----
gives me:
1 2 12 12
2 3 10 10
3 NULL 7 7
and this is what it want to have:
1 2 12 2
2 3 10 3
3 NULL 7 7
I think, the problem is because in ... where t2.id1 = id2) the value of id2
is not the value of the atually updating record, it stays on id2 of the
last insert, which means, id2 is always NULL. And i can't use an alias (why
is an alias here not possible? This is not clear to me). What can i do?
thanks,
Helmuthelmut woess wrote:
> Hi experts,
> i am working with SQL-Server 2000 and have a special problem in one
> of my stored procedures. To be as fast as possible i use a temp table
> defined as variable. And i want to calculate a value in this table
> depending on values in the same table. But i can't make it work. Here
> my problem:
> -- can be copied into QueryAnalizer --
> declare @.tbl table (
> id1 int,
> id2 int,
> w1 int,
> w2 int )
> insert into @.tbl values (1, 2, 12, null)
> insert into @.tbl values (2, 3, 10, null)
> insert into @.tbl values (3, null, 7, null)
> -- this is my wish. It doesn't work, because alias t1 is not valid,
> but i think you can see what i want to do:
> -- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
>
update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
from @.tbl t2 where t2.id1 = t1.id2),0)
from @.tbl t1
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Am Thu, 15 Dec 2005 09:16:21 -0500 schrieb Bob Barrows [MVP]:

> helmut woess wrote:
> update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
> from @.tbl t1
Incredible, how fast you came up with the solution!
thousand thanks, Bob, you saved me a lot of time!
Helmutsql

How to make a "SQL Server" connection with ASP.NET

Hi.

Working with ASP this connection works:

stringMyConn = "dsn=foo.com.bar;uid=john;pwd=xxxx;"
set myConn = Server.CreateObject("ADODB.Connection")
myConn.open stringMyConn

But it doesn't with ASP.NET

Dim myConn As SqlConnection = New SqlConnection("dsn=foo.com.bar;uid=john;pwd=xxxx;")
myConn.Open

What am I doing wrong? Thank you very much.

Since you are connecting to SQL Server, I don't recommend that you use a DSN connection. You can connect to SQL server as follows:

Dim myConn As New SqlConnection("server=;database=;user id=;password=;")
Dim myCmd As New SqlCommand("SELECT * FROM SOMETHING", myConn)

myConn.Open()

' ... Some code here to do something with the SqlCommand object

myConn.Close()

For future reference, a good source for connection strings isConnectionStrings.com.

How to log any activity

Dear friends,
I'm working on an entity relationship diagram about a mice farm.
Let's say we have a very very basic diagram like this:

MOUSE "is in" CAGE

For example, this gives

Mouse "n1500" is in cage "AAA"
Mouse "n1501" is in cage "BBB"

One operator can take one mouse and put it into another cage, for
example the first line becomes

Mouse n1500 is in cage CCC

Now, I was asked to track any movement... I ask myself: do I need to
add

OPERATOR "moves" MOUSE

so that I have

Operator "John" moves Mouse "n1500" on "Monday 3rd, 2007" at "5 PM"
from "AAA" to "CCC"?

or this kind of logging is provided some way by SQL server?

Thanx a lot for any hint.Not automatically.

You could add a trigger and an audit table to your database, so that every time a record is inserted updated, the pertinent data would be logged to the audit table.

ref Using TRIGGERS to add an audit trail to a table (http://www.xtremevbtalk.com/showthread.php?t=217047)

Note that the example shown doesn't audit the data which was changed, but that is easy to add.

Note: Since you're asking specifically about SQL Server, I'm moving this thread there (to the SQL Server Forum).

How to lock out everyone but me?

I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.

I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."

What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:

sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"

and see the contents of my_table.

What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?

Have you tried simply dropping the builtin\administrators login?

"Drop login [builtin\administrators]"

You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.

Sung

|||

I think the right expression would be to deny connection to builtin administrator under normal circumstances.

I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.

You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.

If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.

BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine

How to Localize Prompt String in Reporting Services

Hi,
I am working on SQL Reports using Microsoft SQL Server 2005 Reporting
Services.
These reports are intended to deploy in multilingual environment, for
that purpose, each and every thing has to be localized based on
culture.
1- I am able to localized the field labels by loading custom assembly
using expression .
2- But unable to localize the prompt strings.
3- In Report Designer, Report->Report Parameters, the text given for
prompt does not evaluate the expression, if given "=User!Language", it
prints as it is, next to the prompt for respective parameter.
Is there any way or workaround to localize the prompt string at
runtime?
Thanx in advance,
HariomOn 8 loka, 17:28, hariompandey...@.gmail.com wrote:
> Hi,
> I am working onSQLReports using MicrosoftSQLServer2005Reporting
> Services.
> These reports are intended to deploy in multilingual environment, for
> that purpose, each and every thing has to be localized based on
> culture.
> 1- I am able to localized the field labels by loading custom assembly
> using expression .
> 2- But unable tolocalizethepromptstrings.
> 3- InReportDesigner,Report->ReportParameters, the text given forpromptdoes not evaluate the expression, if given "=User!Language", it
> prints as it is, next to thepromptfor respectiveparameter.
> Is there any way or workaround tolocalizethepromptstring at
> runtime?
> Thanx in advance,
> Hariom
Hi Hariom,
Our company has customers all around the world. The localization
support in Reporting Services 2005 is not so good at the moment. Like
you did we also built a custom assembly to localize report fields.
We even built a custom HTTP module to localize the Report Manager to
any language (Microsoft provides few satellite assemblies to localize
it for few languages but not nearly enough, and you can't create your
own assembly, I asked Microsoft Gold partner support about it).
So what we do is that we go through each component on the web page and
check using reflection whether it has a Text property. If it does we
get the text and replace it with another text in another language.
This works great for Report Manager application.
Unfortunately this approach does not work with report parameter prompt
texts. Even if you change the Text property of these components, they
still render the text that is defined in the RDL for the prompt.
So to answer your question, after very extensive search we have not
found any way to localize them at runtime. We had to build a tool that
translates RDL files to different languages.
Best regards,
Juho Salo

How to load jpeg file in SqL2000 and how to retrieve from SQL2000.

Hi friends
Now I am working in SQL2000 as back end .I want to load jpeg file in database and retrieve from database. Please guide me.You can use this:

CREATE TABLE Images ([stream] [image] NULL)
insert into Images ([stream]) values (@.image)

Hope this helps!!
Deven.

How to load database name at runtime?

I m creating a report using data view.
its working fine. BUt i want to load db name at run time, I tried a sample and which does it very fine.

But when i use the same code in my project, its not loading the database name at runtime, old name is being used.

What i m doing is like this in visual basic.net

rptCustomersOrders.Load("..\CustomerOrders.rpt")

' Set the connection information for all the tables used in the report
' Leave UserID and Password blank for trusted connection
For Each tbCurrent In rptCustomersOrders.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

ReportViewer.ReportSource = rptCustomersOrders

Plz. tell me ; what property i have to set, to load the database name at runtime.

Thanks in adavancehttp://www.dev-archive.com/forum/archive/index.php/t-293276.html|||try adding

.location = .name

after

.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"

so that it will erase previous location details ....

if u have any other methords ... plzz let me know too dude .........|||Here's my code which you can use to change Database Name, User Name, Password and SQL Server Name at run time. This code is written in VB6 and works with Crystal Reports 10.

Copy this code in a Module in VB and I used frm Report where I have placed crystal report viewer control.

Public Sub DisplayReport(ReportFileName As String)
Dim app2 As CRAXDRT.Application
Dim rap As CRAXDRT.Report

Set app2 = New CRAXDRT.Application
Set rap = New CRAXDRT.Report
Set rap = app2.OpenReport(ReportFileName)

rap.EnableParameterPrompting = False

For Each CRXDatabaseTable In rap.Database.Tables
STORED_DATABASE_NAME = CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") ''JUST TO READ DATABASE NAME STORED IN CRYSTAL REPORT FILE
Exit For
Next

For Each CRXDatabaseTable In rap.Database.Tables
CRXDatabaseTable.ConnectionProperties("Data Source") = SQLServerName
CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") = DatabaseName
CRXDatabaseTable.ConnectionProperties("USER ID") = UserName
CRXDatabaseTable.ConnectionProperties("PASSWORD") = LoginPassword
If Not CRXDatabaseTable.TestConnectivity Then
MsgBox "Error connecting to database table." & vbCrLf & "Please contact Adminstrator to validate Report Database"
Exit Sub
End If
Next

rap.EnableParameterPrompting = True
On Error Resume Next
Ret = rap.SQLQueryString 'THIS WILL CALL USER TO INPUT PARAMETERS FOR REPORT (IF ANY)
On Error GoTo 0

If Err.Number = -2147206395 Then 'CHECK IF USER PRESSED CANCEL
Err.Clear
Exit Sub
ElseIf Err.Number <> 0 Then 'CHECK IF ANY OTHER ERROR OCCURED
MsgBox Err.Number & " : " & Err.Description
Err.Clear
Exit Sub
End If

If UCase(STORED_DATABASE_NAME) <> UCase(DatabaseName) Then
''IF DATABASE WHILE CREATING REPORT WAS DIFFERENT THEN THE CURRENT DATABASE.
''UPDATE DATABASE NAME IN CONNECTION STRING, ELSE CONNECTION STRING READS DATA FROM DATABASE USED WHILE CREATING REPORT
DoEvents
rap.EnableParameterPrompting = False
Ret = rap.SQLQueryString
Ret = Replace(Ret, STORED_DATABASE_NAME, DatabaseName)
rap.SQLQueryString = Ret
rap.SQLQueryString = Ret ''IT DOESNOT WORK IF I SET THIS ONCE (PARAMETERS ARE NOT SET). :-( CRYSTAL BUG
DoEvents
End If

frmReport.Show
frmReport.CrystalActiveXReportViewer1.ReportSource = rap
frmReport.CrystalActiveXReportViewer1.ViewReport
frmReport.CrystalActiveXReportViewer1.Refresh
End Sub|||Sorry forget to add this declaration in my above function:

Dim CRXDatabaseTable As CRAXDRT.DatabaseTable

How to known that Sqlconnection is working

Hello all,
Actually i want to known, is their any method or property in SqlConnection class which will return some value, so that through which we become confirm that connection has established.

Thanks in advance!Look at the State property of the connection object|||very thanks to you , i think this is what i am looking for.

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
Fluido
You can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.c om...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
FluidoYou can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.com...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
FluidoYou can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.com...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido