|
Eddy Vluggen wrote:
Bram van Kampen wrote: and do the SQL Constructs make
sense for a pure SQL database.
It uses SQL, but I don't know how the
word "pure" fits in.
Well, 'Pure' fits in as follows:-
SQL stands(as you know) for 'Standard Queery Language', and was as such originally designed and intended to formulate queeries to a Database in a uniform way, for mutualy incompatible Databases in say DBase, Access, etc. I.E. it was an abstraction layer to create a common access method to Databases in various proprietary binary formats .
MS SQL implements this abstraction layer, but can also be used to act as DB propper. That is: one can create a DB complete with tables and colums from scratch in SQL, without also specifying seperately an underlying Database (This is a step further than intended in the 1980's). This later concept is what I describe, (for want of a better word) as a'pure' SQL database. 'Pure' in the sense that to the user only SQL is involved, and no other underlying database.
I understand from your answer that the connection string would be different. I extrapolate from the above that, provided Tables and Colums are identical in type and name, that the transaction and queery strings should not need changing when stepping back from Access to 'Pure' SQL.
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: I extrapolate from the above that
Wrong assumption. Although you can force both to use SQL92, it's not optimal. Both have their own SQL-dialect, and there's a little difference in adding parameters to the IDbCommand.
Still, I like Access for prototyping Sql-server database; one can build a UI quickly around the tables, and they're easily copied to a USB-stick. There's a Wizard somewhere in there that "upgrades" the database to Sql Server-format, so once it works you can migrate all the tables easy from Access. The wizard can't translate all, e.g., there's no UI-service in Sql Server. It also doesn't translate reports, but tables and views, yes.
If you want to be able to use the Access-reports with the Sql Server database, that's possible too - by linking the tables from Access and installing the free Access Runtime[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
Back to the vexed old question, 'How to connect'
Eddy Vluggen wrote: Which credentials did the Management Studio use to connect to the database?
Well, I was running VS 2012 R2 and DB Management Studio in my Windows User Account(As per Standard Windows 7 Installation). No further questions were asked by the system. It appears that I can there create new db tables, and even populate them, but, cannot read or otherwise use them. Was this security system per chance written by 'Franz Kafka'?
Eddy Vluggen wrote: Yes, but that's local to your machine - the database-server has it's own
security. There's two modes; first there's Windows Authentication, which takes
the user that's currently logged into Windows to authenticate (and is DBO when
creating a table). One doesn't need a password in that case, since you already
provided a username/password when logging into Windows. That's the preferred
way. Alternative, there's "mixed mode", where SQL accepts a username/password
combination (with "sa" usually being the name of the admin account).
In this case, the Databaseserver IS the local machine.
I have since reset the database to 'Mixed Mode', and changed the password vor the 'sa' account to '12345'. I havechanged the connection string to:
SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"User ID=sa;Password=12345;" +
"Trusted_Connection=true;" +
"connection timeout=5");
It now fails with:
Quote: System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed.
Login failed for user 'b-PC\Bram'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean
My first priority now is to connect to the DB (by whatever means, including compromising security,) to get a proof of concept together
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: Was this security system per chance written by 'Franz Kafka'?
Microsoft. If you can create, you can read. How do you create tables? Using SQL?
Bram van Kampen wrote: I have since reset the database to 'Mixed Mode'
Remove the "Trusted_Connection=true" part from the connectionstring, and you should be able to connect.
Bram van Kampen wrote: My first priority now is to connect to the DB
Should work if you change the connectionstring. I'll reply to the other post later today.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Eddy
I am getting frustrated. Not with you, but, with this system. I very much appreciate your interest and time, but I am also getting embarased. Nothing seems to work! I returned to User Mode, this being the recommended mode for use by Microsoft. (in the current XP version, Windows Security is totally ignored altogether. We never had any security issues either) This version 3 will need to connect to the internet, so the security issue becomes important.
Eddy Vluggen wrote: Should work if you change the connectionstring. I'll reply to the other post
later today.
I had to shut down the terminal several times to get rid of the internal compiler error.
When I eventually got it running, I got:
Quote: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
About my reference to Franz Kafka. He is a (long dead, I think died 1924) european author. I also think, important reading for IT consultants, designers et all. Two of the books he wrote, 'The Process' and 'The Castle' are worth reading. Both deal each in a different way with an innocent and unsuspecting individual, getting involved in a life changing and enormous burocracy.
These books were required reading for me in 1973, when I grauated secondary school.
Good Idea to read them! More apt than ever!
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: This version 3 will need to connect to the internet, so the security issue becomes important.
Do yourself a favor; download Sql Server Express and connect a machine you can miss to the internet. Dub it "honeypot". Or name it after a nuclear reactor
Bram van Kampen wrote: I had to shut down the terminal several times to get rid of the internal
compiler error.
When I eventually got it running, I got:
Forget about passwords and using the Sql mixed mode business. With that "integrated security", things should work under your current user account, binding all things to the current Windows User under the alias of "dbo".
Later, determine all access-points for the internet-user, write sprocs to fetch that data, and give the user from the IIS-process only execute-rights on those sprocs.
Bram van Kampen wrote: I also think, important reading for IT consultants, designers et all. Two of the books he wrote, 'The Process' and 'The Castle' are worth reading.
Thanks, added them to my reading-list, got a long holiday in the late summer
QPQ, the best book that I read this year was Small Gods[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i am developing android Dictionary Application.. i Required SQLite based Dictionary for android ... Any one Plzz who can help me....
|
|
|
|
|
mAzeem22 wrote: i Required SQLite based Dictionary for android
I doubt that you'll find a dictionary that's completely suited to your wishes.
You could search the net for "travlang dictionary", a free dictionary in MS-Access format. Next, you'd have to convert it to SQLite (if your target-language is supported).
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
SS2008. Big database: 17GB total.
The database has about 400+ tables. If I select a table, I can right-click it and select its properties and there's a Storage item I can click to get the size of the table.
Is there some way I can do that for all tables without going through them one-by-one? I'd like to see where the bulk of the space is being used. Is it possible?
EDIT: I found this[^] link which does what I'm looking for. Open a new query in SSMS and drop the code in the example shown in the link. It worked for me without any problem.
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 8-Jun-13 14:20pm.
|
|
|
|
|
If you have Reporting Services (SSRS) installed, you can right-click on a database in SSMS and select Reports -> Standard Reports -> Disk Usage by Table or Disk Usage by Top Tables.
|
|
|
|
|
Thanks for that. I do have SSRS installed and it does have the report you described.
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 have 3 tables named as patient, medicine, attachment
patient table has columns name as
patient_id patient_name
medicine tables has columns
med_id medicine_name patient_id
and attachment has columns
attachment_id attachment patient_id
now i want data as
patient_name medicine_name attachment
where attachment_table has 2 or more columns who have same patient_id
same as happn with attachment table
but the main problem is
i want to get all the data into a single row
even if attachment has particular patient or no
same as if medicine table has particular patient_id or no
so can any one help me in it
|
|
|
|
|
Patient
Patient_id
Patient_name
Medicine
med_id
medicine_name
patient_id
attachment
attachment_id
attachment
patient_id
Let's use "Coffee" as a sample medication; if both you and me were to be medicated with coffee, the medicine would need to be inserted into the "medicine" table twice. That's a modelling error.
Arun kumar Gautam wrote: now i want data as
patient_name medicine_name attachment
I'd suggest you first fix your database-model. You can try Normalization[^] to correct it, or hope that someone else comes up with a different design.
FWIW, it'd be best to register which patient takes which medication in a separate table. Once you have that, it's easy to join the tables and display them in a single row.
Further, I'd also recommend to change the naming-pattern of the fields. As is, the name of the table is often repeated in the fieldname, wich is redundant.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Arun kumar
Could you provide sample data for understanding purpose and from that how you want the data could you give sample output
Regards,
Prakash.ch
Prakash.ch
|
|
|
|
|
I know how to make a Stored Procedure that its data affect in one TABLE.
The problem is that I'm making a three tier project, and I'm inserting, Updating Reading and Deleting data to tables using Stored Procedure.
My question is, how to make a tored procedure that its data are affecting two tables?
For example. TABLE1 has coulmns Name and Age, and TABLE2 has columns Year and Time.
How to programm one procedure using parameters of two tables.
P.S. Is it possible to create a procedure that its data can be stored in two tables?
Thank you in advance for your time.
|
|
|
|
|
Have no idea what database program you are using so I will speak about MS SQL Server.
A stored procedure may touch many tables. One set of data may be put into many tables.
If Table1 and Table2 do not have a shared column (PersonID for example) it becomes very hard to join the two.
|
|
|
|
|
I solved the problem. Thank you a lot.
I used the nested BEGIN and END between two tables.
Cheers.
|
|
|
|
|
What nested BEGIN and END..? used in your Stored Procedure.
|
|
|
|
|
I want to insert the sql Error Message into the Error Table and return the user defined Error Message. Below is my code, if it sucess then Return Null else it should return Error message like 'Error on Inserting' at the same time it should save the actual error details in the Error table
Create PROCEDURE addTitle
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Insert into apl_Error(Error_Desc,Error_Date) select RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(),GETDATE()
END CATCH
Return
|
|
|
|
|
You will need a "translation" table that gives the message you want based on the error.
|
|
|
|
|
Hi,
Try like this... If you need add TRANSACTION also in this Code.
CREATE PROCEDURE addTitle
@ErrorDtls VARCHAR(100) OUTPUT
BEGIN
BEGIN TRY
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
SELECT @ErrorDtls = NULL
END TRY
BEGIN CATCH
Insert into apl_Error(Error_Desc,Error_Date)
SELECT RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(), GETDATE()
SELECT @ErrorDtls ='Error on Inserting'
END CATCH
END
DECLARE @ErrorDtls VARCHAR(100)
EXEC addTitle @ErrorDtls=@ErrorDtls OUTPUT
SELECT @ErrorDtls
GVPRabu
|
|
|
|
|
Hi
First create table
CREATE TABLE apl_Error
(
Error_Desc VARCHAR(MAX),
Error_Date DATE
)
After that create this procedure
CREATE PROC SAMPLE
@ERROR_DESC VARCHAR(MAX) OUTPUT
AS
BEGIN
BEGIN TRY
CREATE TABLE #TABLE(ID int,fName varchar(20))
INSERT INTO #TABLE VALUES('A1','CHOICE')
SELECT @ERROR_DESC=NULL
END TRY
BEGIN CATCH
INSERT INTO apl_Error(Error_Desc,Error_Date)
SELECT cast( RTRIM(LTRIM(CAST(ERROR_STATE() as CHAR(10)))) +','+ RTRIM(LTRIM(CAST(ERROR_LINE() AS CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE() as varchar(max)),GETDATE()
SELECT @ERROR_DESC='Error on Inserting'
END CATCH
END
to execute this procedure
DECLARE @NAME VARCHAR(MAX)
EXEC SAMPLE @ERROR_DESC=@NAME OUTPUT
SELECT @NAME AS Error_Description
then see apl_Error table now you can see the message and date.
Error_Desc Error_Date
1,8,SAMPLE,Conversion failed when converting the varchar value 'A1' to data type int. 2013-07-05
|
|
|
|
|
TSQL is my biggest weakness here. I really don't enjoy writing it but you have to know it to get things done.
I'm looking for some help here, on making a better search system, and I'm not sure which direction to go. I guess at first, I should get the TSQL right, or better at least.
I have a FTS catalog with partNumber, Title, Description, VendorName for the productInfo table.
I wrote this 2 years ago, but it's pretty weak, and doesn't work well.
For instance, if I search part number "06-CR10", it produces nothing, but if I search "CR-10", it produces a result.
I have trouble with 2 words or phrases like "tile spacers" using CONTAIN, but it works fine using FREETEXT.
my question is:
Should I be detecting multiple words, and use different TSQL based on word counts? Or just better TSQL?
I'm just looking for suggestions.
Dim mySelectQuery As String = "SELECT " & _
"ProductID, Category, SubCategory, ShortDescription, LongDescription, Thumbnail, PartNumber, Price " & _
"FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR FREETEXT(PartNumber, @Query) " & _
"ORDER BY RANK() OVER (Order BY PartNumber, ShortDescription, LongDescription) DESC"
|
|
|
|
|
Short answer: use both CONTAIN and FREETEXT.
Less short answer: The idea is to call a PROCEDURE which complements the use of the higher energy union of the two methods.
Until discovering INFORMATION_SCHEMA ("Views") I was adhereing, for many years, to using LIKE without even considering FULLTEXT search-enabled tables, if you can believe that. Besides the overhead requirement of LIKE and the stored procedure call (yeah, using "String" would be ok), I had only the sys.x catalog from which to draw the associations into a database relationship.
Best answer: hey, whatever works for you.
|
|
|
|
|
Sounded complex on the fist reading. But very informative.
I'll do some more reading on some of the words you used, to learn more about it.
But in the meantime, I experimented with FREETEXT, CONTAINS and LIKE an sort of solved my partNumber issue by using LIKE for the partNumber, so now the part number works.
Dim mySelectQuery As String = "SELECT COUNT(ProductID) FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR PartNumber LIKE @Query2"
I will try searching each column with specific ways to obtain the result I'm looking for and keep refining it for now.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
