|
try
select columnName as S_NO from tablename
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
You're real problem is that you are putting UI information into the query, this type of formatting belongs in your user interface no the database
Oh and this does work in sql server 2005/08
SELECT ship [S. No.] FROM Ship AS S
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding quotation marks around the alias:
Select S_no AS "S. No." From Emp;
|
|
|
|
|
In SQL query '.' can be used to get a column from a table...
For eg : "Select Employee.EmpId from Employee"
So Sql always expects a column name after a '.' , use some other characters as alias names.
This would solve your problem
|
|
|
|
|
Use a valid identifier. Dot is not a valid identifier.
|
|
|
|
|
read complete summery of this question. Do't waste your time in Closed Questions.
If you can think then I Can.
|
|
|
|
|
Let's say I have a publisher and subscriber database set up. If I want to say, add a column to a table in publisher, will the change automatically be replicated to the subscriber database? Or is that only for data?
"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson
|
|
|
|
|
Modifications to the structure can also be replicated from publisher to subscribers but with several limitations. In short you can make 'minor' changes to the tables in publisher database and if those tables are included in a publication, modifications are repeated at subscribers. For more detailed information I think you should read through this: http://technet.microsoft.com/en-us/library/ms151870.aspx[^]
|
|
|
|
|
Exactly the link I needed, I wish I had found it on my own. Thank you for your time.
"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson
|
|
|
|
|
You're welcome
|
|
|
|
|
Hi there,
I'm trying to create a database programmatically using .Net3.5 and C#. I have this nice SQL-script which works fine in the SSMS editor, but now to try it for real in C#. I learned that the GO-statements were only for the SSMS editor, so I removed those. (am I right on that one?). What am I missing here?
Context: MSSQL 2008R2, VS2008, .Net3.5, WindowsXP.
Below is my script. The error I get is the following:
Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.
The source I use to execute this script is here. (removed the try/catch/finally blocks etc)
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string initDBQuery = string.Format(InstallationHelper.CreateDBScript(), settings.DatabaseLocation, settings.Database, settings.DatabaseUser, settings.DatabasePassword);
SqlCommand command = new SqlCommand(initDBQuery, conn);
command.ExecuteNonQuery();
This is the script (in C#-form):
string script =
@"
CREATE DATABASE [{1}] ON PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB );
LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
ALTER DATABASE [{1}] SET COMPATIBILITY_LEVEL = 100;
ALTER DATABASE [{1}] SET ANSI_NULL_DEFAULT OFF ;
ALTER DATABASE [{1}] SET ANSI_NULLS OFF ;
ALTER DATABASE [{1}] SET ANSI_PADDING OFF ;
ALTER DATABASE [{1}] SET ANSI_WARNINGS OFF ;
ALTER DATABASE [{1}] SET ARITHABORT OFF ;
ALTER DATABASE [{1}] SET AUTO_CLOSE OFF ;
ALTER DATABASE [{1}] SET AUTO_CREATE_STATISTICS ON ;
ALTER DATABASE [{1}] SET AUTO_SHRINK OFF ;
ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS ON ;
ALTER DATABASE [{1}] SET CURSOR_CLOSE_ON_COMMIT OFF ;
ALTER DATABASE [{1}] SET CURSOR_DEFAULT GLOBAL ;
ALTER DATABASE [{1}] SET CONCAT_NULL_YIELDS_NULL OFF ;
ALTER DATABASE [{1}] SET NUMERIC_ROUNDABORT OFF ;
ALTER DATABASE [{1}] SET QUOTED_IDENTIFIER OFF ;
ALTER DATABASE [{1}] SET RECURSIVE_TRIGGERS OFF ;
ALTER DATABASE [{1}] SET DISABLE_BROKER ;
ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ;
ALTER DATABASE [{1}] SET DATE_CORRELATION_OPTIMIZATION OFF ;
ALTER DATABASE [{1}] SET PARAMETERIZATION SIMPLE ;
ALTER DATABASE [{1}] SET READ_WRITE ;
ALTER DATABASE [{1}] SET RECOVERY SIMPLE ;
ALTER DATABASE [{1}] SET MULTI_USER ;
ALTER DATABASE [{1}] SET PAGE_VERIFY CHECKSUM ;
USE [{1}];
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [{1}] MODIFY FILEGROUP [PRIMARY] DEFAULT;
";
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
Helfdane wrote: I learned that the GO-statements were only for the SSMS editor, so I removed
those. (am I right on that one?).
Yap, that's correct.
Helfdane wrote: Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.
The semicolon is used to end a statement so in your database creation statement the LOG portion is part of the CREATE DATABASE statement. Try removing the extra semicolon from the first row:
CREATE DATABASE [{1}] ON PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
Haven't tried to execute a script using SqlCommand but hopefully it'll work. If it doesn't, you should split the statements to separate executions. Just a thought: From error-handling point of view it could better to use separate executions.
|
|
|
|
|
You're a lifesaver! Thanx a million!
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
No problem
|
|
|
|
|
Yeah, what he said, plus I don't think the USE statement will work.
|
|
|
|
|
A while back I was trying to do the same, and several helpful souls told me it can't be done. Well, that just gets my dander up, so I went ahead and did it:
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
String str;
SqlConnection myConn = new SqlConnection
("Server=BAAL\\SQLEXPRESS2;Integrated security=SSPI ;database=master");
str = "CREATE DATABASE MyDatabase";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("Database Created Successfully", "MyApp",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyApp", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
}
I ran it once, then switched over to the server to check - sure enough, myDatabease was there, ready to be populated.
Your app will certainly need more, but it works, and should get you started.
Will Rogers never met me.
|
|
|
|
|
Thanks!
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
Hi,
I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though,
1. Search single user activities
2. Search all user activities
|
|
|
|
|
Just create one single table.
If you create a table per user the database will be unmaintainable.
Wout Louwers
|
|
|
|
|
Single table
This will do for now
|
|
|
|
|
Separating the tables per-user is not necessary. Make sure that you specify that the user ID is an index and the RDBMS will do all of the heavy work for you - making sure that internally the table is structures in such a way that queries such as:
select * from audit where UserID = 'someuserid'
are very rapid. It would be well worth examining the documentation on CREATE INDEX:
http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]
Careful usage of features such as these will give a much more performant solution that trying to roll your own equivalent (by separating into tables per user).
|
|
|
|
|
I think perhaps you replied to the wrong post?
This will do for now
|
|
|
|
|
eight wrote: 1. Search single user activities 2. Search all user activities
I don't see any reason to put them in separate tables. If it's the same data, with only a single different property, then it should go in the same table and the extra property becomes part of the key.
I are Troll
|
|
|
|
|
|
I'm upgrading a MSDE SP4 Database consists of Tables containing Blob objects to SQL 2008 Express R2, during upgrade it goes on fine,and works after upgrade, but and after a while i get this microsoft error report.
Some unexpeceted errors have happened on the software you recently used.You were not asked to send the error reports at the time they occured.
Error Signature
Event Type : sqlexception001
Any clue ?
|
|
|
|