How Can L Take Text From My Tables In Database Via Stored Prucedure?
Jan 21, 2008
hello dear friends? l want to take values from my table via a stored procedure and appoint this value,that l take from table via stored procedure, to Textbox1.TExt or LAbel1.Text table(id,name,surname,adress) id is primary key and select query will do this ;select name which id =4 and then this value will appoint to label or textbox text thanks indeed for help
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
hi in my front end i have one text area where user can input his sql statements. for samller data its working. the data type of coulmn is Text in sql server 2005. but problem is when i try to store this data its showing error.i meant not storing data at all. if i cut some of line from this then in this case it stores the data .what should i do to overcome this problem
thanx a lot.
DECLARE @sTemp VARCHAR(10) DECLARE @nMemberID INT DECLARE @nPartnerID INT DECLARE @nDocumentTypeNo INT DECLARE @nDocumentFormat INT DECLARE @sEmailAddress VARCHAR(200) SET @sTemp = '{*MEMBERID*}' IF UPPER(@sTemp) = 'NULL' SET @nMemberID = NULL ELSE SET @nMemberID = CAST(@sTemp AS INT) SET @sTemp = '{*PARTNERID*}' IF UPPER(@sTemp) = 'NULL' SET @nPartnerID = NULL ELSE SET @nPartnerID = CAST(@sTemp AS INT) SET @sTemp = '{*DOCUMENTTYPENO*}' IF UPPER(@sTemp) = 'NULL' SET @nDocumentTypeNo = NULL ELSE SET @nDocumentTypeNo = CAST(@sTemp AS INT) SET @sTemp = '{*DOCUMENTFORMAT*}' IF UPPER(@sTemp) = 'NULL' SET @nDocumentFormat = NULL ELSE SET @nDocumentFormat = CAST(@sTemp AS INT) SET @sEmailAddress = '{*EMAILADDRESS*}' IF EXISTS(SELECT * FROM ProtocolSettings WHERE ((@nMemberID IS NULL AND MemberID IS NULL) OR MemberID = @nMemberID) AND ((@nPartnerID IS NULL AND PartnerID IS NULL) OR PartnerID = @nPartnerID) AND ((@nDocumentTypeNo IS NULL AND DocumentTypeNo IS NULL) OR DocumentTypeNo = @nDocumentTypeNo) AND ((@nDocumentFormat IS NULL AND DocumentFormatNo IS NULL) OR DocumentFormatNo = @nDocumentFormat) AND ProtocolSettingNo = 307) BEGIN UPDATE ProtocolSettings SET SettingValue = @sEmailAddress WHERE ((@nMemberID IS NULL AND MemberID IS NULL) OR MemberID = @nMemberID) AND ((@nPartnerID IS NULL AND PartnerID IS NULL) OR PartnerID = @nPartnerID) AND ((@nDocumentTypeNo IS NULL AND DocumentTypeNo IS NULL) OR DocumentTypeNo = @nDocumentTypeNo) AND ((@nDocumentFormat IS NULL AND DocumentFormatNo IS NULL) OR DocumentFormatNo = @nDocumentFormat) AND ProtocolSettingNo = 307 END ELSE BEGIN INSERT INTO ProtocolSettings (ProtocolSettingNo, MemberID, PartnerID, DocumentTypeNo, DocumentFormatNo, SettingValue) VALUES(307, @nMemberID, @nPartnerID, @nDocumentTypeNo, @nDocumentFormat, @sEmailAddress) END
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure: Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK) Categories: - CategoryID (int, PK) - CategoryName (varchar) I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query: CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID) But this gave me the error: Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns. I tried converting the Content to a varchar(max) within my view but it still didn't like. Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.
Hi all, I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc. For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?
I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader datareader2 = cmdFindRowsWithKeyword.ExecuteReader Dim strMsg As String Dim intRowToFlag As Integer Dim strRowsToGet As String Dim strKeywordAsTyped As String Dim strKeywordAllCaps As String Dim strKeywordAllLower As String Dim strKeywordFirstLetterCap As String Dim FirstLetter As String
'Assign keyword as typed to variable strKeywordAsTyped = txtSearchFor.Text 'Assign keyword as typed to variable then convert it to all uppercase strKeywordAllCaps = txtSearchFor.Text strKeywordAllCaps = strKeywordAllCaps.ToUpper 'Assign keyword as typed to variable then convert it to all lowercase strKeywordAllLower = txtSearchFor.Text strKeywordAllLower = strKeywordAllLower.ToLower 'Assign keyword as typed to variable then convert it so just the first letter is in uppercase strKeywordFirstLetterCap = txtSearchFor.Text FirstLetter = strKeywordFirstLetterCap.Chars(0) FirstLetter = FirstLetter.ToUpper strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1) strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row. If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag SqlConnection2.Open() Dim datareader3 As SqlClient.SqlDataReader datareader3 = cmdFlagRowsWithKeyword.ExecuteReader datareader3.Close() SqlConnection2.Close()
I have a SQL SERVER database which has Articles Table. This table contains "Description" field which is of type "text". I am trying to insert 800- 1000 words of data into this field. This data also contains code snippets. I dont know for some reason it only inserts one or two lines and thats it. No error is being thrown. I am using multiline textbox to enter the data into the database. any ideas
It displays something like this:
test 1
By AzamSharp
Creating XML Men // This is very long text. Actually its the whole article but it only displays three words
I have a SQL Server database. The data from a table is populated in the table and can do a regular display query on a record without issue.
Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.
I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?
The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.
I then have to take this data and write it back out to one file with a header and detail lines.
Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.
If you have both invoice header lines and invoice detail lines in a comma delimited file, how can I get the data in the file to be imported into two different tables. I can produce a text file eg:
1,20,10/03/2002,39 High Street Any Town,, 2,20,Fluffy Slippers,2,Red,10.99 2,20,Pyjamas,3,Black,15.99 2,20,Trousers,1,Lilac,24.99 1,21,10/03/2002,11 Gibson Close, 2,21,Sandles,1,Black,12.99 2,21,Shoes,4,Blue,23.99 1,22,13/03/2002,45 Mill Street, 2,22,Womble Feet,4,White,16.99 2,22,Glass Slipper,1,Transparent,23.99
Lines with 1 in the first column should go in the InvoiceHeader table Lines with 2 in the first column should go in the InvoiceDetails table.
I have tried with DTS but to no avial - ActiveX scrips in the Transform Data Task can only seem to access one data destination - it one table not two.
Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to
select a,b into #inserted from inserted
Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.
Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)
I have around 140 text files in a folder where all the file entries are delimited by tabs. Currently i am copying the data and then pasting it in to the tables and it is perfectly working fine. I have already created tables for those 140 files. But now i want to load those files in to the table at a single shot. How can i do it? I am using SQLServer 2005.
Is there a way using MS SQL Server and Enterprise Manager to get a textdocument (or perhaps even a Word document) listing all table names,column names, etc of a database?--Sugapablo------------------------------------http://www.sugapablo.com <--musichttp://www.sugapablo.net <--personal
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.
Hi, I'm using SQL Express Advanced CTP and I'm in the process of adding a search facility to an asp.net site. I'm having a huge problem deciding the easiest and most optimised method to use when creating the index using multiple tables and where I can't seem to to produce a unique, clustered index. The site I'm testing on is a portal in which we have various, different clients advertising their business. A user will be able to search using keywords such as business activity, business location and anything else that may appear in the business / publicity descriptions. A publicity (publicity_id) may belong to one or more TOWNS (ville_id), e.g., Business A might have a promotional offer that applies to three local towns, Business B to all towns and Business C to just one town. A publicity may belong to one or more ACTIVITIES (activity_id), e.g., Business D is a resturant but has also a bar and Business E belongs to prĂȘt-Ă -porter, fashion, and beauty. I'm using a view (ActivePubs) to produce a query across multiple tables Clients (business name), Publicities (publicity descriptions), Towns and Activities. So from the nature of how the publicities are connected to activities and towns, I have many to many relationships that produce duplicate publicity_id values when I create the view, and thus this provides a problem when trying to create a unique, clustered index on ActivePubs; and none of the coloumns produce unique values that monotonically increases, ideal, I believe, for creating unique, clustered indexs. I have also read that it is only best to use interger values for creating unique, clustered indexes. I thought that if I SELECT the row number in ActivePubs, SELECT ROW_NUMBER() OVER (ORDER BY dbo.Publicities.publicity_id) AS ROWNUM, ... in addition to the rest of the query, then I might be able to use the unique values from the query result to create the unique, clustered index CREATE UNIQUE CLUSTERED INDEX CL_Inx_Pubs ON ActivePubs(ROWNUM) but instead this produces the following error... Cannot create index on view "ActivePubs" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view. So I scrapped this idea but without this method using just CREATE UNIQUE CLUSTERED INDEX CL_Inx_Pubs ON ActivePubs(publicity_id) I get the following error... CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'ActivePubs' and index name 'CL_Inx_Pubs'. The duplicate key value is (32). I did consider NOT using a UNIQUE clustered index, but got this error... Cannot create nonunique clustered index on view 'daysandnights.ActivePubs' because only unique clustered indexes are allowed. Consider creating unique clustered index instead. The only other method I think about using is storing the query in a temporary table instead of using a view, then creating an additional column in the temporary table to store a unique, automatically incremented, PK for the table to give me my unique index, but my guess is that this is overkill? Much appreciate any help you can provide. Deakus
is it possible to use a full-text search query on two tables at the same time? for example, say i have a table of items, and another table of descriptions... would i be able to search both those tables for a string in one query? they are currently in indexed in separate catalogs, would i need to put them both in one?
I am trying to create a text file from multiple SQL Tables using @BCP_Command. I tried using DTS and SQL but the number of columns in the tables have to be the same size when doing a union. I also not to place a delimeter between each column. I've learned how to use BCP_commands on one file not sure if you can make it work with two or more.
Hello,We are developing an application against an MS SQL Server 2000 databasewhich requires that we implement full-text searching across columns inmultiple tables. The research that we have conducted seems to indicatethat this is not directly possible within SQL Server 2000. And we canfind no way to implement this as the catalogs that are generated aretable specific.As a work-around, we are planning to create a secondary table usedstrictly for searching which is going to denormalize and combine thesearchable data into one catalog. We just want to confirm that this isthe recommended approach or if there is an alternate solution anyonehas used. Please note that upgrading to SQL Server 2005 is not anoption.Thanks.John FlemingJoin Bytes!
Hi,I have 2 tables and each has a text column. When i compared the 2columns with the LIKE operator i found something strange.create table test1( var1 text )create table test2( var1 text )insert into test1 values ( '-- [ CustomerType = 1 ]' )insert into test2 values ( '-- [ CustomerType = 1 ]' )select * from test1, test2where test1.var1 like test2.var1The last query surprisingly did not return any results. However when itook of the '[' in the text data and had something like thisinsert into test1 values ( '-- CustomerType = 1 ]' )insert into test2 values ( '-- CustomerType = 1 ]' )the query returned expected results, showing one row.any helpful would be great.Regards,Arun Prakash. B
I have created a DTS package which imports text file into single sql server table with 8 columns (SourceData). The DTS package uses 'Test1.txt' file. Now i have around 200 text files (Test1,Test2,.....Test200). I need to import them one by one into 'SourceData' table. Could you pls. help me out in getting solved this mistery.
I have 2 sql tables. 1 is the header table and another is the detail table. How can I have the header record being appended in the text file and then have the detail records being appended to a same text file again with comma delimited ?