|
Hi,
Well, this is kind of embarrassing, i always used the GUI for altering the database tables in sql server 2005. Now, in sql server 2008, I need to write sql statements to do this.
I need to alter the existing column, and set it to not null with default value 0.
I got the not null part working, get error on default value.
alter table dbo.mytable
alter column [VendorLevelId] INT Not NULL Default 0
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Default'.
|
|
|
|
|
Well, that's odd! BOL[^] says you should be able to use alter column ... set default x to set the default value, but it doesn't seem to work.
Try using three statements:
ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;
UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;
ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here's an example from BOL (2k8):
CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
Straightforwardly:
SELECT * FROM dbo.doc_exy
Gets:
10
Now, doing the operation appears to suggest that no default value is ever needed:
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO
SELECT * FROM dbo.doc_exy
Shows what I mean:
10.00
DROP TABLE dbo.doc_exy ;
GO
|
|
|
|
|
Two of my OLAP databases disappeared in the last few days. Thankfully I had a backup.
However, I would want to investigate the root cause. How do I proceed?
|
|
|
|
|
Check who has access to the system, look at the audit logs etc.
Veni, vidi, abiit domum
|
|
|
|
|
There's also a chance that if you are not secured with regards to sql injection attacks - that you have just been the victim of one.
Dropping tables is a fairly classic symptom of an sql injection attack.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Does anyone have a code snippet that shows how to connect to a MariaDB database? Many of the examples I've found point to a MySql-compatible assembly. Is that the way you have to do it? I have no idea what assemblies to add to my references in a test project. I've not used MySql in donkey's years so I have absolutely no idea how to kick-start things using MariaDB.
Once I know what references to add and the syntax of a connection string particular to MariaDB's requirements I'll be okay from there.
TIA.
Edit: Forgot to mention I d/l the version 10.0 beta. Everything installed okay but I don't have any references visible to either MySql or MariaDB in a dotnet project.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
modified 12-Nov-13 10:07am.
|
|
|
|
|
the MySQLConenctor is normally a seperate installation from MySQL Connector[^]
once you download and install that usually you just add a reference to the project like you would do any normal installed .net library.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks a lot Simon. I downloaded it and ran the install. I added a reference to the assembly in the installation folder and I can see a shed-load of MySql classes. I'll wire one up to my test database and see if it can connect to it. So, I'm a step on the road with a way to go...
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I downloaded and installed the file that Simon provided at this link[^] (select the one called ADO.NET Driver for MySQL (Connector/NET). When you follow the download links the sly folks at Oracle/MySql try to make logging in to their website "mandatory" and it's easy to miss the tiny "No thanks, just start my download" link below it, so there's no need to register. When you've downloaded and installed it create a project and add an assembly reference to "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.7.4\Assemblies\v4.0\MySql.Data.dll" (obviously, select your Program Files location and the framework version as appropriate).
If you have MariaDB installed chances are you have the IDE tool, HeidiSQL installed. I used that to create a new session, database and a table. In HeidiSQL, the menu Tools -> User manager is where I created a user registration and pointed it to "home", 127.0.0.1 and I set the access rights to full, the usual suspects.
Here's a code fragment I slapped into a console app (apologies if it doesn't format nicely):
string connStr = "server=127.0.0.1;uid=DogzBolx;pwd=password;database=imagecatalog;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
string sql = "select * from data order by count desc;";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable data = new DataTable();
da.Fill(data);
foreach(DataRow row in data.Rows)
{
string md5 = Convert.ToString(row[0]);
int count = Convert.ToInt32(row[1]);
Console.WriteLine(md5 + " " + count);
}
conn.Close();
Excuse my glib database name. I ran the app and (bless my cotton socks) it worked! Anyway, it works fine, and it's rather jolly quick. Whether MariaDB can do what I'm familiar with using SQL Server I can't say but it seems to offer a lot of things. HeidiSQL itself is rather nice. I don't know if there are other tools to maintain MariaDB databases but seeing it's all part of the MariaDB-related family it's certainly works just fine. That's my $0.02c worth.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
modified 14-Nov-13 7:24am.
|
|
|
|
|
I need to use a function inside my database that need have the option Concat NULL yields NULL ON, however my database doesn't use that, so , I need to ensure that'll be ON when the function executes.
But seens not possible to define that inside the function ( Set .. On, is not allowed ).
There's any way to do that?
|
|
|
|
|
|
So code around it, ISULL(FieldName,'') will do for text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Microsoft have already stated[^] that the option to turn CONCAT_NULL_YIELDS_NULL off will be removed in a future version. You should probably be looking to update the database so that it works with this setting turned on.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yeah, we should start think about it here, thank you. The problem is that we're using that on the return of a XML. The error are on the XML return. to solve, before call the function we got to use this.
SET CONCAT_NULL_YIELDS_NULL ON;
GO
This is our function
CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML
IF @BreakLine = 1
BEGIN
SET @Text= REPLACE(REPLACE(REPLACE(@Text,'<br>', CHAR(13)), '<br/>',CHAR(13)), '<br />',CHAR(13))
END
ELSE
BEGIN
SET @Text= REPLACE(REPLACE(@Text, '<br/>','<br>'), '<br />','<br>')
END
SET @XML = CAST(('<x>' + REPLACE(REPLACE(dbo.Function_RemoveInvalidCharacters(@Text), '&', '&'), '<', '</x><x>')+'</x>') AS XML)
SELECT
@Text=
(
SELECT
CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
'<' + N.value('.', 'VARCHAR(MAX)')
ELSE
STUFF
(
N.value('.', 'VARCHAR(MAX)')
, 1
, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
, ''
)
END
FROM
@XML.nodes('x') AS T(N)
FOR XML PATH('')
, TYPE
).value('.', 'VARCHAR(MAX)')
RETURN LTRIM(RTRIM(@Text))
END
GO
I'm sorry if the code looks confuse, was made for a coworker here.
|
|
|
|
|
from microsoft access 2003 link SQLEXPRESS 2005 saw the other table normal, but i can not see the table in the form of DBO.* ? why is that ? no way to fix it? (Note that I'm using SQL Express 2005)
|
|
|
|
|
Member 2458467 wrote: in the form of DBO.* Is your user a sysadmin?
For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
If there are 4 attributes: Candidate name, Phone No., Address, Skills name. design 3 tables and write a sql query for sql server to display the candidate details on the basis of their skills name.
(For example in any job portal, searching the candidate details on the basis of their skills name)
I m new to sql server db, Please provide me the answer as soon as possible. Thank you.
|
|
|
|
|
Have you tried anything jet? Are you stuck with your solution?
Let see us your code - and probably fix it!
(Sorry but we are not here to do your homework! We have a job to do...)
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is (V).
|
|
|
|
|
You cut/pasted this from your assignment document, too lazy to even change the wording so it sounded genuine.
The people here are volunteers, mostly professional developers who are willing to help you to learn and benefit from their experience. As has been stated, do some research, try something and when you have a genuine problem come back and ask for help.
As for being new, getting the solution from a forum will insure you stay new and hopefully unused!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Member 10392875 wrote: Please provide me the answer as soon as possible.
Your answer is that you should do your own homework.
|
|
|
|
|
That would take me about ten minutes and I could do so in any of several different database systems.
You posted nearly a full day ago; what have you accomplished in that time?
|
|
|
|
|
I'm not going to do your homework, but I can point you in the right direction: Normalization and Joins .
|
|
|
|
|
Hi friends. I have 2 coulmns in a table (RealPersonID and CompanyID). They are foreign keys. My problem is that either of them is always 0 and the other one is none zero.
So i want to do that in only one query:
x=(Select CompanyID from Table1)
y=(Select RealPersonID from Table1)
if x!=0 (select companyName from Companies where companyID=x)
else (select personName from Person where personID=y)
|
|
|
|
|
Non tested!
Select IsNull(C.CompanyName, P.PersonName) Contact
From LinkTable L
Left Join ComapanyTable C on C.CompanyID = L.CompanyID
Left Join PersonTable P on P.RealPersonID = L.RealPersonID
This may help you understand joins Visual Representation of SQL Joins[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|