I have a SQLSERVER INSTANCE A, to which I want to connect from a Client as TEST, using the SQL Server alias functionality under the client network utility program. The alias is working fine with the TCP\IP connection but it fails when I try to connect to the SQL Server using the named pipes. In my case I'm trying to connect to a named instance of SQL Server 2000 using the following pipe: \\servername\pipe\mssql$instancename\sql\query
Any help?I have just tried this. Pain in the neck, too. When you change the name of the alias, the client network utility is all helpful, and changes the actual path, and server name. So you have to get the right path, and save it of in notepad or something. Change the name of the alias, replace the name of the server with the name of the server you want, then recover the path from notepad. I tried it, and it did not work. Then I hit the Apply button. Now it works. Does that approximate what you went through?|||I can connect to any server using the alias name with the TCP/IP, but the same doesn't work with the Named Pipes.
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Name property of SqlUserDefinedType
Hi,
MSDN describes that Name property of
Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server, but
by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
VS.NET IDE?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#maMbvsHGHA.2300@.TK2MSFTNGP15.phx.gbl:
> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL
> Server, but by VS.NET IDE. I have set a name for my UDT, how can I see
> its effect it VS.NET IDE?
>
That statement is a bit misleading. The name property defines what name
the UDT will be created as, in SQL Server, if created through the auto
deployment features in VS.
So, if you have set the Name property and subsequently deployed the type
from within VS, you should see a type in the database with the same name
as the property.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server,
> but
> by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
> VS.NET IDE?
Have you deployed the UDT from IDE? The Name property defines SQL Server
name of the type, which is used by IDE when deploying the type (i.e. when
IDE prepares the CREATE TYPE statement).
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
MSDN describes that Name property of
Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server, but
by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
VS.NET IDE?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#maMbvsHGHA.2300@.TK2MSFTNGP15.phx.gbl:
> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL
> Server, but by VS.NET IDE. I have set a name for my UDT, how can I see
> its effect it VS.NET IDE?
>
That statement is a bit misleading. The name property defines what name
the UDT will be created as, in SQL Server, if created through the auto
deployment features in VS.
So, if you have set the Name property and subsequently deployed the type
from within VS, you should see a type in the database with the same name
as the property.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server,
> but
> by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
> VS.NET IDE?
Have you deployed the UDT from IDE? The Name property defines SQL Server
name of the type, which is used by IDE when deploying the type (i.e. when
IDE prepares the CREATE TYPE statement).
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
Wednesday, March 21, 2012
n00b question - permissions from website
Hi everyone,
I've inherited a website based on the dotnetnuke frameowork.
this website also uses the reporting services from sqlserver.
When the report button is clicked in the website, the user is prompted
with a login and password (standard windows dialog)
Entering the username/password for the web site fails,
entering username/password for the machine works.
The user - of course - doesn't want to enter enything, let alone see the darn dialog box,
just wants to see the report on the screen.
Does anyone have any ideas on where to start looking for a solution to this problem?
thanks
tony
The settings for the permissions of the report service are in the service's properties in IIS settings in control panel -> administrative tools. You might be able to set it to use the same password as the rest of the website there.
N' prefix required or not?
Need to provide international support for the product. Use
SqlServer 2000 for repository; DataDirect JDBC driver and
Java code for the application.
Using nvarchar datatypes for columns. Data entry/retrieval
will be through Java code. When send SQL commands through
Java/JDBC; do we need to prefix the constants with the N'
prefix example - INSERT INTO abc VALUES (N'xyz') or will
INSERT INTO abc VALUES ('xyz') store the data in unicode.
If the datatype supports unicode chars do we still need to
use the N'prefix? Also assuming data is stored in unicode;
when using in where clause while selecting will we have to
use SELECT * from abc where f1 =N'xyx' for it to be
retrieved? FYI, All database access is through Java/JDBC
which both claim to provide full international/unicode
support.
Thanks,If you are sending in hard-coded strings, then you will need to prefix them
with N to indicate that the string is Unicode. Otherwise, unicode strings
will be stored as a series of junk characters in SQL Server.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"AB" <anonymous@.discussions.microsoft.com> wrote in message
news:900901c3e9f5$a1fc1f20$a501280a@.phx.gbl...
> Need to provide international support for the product. Use
> SqlServer 2000 for repository; DataDirect JDBC driver and
> Java code for the application.
> Using nvarchar datatypes for columns. Data entry/retrieval
> will be through Java code. When send SQL commands through
> Java/JDBC; do we need to prefix the constants with the N'
> prefix example - INSERT INTO abc VALUES (N'xyz') or will
> INSERT INTO abc VALUES ('xyz') store the data in unicode.
> If the datatype supports unicode chars do we still need to
> use the N'prefix? Also assuming data is stored in unicode;
> when using in where clause while selecting will we have to
> use SELECT * from abc where f1 =N'xyx' for it to be
> retrieved? FYI, All database access is through Java/JDBC
> which both claim to provide full international/unicode
> support.
> Thanks,
SqlServer 2000 for repository; DataDirect JDBC driver and
Java code for the application.
Using nvarchar datatypes for columns. Data entry/retrieval
will be through Java code. When send SQL commands through
Java/JDBC; do we need to prefix the constants with the N'
prefix example - INSERT INTO abc VALUES (N'xyz') or will
INSERT INTO abc VALUES ('xyz') store the data in unicode.
If the datatype supports unicode chars do we still need to
use the N'prefix? Also assuming data is stored in unicode;
when using in where clause while selecting will we have to
use SELECT * from abc where f1 =N'xyx' for it to be
retrieved? FYI, All database access is through Java/JDBC
which both claim to provide full international/unicode
support.
Thanks,If you are sending in hard-coded strings, then you will need to prefix them
with N to indicate that the string is Unicode. Otherwise, unicode strings
will be stored as a series of junk characters in SQL Server.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"AB" <anonymous@.discussions.microsoft.com> wrote in message
news:900901c3e9f5$a1fc1f20$a501280a@.phx.gbl...
> Need to provide international support for the product. Use
> SqlServer 2000 for repository; DataDirect JDBC driver and
> Java code for the application.
> Using nvarchar datatypes for columns. Data entry/retrieval
> will be through Java code. When send SQL commands through
> Java/JDBC; do we need to prefix the constants with the N'
> prefix example - INSERT INTO abc VALUES (N'xyz') or will
> INSERT INTO abc VALUES ('xyz') store the data in unicode.
> If the datatype supports unicode chars do we still need to
> use the N'prefix? Also assuming data is stored in unicode;
> when using in where clause while selecting will we have to
> use SELECT * from abc where f1 =N'xyx' for it to be
> retrieved? FYI, All database access is through Java/JDBC
> which both claim to provide full international/unicode
> support.
> Thanks,
Monday, March 12, 2012
Mysterious Connections
A procedure is run that kills all connections to my SQL
server that have been idle for over 2 hours. I do this by
comparing the lastbatch time and current time. I have
noticed that with a few access databases that connect, the
last batch time is sometimes 01/01/1900. We reckon that it
is because it is of the linked tables refreshing, a
connection is present but no comands are issued.. are we
correct or is there some other reason?Hi Mat,
If you are using ADO, it will sometimes open 2 connections to the database
due to the connection pooling feature, even if you only use one. This might
be what you see.
Jacco Schalkwijk
SQL Server MVP
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:ee5e01c40c49$8f4e0f20$a301280a@.phx.gbl...
> A procedure is run that kills all connections to my SQL
> server that have been idle for over 2 hours. I do this by
> comparing the lastbatch time and current time. I have
> noticed that with a few access databases that connect, the
> last batch time is sometimes 01/01/1900. We reckon that it
> is because it is of the linked tables refreshing, a
> connection is present but no comands are issued.. are we
> correct or is there some other reason?
server that have been idle for over 2 hours. I do this by
comparing the lastbatch time and current time. I have
noticed that with a few access databases that connect, the
last batch time is sometimes 01/01/1900. We reckon that it
is because it is of the linked tables refreshing, a
connection is present but no comands are issued.. are we
correct or is there some other reason?Hi Mat,
If you are using ADO, it will sometimes open 2 connections to the database
due to the connection pooling feature, even if you only use one. This might
be what you see.
Jacco Schalkwijk
SQL Server MVP
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:ee5e01c40c49$8f4e0f20$a301280a@.phx.gbl...
> A procedure is run that kills all connections to my SQL
> server that have been idle for over 2 hours. I do this by
> comparing the lastbatch time and current time. I have
> noticed that with a few access databases that connect, the
> last batch time is sometimes 01/01/1900. We reckon that it
> is because it is of the linked tables refreshing, a
> connection is present but no comands are issued.. are we
> correct or is there some other reason?
MYSQL vs SQLServer
Hi
Please, i want to know advantages and disadv. of sqlserver and mysql.
OR
When to use each of them?
OR
Which is better?
OR
Any thing to compare?
Any information?
ThanxOriginally posted by Taymoor
Hi
Please, i want to know advantages and disadv. of sqlserver and mysql.
OR
When to use each of them?
OR
Which is better?
OR
Any thing to compare?
Any information?
Thanx
Advantages
----
MySQL is cheaper!
MySQL runs on Linux, Unix, Windows OS!
Dis-Advantages
------
MySQL does not support database Views.
MySQL does not support Stored Procedures.
MySQL does not support Triggers.
MySQL does not support sub-queries.
You must configure MySQL with the InnoDB storage engine if you want transaction-safe(rollback, commit, unit-of-work) tables, and referential integrity (foreign keys).
SQL Server comes with much nicer client tools.
SHAMELESS PLUG -
But, you can always get nice heterogeneous database UI tools at:
http://www.yazoosoft.com
Please, i want to know advantages and disadv. of sqlserver and mysql.
OR
When to use each of them?
OR
Which is better?
OR
Any thing to compare?
Any information?
ThanxOriginally posted by Taymoor
Hi
Please, i want to know advantages and disadv. of sqlserver and mysql.
OR
When to use each of them?
OR
Which is better?
OR
Any thing to compare?
Any information?
Thanx
Advantages
----
MySQL is cheaper!
MySQL runs on Linux, Unix, Windows OS!
Dis-Advantages
------
MySQL does not support database Views.
MySQL does not support Stored Procedures.
MySQL does not support Triggers.
MySQL does not support sub-queries.
You must configure MySQL with the InnoDB storage engine if you want transaction-safe(rollback, commit, unit-of-work) tables, and referential integrity (foreign keys).
SQL Server comes with much nicer client tools.
SHAMELESS PLUG -
But, you can always get nice heterogeneous database UI tools at:
http://www.yazoosoft.com
Friday, March 9, 2012
MySQL linked in SQLServer
:confused: Hallo! I'm trying to link a MySql 's DB in SQLServer with ODBC: the operation was susseful but I'm not able to read and write/update the data.
HELP ME PLEASE!!!
silvia, ItalyI'm confused by "the operation was successful" followed by "I'm not able to read and write/update the data". I don't understand what you mean.
Check out sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp). If you tinker with the link a bit, you ought to be able to get it in Italian if that helps.
The quickest and easiest way is to use the ODBC link that uses a system DSN. That way if you change MySQL servers all you do is fix the DSN and your SQL Server link works.
A more complex but more powerful way is to use the ODBC with a connection string. It is kind of like a car with a manual transmission... You have more control, but you have to pay more attention to how you use it.
-PatP|||IF I RIGHT CLICK IN A TABLE IN THE REMOTE SERVER, I FIND ONLY "COPY" AND "?". IT'S CORRECT??
ciao e molte grazie!!!
silvia|||Without knowing a lot about your system(s), I can only guess. What you describe is one possible outcome of a linked server to MySQL that is actually fairly common.
There are two problems that commonly interfere with cross-server operations.
The most common problem is that the MySQL login used by the ODBC connection doesn't have sufficient permissions within MySQL. The only real solution for that problem is to get more permissions by either changing the MySQL login used by the ODBC connection to one that has more permissions, or by getting the MySQL administrator to grant the MySQL login used by the ODBC connection more permissions.
The next most common problem is that the MySQL ODBC driver on the SQL Server itself is old. The ODBC drivers have improved a lot over time. Older drivers (sometimes even a few months can make a real difference) are often a problem. Visit the MySQL site to see if you can get newer ODBC drivers.
-PatP|||I have set up a link server to MySQL 5
Sometimes mySQL do require a password. The following link server works for me.
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’
If MSDTC is running you can use the following to create a view without a link server.
SELECT * FROM OPENROWSET(’MSDASQL’,'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’, ‘SELECT * from table’)
If you get an error like
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].pensionNo'. The expected data length is 7, while the returned data length is 5.
You can resolve this using the option tag
'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;option=512 ;uid=username;pwd=password'
Notice the option=512
HELP ME PLEASE!!!
silvia, ItalyI'm confused by "the operation was successful" followed by "I'm not able to read and write/update the data". I don't understand what you mean.
Check out sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp). If you tinker with the link a bit, you ought to be able to get it in Italian if that helps.
The quickest and easiest way is to use the ODBC link that uses a system DSN. That way if you change MySQL servers all you do is fix the DSN and your SQL Server link works.
A more complex but more powerful way is to use the ODBC with a connection string. It is kind of like a car with a manual transmission... You have more control, but you have to pay more attention to how you use it.
-PatP|||IF I RIGHT CLICK IN A TABLE IN THE REMOTE SERVER, I FIND ONLY "COPY" AND "?". IT'S CORRECT??
ciao e molte grazie!!!
silvia|||Without knowing a lot about your system(s), I can only guess. What you describe is one possible outcome of a linked server to MySQL that is actually fairly common.
There are two problems that commonly interfere with cross-server operations.
The most common problem is that the MySQL login used by the ODBC connection doesn't have sufficient permissions within MySQL. The only real solution for that problem is to get more permissions by either changing the MySQL login used by the ODBC connection to one that has more permissions, or by getting the MySQL administrator to grant the MySQL login used by the ODBC connection more permissions.
The next most common problem is that the MySQL ODBC driver on the SQL Server itself is old. The ODBC drivers have improved a lot over time. Older drivers (sometimes even a few months can make a real difference) are often a problem. Visit the MySQL site to see if you can get newer ODBC drivers.
-PatP|||I have set up a link server to MySQL 5
Sometimes mySQL do require a password. The following link server works for me.
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’
If MSDTC is running you can use the following to create a view without a link server.
SELECT * FROM OPENROWSET(’MSDASQL’,'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’, ‘SELECT * from table’)
If you get an error like
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].pensionNo'. The expected data length is 7, while the returned data length is 5.
You can resolve this using the option tag
'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;option=512 ;uid=username;pwd=password'
Notice the option=512
MYSQL - DTS - MSSQL help needed Warning novice inside!
Hi,
**I appologise as this is posted in microsoft.public.sqlserver.dts
also**
Would anyone be so kind and point me in the rigth direction?
I have a mysql server and a MSSQL 2000 server.
MYSQL
Database name is Membership
Table is Users
Column is email_address.
I want to transfer the email_address data over to a column in an
existing MSSQL Database.
When I run the DTS wizard I can successfully connect to the MYSQL
database server, but the only option I get is to use a SQL Statement.
So this is what I have started with...
mysql_select_db('membership');
SELECT * FROM users WHERE email_address='"*"
If i try and preview I get an unexpected errror and if i run through
the wizard I get wrong systax at line 1.
I am a total novice at this and am stumbling around in the dark...
Any help much appreciated...
Thanks
Col
**Sorry this is posted in microsoft.public.sqlserver.dts also..**Sorry to add I need this to be automated which is why I am trying to
use DTS
Thanks
Col|||Do you want to transfer all email addresses?
If so, I think this is what you want to do
SELECT * FROM membership.users
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techcs" <colinsealeaf@.blueyonder.co.uk> wrote in message
news:1158045662.036499.225710@.e3g2000cwe.googlegroups.com...
> Hi,
> **I appologise as this is posted in microsoft.public.sqlserver.dts
> also**
> Would anyone be so kind and point me in the rigth direction?
>
> I have a mysql server and a MSSQL 2000 server.
>
> MYSQL
> Database name is Membership
> Table is Users
> Column is email_address.
>
> I want to transfer the email_address data over to a column in an
> existing MSSQL Database.
>
> When I run the DTS wizard I can successfully connect to the MYSQL
> database server, but the only option I get is to use a SQL Statement.
> So this is what I have started with...
>
> mysql_select_db('membership');
> SELECT * FROM users WHERE email_address='"*"
>
> If i try and preview I get an unexpected errror and if i run through
> the wizard I get wrong systax at line 1.
>
> I am a total novice at this and am stumbling around in the dark...
>
> Any help much appreciated...
>
> Thanks
> Col
> **Sorry this is posted in microsoft.public.sqlserver.dts also..**
>|||techcs wrote:
> Sorry to add I need this to be automated which is why I am trying to
> use DTS
> Thanks
> Col
>
Automation doesn't require DTS. If you can connect to mysql with DTS,
then you can connect with other components as well. Create a linked
server that points to the mysql instance, and then you can use a simple
SELECT statement to grab the data. The SELECT statement can then be
scheduled as an Agent job. Something like this:
SELECT email_address FROM linkedservername.Membership.dbo.Users
I don't know MySQL, so I'm not sure of the exact syntax, you might not
need the schema reference, i.e. "dbo".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks will have a look at that now... Sounds good...
Col
**I appologise as this is posted in microsoft.public.sqlserver.dts
also**
Would anyone be so kind and point me in the rigth direction?
I have a mysql server and a MSSQL 2000 server.
MYSQL
Database name is Membership
Table is Users
Column is email_address.
I want to transfer the email_address data over to a column in an
existing MSSQL Database.
When I run the DTS wizard I can successfully connect to the MYSQL
database server, but the only option I get is to use a SQL Statement.
So this is what I have started with...
mysql_select_db('membership');
SELECT * FROM users WHERE email_address='"*"
If i try and preview I get an unexpected errror and if i run through
the wizard I get wrong systax at line 1.
I am a total novice at this and am stumbling around in the dark...
Any help much appreciated...
Thanks
Col
**Sorry this is posted in microsoft.public.sqlserver.dts also..**Sorry to add I need this to be automated which is why I am trying to
use DTS
Thanks
Col|||Do you want to transfer all email addresses?
If so, I think this is what you want to do
SELECT * FROM membership.users
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techcs" <colinsealeaf@.blueyonder.co.uk> wrote in message
news:1158045662.036499.225710@.e3g2000cwe.googlegroups.com...
> Hi,
> **I appologise as this is posted in microsoft.public.sqlserver.dts
> also**
> Would anyone be so kind and point me in the rigth direction?
>
> I have a mysql server and a MSSQL 2000 server.
>
> MYSQL
> Database name is Membership
> Table is Users
> Column is email_address.
>
> I want to transfer the email_address data over to a column in an
> existing MSSQL Database.
>
> When I run the DTS wizard I can successfully connect to the MYSQL
> database server, but the only option I get is to use a SQL Statement.
> So this is what I have started with...
>
> mysql_select_db('membership');
> SELECT * FROM users WHERE email_address='"*"
>
> If i try and preview I get an unexpected errror and if i run through
> the wizard I get wrong systax at line 1.
>
> I am a total novice at this and am stumbling around in the dark...
>
> Any help much appreciated...
>
> Thanks
> Col
> **Sorry this is posted in microsoft.public.sqlserver.dts also..**
>|||techcs wrote:
> Sorry to add I need this to be automated which is why I am trying to
> use DTS
> Thanks
> Col
>
Automation doesn't require DTS. If you can connect to mysql with DTS,
then you can connect with other components as well. Create a linked
server that points to the mysql instance, and then you can use a simple
SELECT statement to grab the data. The SELECT statement can then be
scheduled as an Agent job. Something like this:
SELECT email_address FROM linkedservername.Membership.dbo.Users
I don't know MySQL, so I'm not sure of the exact syntax, you might not
need the schema reference, i.e. "dbo".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks will have a look at that now... Sounds good...
Col
MYSQL - DTS - MSSQL help needed Warning novice inside!
Hi,
**I appologise as this is posted in microsoft.public.sqlserver.dts
also**
Would anyone be so kind and point me in the rigth direction?
I have a MYSQL server and a MSSQL 2000 server.
MYSQL
Database name is Membership
Table is Users
Column is email_address.
I want to transfer the email_address data over to a column in an
existing MSSQL Database.
When I run the DTS wizard I can successfully connect to the MYSQL
database server, but the only option I get is to use a SQL Statement.
So this is what I have started with...
mysql_select_db('membership');
SELECT * FROM users WHERE email_address='"*"
If i try and preview I get an unexpected errror and if i run through
the wizard I get wrong systax at line 1.
I am a total novice at this and am stumbling around in the dark...
Any help much appreciated...
Thanks
Col
**Sorry this is posted in microsoft.public.sqlserver.dts also..**Sorry to add I need this to be automated which is why I am trying to
use DTS
Thanks
Col|||Do you want to transfer all email addresses?
If so, I think this is what you want to do
SELECT * FROM membership.users
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techcs" <colinsealeaf@.blueyonder.co.uk> wrote in message
news:1158045662.036499.225710@.e3g2000cwe.googlegroups.com...
> Hi,
> **I appologise as this is posted in microsoft.public.sqlserver.dts
> also**
> Would anyone be so kind and point me in the rigth direction?
>
> I have a MYSQL server and a MSSQL 2000 server.
>
> MYSQL
> Database name is Membership
> Table is Users
> Column is email_address.
>
> I want to transfer the email_address data over to a column in an
> existing MSSQL Database.
>
> When I run the DTS wizard I can successfully connect to the MYSQL
> database server, but the only option I get is to use a SQL Statement.
> So this is what I have started with...
>
> mysql_select_db('membership');
> SELECT * FROM users WHERE email_address='"*"
>
> If i try and preview I get an unexpected errror and if i run through
> the wizard I get wrong systax at line 1.
>
> I am a total novice at this and am stumbling around in the dark...
>
> Any help much appreciated...
>
> Thanks
> Col
> **Sorry this is posted in microsoft.public.sqlserver.dts also..**
>|||techcs wrote:
> Sorry to add I need this to be automated which is why I am trying to
> use DTS
> Thanks
> Col
>
Automation doesn't require DTS. If you can connect to MySQL with DTS,
then you can connect with other components as well. Create a linked
server that points to the MySQL instance, and then you can use a simple
SELECT statement to grab the data. The SELECT statement can then be
scheduled as an Agent job. Something like this:
SELECT email_address FROM linkedservername.Membership.dbo.Users
I don't know MySQL, so I'm not sure of the exact syntax, you might not
need the schema reference, i.e. "dbo".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks will have a look at that now... Sounds good...
Col
**I appologise as this is posted in microsoft.public.sqlserver.dts
also**
Would anyone be so kind and point me in the rigth direction?
I have a MYSQL server and a MSSQL 2000 server.
MYSQL
Database name is Membership
Table is Users
Column is email_address.
I want to transfer the email_address data over to a column in an
existing MSSQL Database.
When I run the DTS wizard I can successfully connect to the MYSQL
database server, but the only option I get is to use a SQL Statement.
So this is what I have started with...
mysql_select_db('membership');
SELECT * FROM users WHERE email_address='"*"
If i try and preview I get an unexpected errror and if i run through
the wizard I get wrong systax at line 1.
I am a total novice at this and am stumbling around in the dark...
Any help much appreciated...
Thanks
Col
**Sorry this is posted in microsoft.public.sqlserver.dts also..**Sorry to add I need this to be automated which is why I am trying to
use DTS
Thanks
Col|||Do you want to transfer all email addresses?
If so, I think this is what you want to do
SELECT * FROM membership.users
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techcs" <colinsealeaf@.blueyonder.co.uk> wrote in message
news:1158045662.036499.225710@.e3g2000cwe.googlegroups.com...
> Hi,
> **I appologise as this is posted in microsoft.public.sqlserver.dts
> also**
> Would anyone be so kind and point me in the rigth direction?
>
> I have a MYSQL server and a MSSQL 2000 server.
>
> MYSQL
> Database name is Membership
> Table is Users
> Column is email_address.
>
> I want to transfer the email_address data over to a column in an
> existing MSSQL Database.
>
> When I run the DTS wizard I can successfully connect to the MYSQL
> database server, but the only option I get is to use a SQL Statement.
> So this is what I have started with...
>
> mysql_select_db('membership');
> SELECT * FROM users WHERE email_address='"*"
>
> If i try and preview I get an unexpected errror and if i run through
> the wizard I get wrong systax at line 1.
>
> I am a total novice at this and am stumbling around in the dark...
>
> Any help much appreciated...
>
> Thanks
> Col
> **Sorry this is posted in microsoft.public.sqlserver.dts also..**
>|||techcs wrote:
> Sorry to add I need this to be automated which is why I am trying to
> use DTS
> Thanks
> Col
>
Automation doesn't require DTS. If you can connect to MySQL with DTS,
then you can connect with other components as well. Create a linked
server that points to the MySQL instance, and then you can use a simple
SELECT statement to grab the data. The SELECT statement can then be
scheduled as an Agent job. Something like this:
SELECT email_address FROM linkedservername.Membership.dbo.Users
I don't know MySQL, so I'm not sure of the exact syntax, you might not
need the schema reference, i.e. "dbo".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks will have a look at that now... Sounds good...
Col
Monday, February 20, 2012
my issues with FT Search
Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
- FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
- Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
- Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
- Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!On 25 Jul 2005 18:35:20 -0700, barak.benezer@.gmail.com wrote:
>Hi, I am trying to implement a global full text search on our SQL
>Server. Our app has several entities that are stored in the DB. I would
>like to be able to search for 'John Doe' and get results in all types
>of entities. Problem is:
(snip)
>I would greatly appreciate any suggestions/tips/info/workaround.
Hi barak,
I think that your message will attract more answers if posted to the
group microsoft.public.sqlserver.fulltext, where the FTS experts prefer
to hang out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks for your reply. I posted it there and got an answer.
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
- FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
- Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
- Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
- Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!On 25 Jul 2005 18:35:20 -0700, barak.benezer@.gmail.com wrote:
>Hi, I am trying to implement a global full text search on our SQL
>Server. Our app has several entities that are stored in the DB. I would
>like to be able to search for 'John Doe' and get results in all types
>of entities. Problem is:
(snip)
>I would greatly appreciate any suggestions/tips/info/workaround.
Hi barak,
I think that your message will attract more answers if posted to the
group microsoft.public.sqlserver.fulltext, where the FTS experts prefer
to hang out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks for your reply. I posted it there and got an answer.
Subscribe to:
Posts (Atom)