Re: View might cause corruption? - Database Discussion Boards

Re: i want to do left join that should not return same multiple rows

Michael Potter15-Jun-12 10:43

15-Jun-12 10:43

Not sure why you have the second join to the history table. If each upload has a unique user and a unique history row, then user_id in the history table is useless information for this query. The second history join is giving you the redundant rows.

Try and avoid using DISTINCT and its variations. It requires the server to do more work and therefore slow down your query.

SQL

SELECT 
    US.name, 
    US.address, 
    UP.file_path, 
    UP.picture_path, 
    HS.location
FROM 
    [user] US
LEFT JOIN 
   uploads UP 
   ON (US.user_id = UP.user_id)
LEFT JOIN 
   history HS 
   ON (US.upload_id =  HS.upload_id)
WHERE 
   US.user_id = 5
ORDER BY 
   US.user_id

modified 15-Jun-12 16:51pm.

Finding "Missing" ID Numbers in Table (SQL Server 2005)

Peter Bamuhigire13-Jun-12 7:25

Peter Bamuhigire

13-Jun-12 7:25

I have a small programming problem, am requesting Your insight I don't know whether this should be in the C# forum or in the Database Forum, but since it looks more likely a Database thing, I'll ask it Here.
I have an application that is written in C# and has a Backed Database in SQL Server 2005. In Summary the application manages info on a company's distributors and their sales.
In the table, Distributors I have a Primary Key Column DistributorIDNO (char (8)) which stores their IDs in the formart SL000001 (up to a logical maximum of SL999999).
Different branches of the company are allocated number ranges to register new distributors, for example, the range SL002000 to SL002500 may be given to the office in Masaka (a town in My country), and at the Head Office they would continue to register others From SL002501 onwards).
Now, the people in Masaka may not fill up their quarter, which means between SL002000 and SL003000, some numbers are taken up, while others are not.
I would like to write a function that should look through the database and determine which numbers, between a particular range, have not been registered.

Peter

.NET Developer, loved programming from the day I looked at Basic code for the DOS Game "Snake". Learning doesn't stop.

Re: Finding "Missing" ID Numbers in Table (SQL Server 2005)

Mycroft Holmes13-Jun-12 14:45

Mycroft Holmes

13-Jun-12 14:45

This is NOT a good design for your primary key, a PK should be stupid and not convey any information.

Create a table variable and fill it with all possible numbers between the range you want to test, then select from that table where the PK is not in your Distributor table.

SQL

DECLARE
	@Top INT,
	@bottom INT
	
SET @bottom = 2000
SET @Top = 6000

DECLARE
	@Count INT,
	@Str VARCHAR(20)
DECLARE 	@Tbl TABLE(Code VARCHAR(20))	
	
	
SET @Count = 1
WHILE @Count < @Top
BEGIN
	SET @Str = 'SL' + RIGHT('000000000'+CAST(@Count + @bottom AS VARCHAR(10)),8)
	INSERT @Tbl(Code) VALUES (@Str)
	
	SET @Count = @Count + 1

END

SELECT Code
FROM @Tbl		
WHERE Code NOT IN (SELECT PD FROM TableBame)

Never underestimate the power of human stupidity
RAH

Re: Finding "Missing" ID Numbers in Table (SQL Server 2005)

Eddy Vluggen14-Jun-12 0:59

Eddy Vluggen

14-Jun-12 0:59

Does the term "SLOO" change? If not, then your key is built from multiple facts, and should be split into multiple columns. A simple identity-field would do nicely as a surrogate key, as suggested by MyCroft.

Bastard Programmer from Hell Suspicious | :suss:

View might cause corruption?

kmoorevs13-Jun-12 5:35

kmoorevs

13-Jun-12 5:35

My company's reporting software combines data from multiple sources such as point of sale, inventory, accounting, payroll, etc.. Where possible, we provide our customers with a direct connection interface against the datasource, usually SQL Server. All we require is read only access to the vendor database. A long time customer recently switched to a new inventory system. Our connection to this system uses a view created in another sql server database (usually the master) residing on the same server/instance. The customer asked the IT department for help in getting the view created, and setting up a readonly sql server login to pull the data. This started a lot of questioning, and the next thing I know, the customer forwards me a response from the inventory vendor advising against using this view against their database. Why? Without quoting, they stated that due to the possibility of upgrades or structure changes, there was a possibility of corruption to the source tables. It is my understanding that by design, there is zero chance of a view causing corruption to the source tables. At worst, it would fail or produce incorrect results. I know I don't know what I don't know, which is why I am asking here. (and Google isn't coming up with any usable results) Am I wrong? To me this just seems like a scare tactic from the other vendor to charge for custom work.

