|
The only time I would even think that a view could cause you trouble is something called a Materialised view, and that's an Oracle thing - not a SQL Server one. Sounds like a scare tactic to me.
|
|
|
|
|
A view in SQL Server can corrupt the underlying table, as changes to the view will change the table. However, your explanation seems to take care of that (read only user, etc.).
I have not worked with views for a while so I do not remember all the what will happens. Good luck.
|
|
|
|
|
Name and shame the vendor, they are feeding you a line of bullshit. Your understanding is absolutely right, a read only connection to the database cannot corrupt the data. It can seriously affect performance if the view is badly designed but then you already know that!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the feedback! The other vendor acknowledged that the statement was incorrect and will be providing the customer with the resources required (a readonly login + new views) to extract the requested information. Furthermore, they are making these changes standard for all of their customers...a resolution that works for everybody.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Hi All,
Can someone explain why MySql treat the two sql statement below different?
First Query (without inner join)
MySqlConnection conn = new MySqlConnection("server=localhost;database=db_test;uid=root;password=1234567");
MySqlCommand dbCommand = new MySqlCommand("select * from useraccess_privileges", conn);
dbCommand.CommandType = CommandType.Text;
if (dbConn.DBConnect.State == ConnectionState.Closed)
dbConn.DBConnect.Open();
DataTable dtData = new DataTable();
dtData.Load(dbCommand.ExecuteReader());
Result: dtData will return 7 row count
Second Query (with inner join)
MySqlConnection conn = new MySqlConnection("server=localhost;database=db_test;uid=root;password=1234567");
MySqlCommand dbCommand = new MySqlCommand("select t1.*, t2.MenuName from user_access t1 inner join useraccess_privileges t2 on t1.UserName = t2.UserName", conn);
dbCommand.CommandType = CommandType.Text;
if (dbConn.DBConnect.State == ConnectionState.Closed)
dbConn.DBConnect.Open();
DataTable dtData = new DataTable();
dtData.Load(dbCommand.ExecuteReader());
Result: dtData will return 1 row count
Second query should return 7 rows also since i just reference it to other table to get the full name of the specific username.
Thanks
if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}
|
|
|
|
|
Because user_access contains only one row matching useraccess_privileges. useraccess_privileges has 7 rows, user_access has 1 or more rows.
Try a LEFT JOIN instead:
SELECT useraccess_privileges.MenuName, user_access.*
FROM useraccess_privileges
LEFT JOIN user_access
ON user_access.UserName=useraccess_privileges.UserName
and you'll get again 7 rows.
|
|
|
|
|
Hi Bernhard,
Yep, i already did that but no luck. I don't understand why it return only 1 row.
The work around that I did was like this.
DataTable dt = new DataTable();
MySqlCommand command = new MySqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT useraccess_privileges.MenuName, user_access.* FROM useraccess_privileges LEFT JOIN user_access ON user_access.UserName=useraccess_privileges.UserName";
MySqlDataAdapter da = new MySqlDataAdapter(command);
da.SelectCommand.Connection = conn;
da.Fill(dt);
If the above code was applied then it will return the correct row number. I really don't understand why.
Thanks
if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}
|
|
|
|
|
Hello all. I created a DB with a single table, with two columns enabled for full-text search. Everything works fine. However, whenever I lookup for a word ("salud", with means "health" in Spanish), it throws no results:
select id, vision, age, gender, [state], tier1
from visions
where contains(tier1, 'Salud')
Frustrated with this, I started looking on the causes. I came across an article that suggested to run a parse on the word, and so I did:
SELECT * FROM sys.dm_fts_parser (' "Salud" ', 3082, 0, 0)
This returned the following record:
0x00730061006C00750064 1 0 1 Noise Word salud 0 Salud
So, to my dismay, "salud" is a Noise Word. I've been trying to search how to stop "salud" from being a noise word, but all I find is reference to some noisees files, which I'm unable to find in my SQL Server 2008 installation.
Thus my question: does anybody know where can I remove "salud" from being a noise word? Any clue on this matter will be reaaaally appreciated!
Thanks in advance. Best regards.
|
|
|
|
|
The "stop words" are in a system view.
SELECT [stopword], [language_id]
FROM [master].[sys].[fulltext_system_stopwords]
where language_id=3082
I tried a DELETE query:
delete
FROM [master].[sys].[fulltext_system_stopwords]
where [stopword]='salud' and language_id=3082
but that caused an exception:
"Ad hoc updates to system catalogs are not allowed"
I do not have further ideas...
|
|
|
|
|
Hi, thanks so much for your help!
I finally managed to remove the noise words. It seems that MSSQLSVR2K8R2 (Uf!) has hardcoded somewhere (apparently some "resource" database, whatever that is) the noise words for each language. So, what I did was:
1.- Create my own stoplist[^]
2.- Through the SQL Management Studio, open the full-text index properties and then select the recently created stoplist rather than the default.
3.- Alter[^] the stoplist and drop the noise word.
4.- Re-index the full-text index.
Thanks again for your help! 
|
|
|
|
|
Those are stored as plain text-files 
See MSDN[^].
Bastard Programmer from Hell 
|
|
|
|
|
Yeh, tried looking for those files, yet didn't find 'em on MSSQLSVR 2008. I found the Thresaurus, mind you, but not the words. I read in MSDN that in 2008 they were stored in the "Resources" database, yet I wasn't able to find 'em.
Thanks for the insight!
|
|
|
|
|
Fernando A. Gomez F. wrote: MSSQLSVR 2008
I should have read more carefully.. As I read, you did find 'em by now
Bastard Programmer from Hell
|
|
|
|
|
|
Ah, yes, that did it! Find 'em yesterday, very late at night, when I was cursing the DB builders. 
Thanks, cheers!
|
|
|
|
|
Why the heck is "salud" (health) a noise word, escapes my comprehension though.
|
|
|
|
|
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical.
Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)
CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)
You can see the Operator and Facility Id's. They won't work once sent to the server.
I'm not sure how to define compound keys. Can someone point me in the right direction?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
That is a big reason not to use integers as keys.
You may need to rethink your whole application.
I mainly wonder why the clients would be adding records to these tables.
However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.
|
|
|
|
|
PIEBALDconsult wrote: You may need to rethink your whole application.
I mainly wonder why the
clients would be adding records to these tables.
What if the client doesn't have a network connection? Where would the store the data. This application cannot have down time in the event of a lost connection. So the app will have a local data store and update the server periodically.
So, since new records can be added in the client's DB, I need to make sure the PK's are unique.
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
Yes, but why would the client be adding records to those tables?
Kevin Marois wrote: PK's are unique
Globally unique?
|
|
|
|
|
I don't understand why you're questioning this.
The software is installed locally on a user's PC, along with a local copy of the DB. The software does CRUD operations on the local copy of the DB.
Then, at night, or at least once per week, the software uploads all new/changed/deleted records to the server.
If it's not broken, fix it until it is
|
|
|
|
|
PIEBALDconsult wrote: That is a big reason not to use integers as keys.
Yeah, people being too lazy. The problem goes away with GUIDs, since that allows for laziness.
Bastard Programmer from Hell 
|
|
|
|
|
Kevin Marois wrote: and each night will upload its data to the server
Is this a oneway communication where the data is only uploaded, or will it synchronise the data with the server?
If it's just an upload you don't need to have global keys, you can add new keys at the server when uploading.
|
|
|
|
|
There will be two-way communicaation
If it's not broken, fix it until it is
|
|
|
|
|
I don't see a key, only an identity-field. If that identity is the key, and you need a quick solution, add a second column with a GUID and promote that to be the primary key.
I'd recommend to have a "real" key; take those columns that uniquely identify a record, and use those as a key, and the identity merely as an alternative shortcut to that.
Bastard Programmer from Hell
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
