Managed Replacement For SQLDMO?
Feb 23, 2006In VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?
Thanks,
Rainer.
ADVERTISEMENT
SQL Server Replacement
Sep 23, 2004I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
Text Replacement
Feb 28, 2008Hello all,
I get some dates entered manually in french format in a varchar(255) field and want to convert that field in datetime. Since, Enterprise manager is warning me about a risk to loose information, I'm thinking about a way to update the french text in the corresponding english text before doing the conversion.
Example:
25-fév-08 (varchar(255)) has to become 25-feb-08 (datetime)
Could anybody help me, please?
RSS Replacement For Sp_makewebtask
May 7, 2007Hello All!
I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.
I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.
Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?
I know that RS is xml based, but some pointer on how/where to start would be much appreciated.
Many Thanks....
REPLACEMENT PROBLEM
Feb 24, 2008I wish to replace column2 'ABC' value become blank.
EXAMPLE TABLE VIEW
column2
-------
ABC 123, Alt. ABC 456, Alt ABC 789
select replace(replace(B.[column 2],A.[column 1],''),'ABC','')
from TABLE A,TABLE B
where A.id = B.id
After executed
column2
-------
123, Alt. 456, Alt. 789
But not expected result, because i jz wan ABC become blank not Alt. ABC.
So what should i modify with my logic and script?
Replacement For My LIKE Clause
Dec 27, 2006Dear all,I need some help from all my Transact SQL Guru friends out there..Here is the scenario in its most simplified form.. ..I have two tables.. A(Lookup table) and B(Transaction Table)TableA FieldsEmployeeLocationIDEmployeeLocation (This could have values say"B","BO","BOM","C","CA","CALC") etc...TableB FieldsEmployeeIDEmployeeName.......EmployeeLocationID (will have null initially when rows are populatedfirst time)EmployeeLocation (This could have values"BA123","BOMBAY","BOTS123","BRACK".... etc)I hope you get where I am leading this to, from my examples..Requirement is to populate the EmployeeLocationID in Table B withEmployeeLocationID from TableA by matching the field EmployeeLocationin both tables.Please note that table B's EmployeeLocation could be A'sEmployeeLocation + some additionalcodes like "123","RACK" etc in theabove example...Therefore, this is what I had wrote initially..update Bset B.EmployeeLocationID =A.EmployeeLocationID
Quote:
String Replacement
Jul 20, 2005Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob
View 4 Replies View RelatedReplacement For BCP In 2005
Oct 25, 2007
Hi,
Is there any replacement for BCP in Sql Server 2005 ?
Thanks
Altaf Hussain Nizamuddin
DB_ID() Replacement
Sep 1, 2006In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.
The original statement is:
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
I replaced DB_ID() as follows but none worked:
DB_ID(N'pubs')
pubs
What should the syntax look like?
IsInteger - Replacement For IsNumeric
Dec 13, 2005Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO
CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
-- String to be tested - Must only contain [0-9], any spaces are trimmed
@strINTvarchar(8000)
)
RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_IsINTCheck that a String is a valid INT
*SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
*IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
*
* Returns:
*
*int valueValid integer
*NULLBad parameter passed
*
* HISTORY:
*
* 30-Sep-2005 Started
*/
BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue
/** TEST RIG
SELECTdbo.kk_fn_UTIL_IsINT('123'),IsNumeric('123')
SELECTdbo.kk_fn_UTIL_IsINT(' 123 '),IsNumeric(' 123 ')
SELECTdbo.kk_fn_UTIL_IsINT('123.'),IsNumeric('123.')
SELECTdbo.kk_fn_UTIL_IsINT('123e2'),IsNumeric('123e2')
SELECTdbo.kk_fn_UTIL_IsINT('XYZ'),IsNumeric('XYZ')
SELECTdbo.kk_fn_UTIL_IsINT('-123'),IsNumeric('-123')
SELECTdbo.kk_fn_UTIL_IsINT('-'),IsNumeric('-')
**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO
Kristen
Special Character Replacement
Mar 3, 2006shailendra writes "Hi,everbody
lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.
very urgent.....
waiting for the reply
shail"
Data Migration With Value Replacement
Dec 16, 2006Table A
200 Rows, each row: X, has a unique value. Has 2 extra columns: C1 and D1.
Table B
4000 Rows, each row: Z, has a value that corresponds to Table A's unique values. Has 2 extra columns: C2 and D2.
What I am looking to do is update all 4000 rows in Table B to match the data set in Table A. C1 -> C2, D1 -> D2 following the criteria that X = Z.
All the update attempts I've made so far only update 200 rows within Table B.
Thank you for your help.
Double Quotes Replacement
Sep 5, 2005Hi,It seems to be simple, however, it stumbles me.how to replace all the double quotes (") within the followingsentence (or a column) with single quotes ('),colA = this is a freaking "silly" thing to dointocolA this is a freaking 'silly' thing to doSelect Replace(colA,'"',''')[color=blue]>From tblXYZ[/color]won't work,Select Replace(colA,'"',"'")[color=blue]>From tblXYZ[/color]won't work neither.How come? Thanks.
View 4 Replies View RelatedReplacement For Access Forms
Aug 2, 2007Just wondering if anyone has any suggestions for a replacement for Access Forms once I move the tables etc to SQL 2005?
Does SQL 2005 have any form building functionality like Access?
If Conditional Statement Replacement
Oct 2, 2007
Hi,
Is there a work around for the following query on sql server compact edition
IF(Some Condition) --
BEGIN
INSERT INTO @TEMP
SELECT 1, XYZ_ID FROM My_Table
END
Thanks,
Dp
How To Set A File For Replacement Of Variables?
Aug 30, 2007Hi everyone,
I'd like to change in order to improve the maintenance of our packages.
I've got lots of packages running with values such as "path" and "filename" inside variables.
Let me know how can I set xml file for that.
Thanks a lot,
Replacement For Access Forms
Nov 1, 2007
Hi,
We had to migrate from MS Access 2003 to SQL Server 2005 due to some project requirements.
We had used Forms, earlier, to enter data to the Access database.
I understand SS2005 doesnt support forms.
Can you suggest any other way of creating an equivalent entity as that of forms, so as to enable users to enter data to the SS 2005 DB easily.
Thanks in Advance for your help,
Regards,
Sundar
SQLDMO: HELP!! (This One Not The Other)
Feb 14, 2000I am trying to write a database compare application for our SQL Server databases using VB and SQLDMO.
I find SQLDMO to be rather slow but on large databases with about 800+ tables, this loop eats up all of my abundant memory until everything crashes. On smaller db's it works fine but is a slower than I expected.
Dim Tbl as SQLDMO.Table
Dim Col as SQLDMO.Column
For Each Tbl in DB.Tables 'DB was previously set
For Each Col in Tbl.Columns
Debug.Print Tbl.Name & "." & Col.Name
Next Col
Next Tbl
Here's a pic of what the app looks like so far. If it looks useful to you, let me know, I'll give you a copy if I can get it working better!
Chad
Sqldmo.dll
Feb 5, 2006I am using SQLDMO.DLL for Backup & restore utility in C# application.
Following is the code :
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect("IBM0505d-040","sa","");
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Devices = res.Files;
res.Files = this.txtRestorefrom.Text;
res.Database = "abc";
res.ReplaceDatabase = true;
res.SQLRestore(srv);
While running above code it gives following exception :
"[Microsoft][ODBC SQL Server Driver][SQL Server]Database in use. The system administrator must have exclusive use of the database to run the restore operation.[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally."
Can somebody suggest solution for this.
Thanks,
Jitendra C.
Sqldmo
Feb 8, 2006Hi,
I am using SQLDMO for database backup and restore.
While taking a backup if I specify existing backup file name then I think it appends to existing file because its size increases almost by double.
And when I restore from that file the it restores data which I take back up very first time. It doest dont restores latest data.
If user specify existing file name then it should overwrite.
How to specify this using SQLDMO
Thanks
CHECKSUM Replacement For IMAGE And TEXT
Aug 22, 2006With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.
This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.
With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
ENDActually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION dbo.fnPesoTextChecksum
(
@Data TEXT
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden
Replacement For ISAPI In SQL Server 2005
Oct 10, 2006Hi everyone, I have some code that we need to migrate to SQL Server2005 from 2000, and I have a webpage that upon viewing, fires a queryto the SQL server using ISS and ISAPI. The result set is formatted fordisplay using XSLT. But since ISAPI is deprecated in SQL 2005, I waswondering how to migrate this.Thanks,
View 5 Replies View RelatedNull Replacement On Excel Query
Nov 23, 2006Hello everyone,
i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.
Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?
thanks a lot.
Where Is SQLDMO Documentation?
May 5, 2008Hi all
Could anyone tell me from where i can find complete Documentation for SQLDMO Object and how can i initialize
and use it in my programms?
Kind Regards.
SQLDMO Error
May 25, 2006I have a program using SQLDMO object.When I use it on my computer and I have install sqlserverit works OK,but when I move the program to another computer without sqlserverthe program runs error the error message is com exception 80040154can the proram using SQLDMO running on computer without installing sqlserver?BTW: my System is win2003,and the other is windows xp
View 2 Replies View RelatedAre There Sqldmo In Sql Server 6.5 ?
Feb 21, 2001Hi
Are there sqldmo for server 6.5 ?, This is , the Sql server 6.5 has SQLDMO ?
SQLDMO Problem
Jun 8, 2006Hi
I need to install sqldmo.dll and related files - so I can use SQL Objects. I've been searching the Internet and trying for hours to register the file on XP Client machines - but not go.
I have put the following files in the locations below:
sqldmo.dll Program FilesMicrosoft SQL Server80ToolBinn
sqldmo.rll Program FilesMicrosoft SQL Server80ToolBinnResources1033
sqlresld.dll Program FilesMicrosoft SQL Server80ToolBinn
sqlsvc.dll Program FilesMicrosoft SQL Server80ToolBinn
sqlsvc.rll Program FilesMicrosoft SQL
Server80ToolBinnResources1033
sqlunirl.dll System32
w95scm.dll Program FilesMicrosoft SQL Server80ToolBinn
I have also tried putting the files in System32 with the appropriate subdirectories - still not go. I cannot seem to get this to work.
If anybody has successfully installed sqldmo.dll for use on SQL Server 2000 client machines running XP, I'd be really grateful for a few pointers.
Thanks
Paul
Registry Key For Sqldmo.dll
Apr 24, 2008can anyone give the registry key for sqldmo.dll?
View 1 Replies View RelatedSQLDMO Equivalent
Dec 18, 2006jess writes "Hi,
IS there a component/way of retrieving a list of SQL Servers or databases on your local network for SQL 2005/Express. The same way sqldmo worked for a .net project.
thanks"
SQLDMO Object
Sep 8, 2005Hi,how to create an SQL DMO object in .net and use it to enumerate theproperties of Sql server. Can we use this object to list propertieswithout logging in to the server?
View 3 Replies View RelatedSQLDMO Installation
Jul 20, 2005Using InstallShield Developer 7.04.Does anyone know of a good way to detect if SQLDMO is installed?TIAmcpoo
View 2 Replies View RelatedSQLDMO.BulkCopy
Jul 20, 2005Hi all I am trying to do a Bulk Copy from a "tab delmimited" text file to atable in my database. I have it almost working except when the file hastoo few columns for the table (table has 421 columns).Some of my the files will have 419 columns some others files will have 421columns.When my bulk insert script encounters a file with 419 colums it will putsome of the data from the next line in the last 2 columns.I tried creating a DTS package with a bulk insert and I get the same outcome.Here is my test script can anyone help'--------------------------------------------------------------<%Dim objServer: Set objServer = Server.CreateObject("SQLDMO.SQLServer")Dim objBCP: Set objBCP = Server.CreateObject("SQLDMO.BulkCopy")Dim objDB: Set objDB = Server.CreateObject("SQLDMO.Database")dim BulkCopydim objTabledim itemdim g_strUploadPath: g_strUploadPath =Server.MapPath("../DOC2/")&"upload"dim strFileName: strFileName = g_strUploadPath & "ex_test.txt"'if file doesn't exist and it's an import,'don't waste time (too be add later)'If Import = True And Dir(FileName) = "" Then Exit Function'On Error GoTo ErrorHandlerobjServer.Connect "XXX.XXX.XX.XX", "XXX", "XX"objServer.EnableBcp = 1Set objDB = objServer.Databases("Advia120v2_dev")With objBCP.DataFilePath = strFileName.UseBulkCopyOption = True'tab delmitted, carriage return line feed ends row.DataFileType = 2'.ColumnDelimiter = chr(9)'.RowDelimiter = chr(13) & chr(10).IncludeIdentityValues = FalseEnd WithobjDB.Tables("Hemo_193_39552_39").ImportData objBCP'BCP = True'ErrorHandler:'Set objBCP = Nothing'Set objServer = Nothing%>'--------------------------------------------------------------thanks
View 3 Replies View RelatedUrgent - Replacement Of Number Function In Sql Server
Apr 10, 2001Hey folks,
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