"Go forth into the source" - Neal Morse

Re: View might cause corruption?

David Mujica13-Jun-12 5:56

David Mujica

13-Jun-12 5:56

Having worked for a software vendor in the past, I'm leaning toward scare tactic.

The vendor doesn't want to deal with potential performance problems / data inconsistency, etc, so they are covering their A**.

One way to handle this is ask the vendor what is their "supported" integration method?

You can always build the view against the vendor's recommendation, but look at your support contract and it might put a monkey wrench in the works.

Re: View might cause corruption?

Pete O'Hanlon13-Jun-12 5:57

Pete O'Hanlon

13-Jun-12 5:57

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.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: View might cause corruption?

Corporal Agarn13-Jun-12 5:57

Corporal Agarn

13-Jun-12 5:57

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.

Re: View might cause corruption?

Mycroft Holmes13-Jun-12 14:52

Mycroft Holmes

13-Jun-12 14:52

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

Re: View might cause corruption?

kmoorevs14-Jun-12 4:20

kmoorevs

14-Jun-12 4:20

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

MySQL

klaydze12-Jun-12 21:54

klaydze

12-Jun-12 21:54

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;
}

Re: MySQL

Bernhard Hiller12-Jun-12 23:34

Bernhard Hiller

12-Jun-12 23:34

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:

SQL

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.

Re: MySQL

klaydze13-Jun-12 1:33

klaydze

13-Jun-12 1:33

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;
}

Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.12-Jun-12 13:55

Fernando A. Gomez F.

12-Jun-12 13:55

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:

SQL

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:

SQL

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.

Re: Remove a noise word in a SQL Server 2008 database

Bernhard Hiller13-Jun-12 0:00

Bernhard Hiller

13-Jun-12 0:00

The "stop words" are in a system view.

SQL

SELECT [stopword], [language_id]
  FROM [master].[sys].[fulltext_system_stopwords]
  where language_id=3082

I tried a DELETE query:

SQL

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...

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:05

Fernando A. Gomez F.

13-Jun-12 5:05

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! Smile | :)

Re: Remove a noise word in a SQL Server 2008 database

Eddy Vluggen13-Jun-12 1:24

Eddy Vluggen

13-Jun-12 1:24

Those are stored as plain text-files Smile | :)

See MSDN[^].

Bastard Programmer from Hell Suspicious | :suss:

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:06

Fernando A. Gomez F.

13-Jun-12 5:06

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!

Re: Remove a noise word in a SQL Server 2008 database

Eddy Vluggen13-Jun-12 5:48

Eddy Vluggen

13-Jun-12 5:48

Fernando A. Gomez F. wrote:
MSSQLSVR 2008

I should have read more carefully.. As I read, you did find 'em by now Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Re: Remove a noise word in a SQL Server 2008 database

Bernhard Hiller13-Jun-12 4:40

Bernhard Hiller

13-Jun-12 4:40

Eddy Vluggen supplied an important hint - but the version is different. Look at http://msdn.microsoft.com/en-us/library/cc280871%28v=sql.100%29.aspx[^]

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:07

Fernando A. Gomez F.

13-Jun-12 5:07

Ah, yes, that did it! Find 'em yesterday, very late at night, when I was cursing the DB builders. Laugh | :laugh:

Thanks, cheers!

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:08

Fernando A. Gomez F.

13-Jun-12 5:08

Why the heck is "salud" (health) a noise word, escapes my comprehension though.

SQL Compound Primary Keys

Kevin Marois12-Jun-12 11:36

Kevin Marois

12-Jun-12 11:36

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

Re: SQL Compound Primary Keys

PIEBALDconsult12-Jun-12 16:45

PIEBALDconsult

12-Jun-12 16:45

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.

Re: SQL Compound Primary Keys

Kevin Marois12-Jun-12 18:13

Kevin Marois

12-Jun-12 18:13

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.