Tables Are Populated
Apr 28, 2008What does it mean "Tables are populated" in plain English?
Sorry my mother language is not English.
Thanks
ADVERTISEMENT
Which Tables Are Populated
May 10, 2007Hello all.
I have a database with literally hundereds of tables in it. Can anyone advise me of how to tell which ones are populated with data and which ones arent?
Thanks people
Dataset With Multiple Tables Is Not Getting Populated
Dec 25, 2006Hi,
I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.
I have seen the problem earlier and could not resolved it. Please let me know if any one can help.
Thanks in advance
Rohit
SQL Server Admin 2014 :: Replication - Subscription Database Created But Tables Not Populated
Nov 6, 2015As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
View 2 Replies View Related@@ERROR Is Not Being Populated
May 23, 2006Hi All,
I ran the code below in QA, and @@ERROR never gets populated with any error code, despite the fact that an error occured. This event is very misleading and errors are never caught. Any advice greatly appreciated.
-- Create the table
create table test_table (
test_field int
)
-- Execute the code
DECLARE @err_status int, @row_count int
insert into test_table (
test_field
)
values (
'TEST STRING'
)
select @err_status = @@ERROR, @row_count = @@ROWCOUNT
IF @err_status <> 0 PRINT @err_status
-- Results:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'TEST STRING' to a column of data type int.
New Field Populated Url
Apr 29, 2008hello:
i'm looking for a way to populate a new field in an existing table based on the id of that field with a url string.
for instance, table 'example'
fields x, y, z
I want to populate z based on id from field x with the following query:
http://x.y.z?id=<from field x>&abc=xxx,&abc=yyy
Is there a way to do this?
Thanks!
How Is Available Servers List Populated
Feb 15, 2005When registering a new SQL Server in EM using the wizard a window appears that is supposed to list available SQL Servers, however it does not.
So, my question is:
How is this list derived? and how can the list be managed?
Connectionstring Gets Populated Unwillingly
Jul 26, 2007
Hi,
I hav eplaced an expression for the flat file connection as below
@[User::FileDirectory] + @[User::FileName]
This is supposed to be used instead of the ConnectionString property of the flat file connection.
You can see that I have created two variables.
The variable
@[User::FileDirectory] is set to the directory. i.e. I have hardcoded the path to it and assigned it to this variable.
The variable @[User::FileName] is picked up automatically.
The question is:
When I go to the properties of the flat file connection, I delete the value inside the connectionstring property becuase there is now the expression which is set to the connectionstring.
But when I come back to this property then I am not sure why the connectionstring property gets populated with the directory that I hardcoded to the variable.
Many thanks
Populating A Blank Column Beside A Populated One
Aug 23, 2006I have a table with two columns:
OwnerName Owner
John;Smith
Mary;Smith
OwnerName is populated. Owner is not.
I want to populate the Owner column with the OwnerName in alphabetical order. I have already created a function to do this.
Select fnGetOwner(OwnerName) from OwnerTable. This returns:
Smith, John
Smith, Mary
How do I populate the blank Owner field beside the OwnerName in the OwnerTable?
Changing Populated Field Types
Mar 30, 1999How can I change a field type whilst it is populated?
I have tried :
insert new_table
select *
from old_table
but I get :
Disallowed implicit conversion from datatype 'text' to datatype 'varchar'
Table: 'davy.dbo.new_table', Column: 'de_area'
Use the CONVERT function to run this query.
My table formats are as follows :
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area char(255) NULL
)
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area text(16) NULL
)
Transact SQL :: How Columns Are Populated In ETL Process
Apr 30, 2015I need to query some hierarchical data. I've written a recursive query that allows me to examine a parent and all it's related children using an adjacency data model. The scenario is to allow users to track how columns are populated in an ETL process. I've set up the sample data so that there are two paths:
1. col1 -> col2 -> col3 -> col6
2. col4 - > col5
You can input a column name and get everything from that point downstream. The problem is, you need to be able to start at the bottom and work your way up. Basically, you should be able to put in col6 and see how the data got from col1 to col6. I'm not sure if it's a matter of rewriting the query or changing the schema to invert the relationships.
DECLARE @table_loads TABLE (column_id INT, parent_id INT, table_name VARCHAR(25), column_name VARCHAR(25))
DECLARE @column_name VARCHAR(10)
INSERT INTO @table_loads(column_id, parent_id, table_name, column_name)
SELECT 1,NULL,'table1','col1'
[code]...
Count How Many Times Column Is Populated With A Number Per ID
Jul 10, 2013What i want to do is count how many times the [Omnipay_Account] column is populated with a number per parentid.
In the [Omnipay_Account] column you can either have a null or a 15 digit number
Idea result layout would be three columns these columns would be
Every row, would need to be grouped via the parentid number
ParentId Omnipay MSIP
My query is
SELECT
[ParentID]
,[Omnipay_Account]
FROM [FDMS].[dbo].[Dim_Outlet]
Command.Parameters Collection Populated Automatically
Jan 31, 2007Hi,
We have a lot of VB6 code that uses ADO 2.7 and stored procs wih Sql 2005. I have noticed recently that if I use the follow code:
Dim con As New ADODB.Connection
con.ConnectionString = "driver={SQL Server};server=(local);database=test;uid=sa;pwd="
con.Open
Dim com As New ADODB.Command
com.ActiveConnection = con
com.CommandText = "usp_GetSetting"
com.CommandType = adCmdStoredProc
com.Parameters.Append com.CreateParameter(...)
It will fail. the reason being the after setting the CommantText and Type ADO then seems to automatically go away and populate the Parameters collection from the databases metadata according to the SP we are calling.
I have never seen this before, I thought the Refresh method had to be called before the parameters collection get populated.
Can anyone help me please?
Some Columns Not Populated In Data Flow Destination
Mar 13, 2007I am populating a table using a SQL command. very simple.
SELECT RISKID
,RISKIDREN
,RISKIDEND
,PREMIUMSUBTOTAL
,PREMIUMTOTAL
,SURCHARGE1
,SURCHARGE2
,SURCHARGE3
,SURCHARGE4
,SURCHARGE5
,COMMPREMIUM1
FROM PREMIUM
However, the first three columns are not being populated in the destination table. The other columns come over fine.
The SQL stmt. returns data as expected when run against the source database.
I deleted the source and destination and recreated the flow to prevent metadata mapping issues. In the source editor preview I see all of the columns and data. In the destination editor preview, the first three columns of data are null ???.
It appears that the columns are not mapping properly even though they are in the source and destination of the mapping editor.
I have made sure that the destination mapping contains all the columns in the UI.
The source and destination have the columns represented in the advanced editor metedata. I also checked the XML to verify that the columns are in the destination.
There is a row count between the source and destination. which should have no effect.
This is a part of a larger DW load where I have 10 other tables populated within the dataflow. I also do not get any validation, or error messages. So, I have eliminated truncation errors or the like.
I am really puzzled. Has anyone run accross anything like this?
Variable Within SSIS Inconsistently Populated When Scheduled
Aug 22, 2006I have a SSIS package with multiple objects executing ODBC SQL to Teradata. All of the SQL uses the same variable (today) to susbtitute today's date into the native teradata SQL.
Today is populated using the following expression:
"'" + (DT_WSTR, 4) DATEPART( "yyyy", GETDATE() ) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "mm", GETDATE() ) ,2) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "dd", GETDATE() ) ,2) + "'"
The problem is that some of the code uses the correct date, but others appear to use the date that the SSIS package is added to the scheduler. It is not always the same SQL statements which deriver the incorrect date. The code below is four of the SQL statements generated at the same time using this variable - two show the correct date (2006-08-22) and two the date the package was added to the schedule (2006-08-18):
SELECT MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) , COUNT ( * ) FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeDate = (dAtE'2006-08-22') ) GROUP BY MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode ORDER BY TradeTypeInd , MarketSegmentCode
SELECT ParticipantCode , CASE WHEN ParticipantCode = ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END AS Counterparty , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeTypeInd = 'O' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY ParticipantCode , ParticipantCodeBuyer , ParticipantCodeSeller , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode
SELECT ParticipantCode , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-22') ) GROUP BY ParticipantCode , CurrencyCode , MarketSegmentCode
SELECT ( CASE ParticipantCode WHEN ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END ) AS Cparty , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY Cparty , CurrencyCode , MarketSegmentCode
Has anybody experienced similar problems with variables in SSIS packages and the scheduler?
Thanks
Overwriting An Existing Database With One That Is Populated With Data
Jan 21, 2008I am tring to figure out how to simplify the process of populating a database created by an application with the same database, only with data already in it. So far i have created a backup of the database and used that backup file with SQL server management express to overwrite the existing database with that backup file on a new computer so the program will have data when initally installed for Demonstration purposes. I was hoping there was an executable script that i could use, so that when someone wants a demonstration of our product, they can see its options and functionallity with data available. Maby i am going about this the wrong way, i need to know if there is a way that when our program is installed an executable can simply be run to populate our database with a backup of our sample database. Any imput would be helpful.
Thanks.
Isaias
Caching Or Reusing Parameters Populated With SqlCommandBuilder.DeriveParameters
Mar 3, 2004Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
Full Text Index Not Working When Populated From Nvarchar. Bug?
Jul 11, 2007Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
Help With SQL Statement To Find First And Last Populated Field In String Of Fields
Jun 21, 2006Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View 2 Replies View RelatedLoading An Excel Sheet That Gets Populated With Value After Opening In Ssis
Nov 6, 2007
I have an excel sheet that has a bunch of columns. Some of these columns have static data but there are a few of the columns that retrieve data by making calls to servers and returning values that populate these columns. Usually about 30 mins before the columns are populated.
I then need to to load the excel sheet into a table.
I was wondering if there was anyway by which we can make the connection to the excel sheet and then force a delay of about 30 mins before it starts retriving the data?
Is this possible in ssis? Or can I achieve it by some other means?
Thanks for any help in advance
Why Was ReadWriteVariable Not Populated/updated With A Value Of The Dynamically Generated String?
Jul 27, 2007
Hi, I have a script task in SSIS which dynamically generates a string for a file name to be used as flat file source. I execute the task and it executed with success; but when I checked the result of the variable TotFileName from the Expression builder window for the flat file connection manager it was not populated with a file name like \MyServerMyDriveMyFolder200706daily.txt. So something might still be missing from the script below. Or is the way I do it correct? Can someone help with this? Thanks a lot!!
I have created package level variables ImportFolder (value like: \MyServerMyDriveMyFolder ) and TotFileName (value field empty) and make ImportFolder a ReadOnlyVariable and TotFileName a ReadWriteVariable. Then I use expression to set the property for flat file connection manager to use the TotFileName variable.
(Basically the idea is: if now is July 2007 then the filename should be 200706daily.txt; if now is Jan 2008 then the filename should be 200712daily.txt)
-----code------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim TotFileName As String
Dim TrueFileName As String
Dim sYear As String
Dim sMonth As String
Dim sDate As String
sYear = CStr(Year(Now()))
sMonth = CStr(Month(Now()) - 1)
If (Month(Now()) < 11 And Month(Now()) > 1) Then
sMonth = "0" & sMonth
End If
If (Month(Now()) = 1) Then
sMonth = "12"
sYear = CStr(Year(Now()) - 1)
End If
sDate = sYear & sMonth
TrueFileName = sDate & "daily.txt"
TotFileName = CStr(Dts.Variables.Item("ImportFolder").Value) & TrueFileName
Dts.Variables.Item("TotFileName").Value = TotFileName
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Transact SQL :: DB Audit File That Gets Populated With Data As Activity On DB Goes On
Aug 25, 2015writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file
SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default)
order by event_time desc
GO
--DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]
[code]...
Unable To Release Space Occupied By A Dense Populated Table
Aug 22, 2007
Hi ,
I have a table let say TableA , which have a size of 22 GB.Due to its size i down size this table by deleting 40% rows.So ideally it should size to 15 GB but space is not released by TableA. How i can do it ? I tried shrink database, shrink data file wizards and DBCC command , Reorganize the index but all is in vain.
Any suggestion /solution?
Analysis :: Slicers On Partitions Not Getting Refreshed When Populated Using Script Task
Apr 23, 2015I have set the slicers on paritions using Script task.
After cube process, the mdx queries are not hitting partitions as per Slicers.
But once i open each partition slicers from SSMS and close it by doing ok. My mdx query is good enough to hit the relative partition only.
But it is not possible to manually refresh each slicer on partition.
Is there any way out to solve this issue. I am using 2008 R2.
Integration Services :: SSIS Copying Populated Excel File?
Jul 9, 2015I am experiencing unexpected behaviour when copying an Excel file.
An Excel file from TemplateDir to WorkingDir.
Excel file is then populated in WorkingDir, ok Copy File task copies Excel File from WorkingDir to FinalDir, however non populated version of file is copied.
However, if I stop SSIS and and execute Copy Task only, the populated version of file is copied.
It seems SSIS has a kind of lock on Excel file in WorkingDir.
how lock can be released to enable Copy of populated file.
Reporting Services :: Top N As Parameter In SSRS Report Populated By SSAS Cube?
May 21, 2015I have an SSRS report that gets its data from an SSAS cube, and it essentially displays a list of students and absences. Everything works fine.
But the users only want to see the top N students with the highest absences. For example, today they want to see the top 100, but during summer vacation, they only want to see the top 5.
Is there a way to add this parameter to the SSRS report? So if I select "5" from the dropdown parameter, it will show the top 5 absences.
The query that I use to populate the report is something simple:
SELECT NON EMPTY { [Measures].[Absences] } ON COLUMNS, NON EMPTY { ([Dim Date].[Date Key].[Date Key].ALLMEMBERS * [Dim Student].[Lte Cell Key].[Lte Cell Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimDateDateKey, CONSTRAINED) ) ON COLUMNS FROM [Daily Cube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
SQL 2005 Bug? Cannot Create Full Text Index For VARBINARY Column That's Populated With Converted NVARCHAR Values
Jul 11, 2007Hi, I was wondering if any SQL Server gurus out there could help me...I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(varbinary(max), CAST('test text' as nvarchar(max)))no results are ever returned.Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
View 14 Replies View RelatedSQL 2005 Bug? Cannot Create Full Text Index For Varbinary Column That's Populated From Converted Nvarchar Values
Jul 11, 2007Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
Code Snippet
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
Search The Records After The Records Populated
Aug 17, 2007
Hi,
I have to search the records after the records populated.
I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.
For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.
Please tell me how to implement in this report,
Thanks and Regards
Altaf Nizamuddin
Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
Jan 24, 2008I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables
Dec 7, 2006We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedSQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database
Feb 5, 2007From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'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
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'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")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'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
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
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")
End Sub
End Class