mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

manually truncate a log

I have a log.ldf file that has grown to around 150 GB ... I can't really do a
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..
How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..
|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:

> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>
|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Manually Delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
Fabio
I'm curious as to why you can't delete it in EM. This could be symptomatic
of larger problems.
To delete it I would use the following commands in Query Analyzer - where
test1234 is your catalog name.
declare @.int int
declare @.string varchar(200)
Create table holding
(
TABLE_OWNER sysname,
TABLE_NAME sysname,
FULLTEXT_KEY_INDEX_NAME sysname,
FULLTEXT_KEY_COLID int,
FULLTEXT_INDEX_ACTIVE int,
FULLTEXT_CATALOG_NAME sysname)
insert into holding
exec sp_help_fulltext_tables 'test1234'
select @.int = @.@.rowcount
while @.int>0
begin
select @.string='sp_fulltext_table ''' +table_name+''',''drop''' from holding
exec (@.string)
delete from holding
select @.int=@.int-1
end
exec sp_fulltext_catalog 'test1234','drop'
GO
Hilary Cotter
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
"Fabio" <fabio@.glb.com.br> wrote in message
news:OUGTUkSCFHA.520@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>
|||I don't know why I can't delete it in EM, whem I select the folder Full-Text
Catalog, the EM just freeze!
So I can't delete the Catalog or even know it name!!!
How Can I List the catalogs in my database?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uiCUS1TCFHA.1836@.tk2msftngp13.phx.gbl...
> I'm curious as to why you can't delete it in EM. This could be
symptomatic
> of larger problems.
> To delete it I would use the following commands in Query Analyzer - where
> test1234 is your catalog name.
> declare @.int int
> declare @.string varchar(200)
> Create table holding
> (
> TABLE_OWNER sysname,
> TABLE_NAME sysname,
> FULLTEXT_KEY_INDEX_NAME sysname,
> FULLTEXT_KEY_COLID int,
> FULLTEXT_INDEX_ACTIVE int,
> FULLTEXT_CATALOG_NAME sysname)
> insert into holding
> exec sp_help_fulltext_tables 'test1234'
> select @.int = @.@.rowcount
> while @.int>0
> begin
> select @.string='sp_fulltext_table ''' +table_name+''',''drop''' from
holding
> exec (@.string)
> delete from holding
> select @.int=@.int-1
> end
> exec sp_fulltext_catalog 'test1234','drop'
> GO
> --
> Hilary Cotter
> 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
> "Fabio" <fabio@.glb.com.br> wrote in message
> news:OUGTUkSCFHA.520@.TK2MSFTNGP09.phx.gbl...
>
|||try this sp_help_fulltext_catalogs
Hilary Cotter
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
"Fabio" <fabio@.glb.com.br> wrote in message
news:OuJa5HWCFHA.3596@.TK2MSFTNGP12.phx.gbl...
> I don't know why I can't delete it in EM, whem I select the folder
Full-Text[vbcol=seagreen]
> Catalog, the EM just freeze!
> So I can't delete the Catalog or even know it name!!!
> How Can I List the catalogs in my database?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uiCUS1TCFHA.1836@.tk2msftngp13.phx.gbl...
> symptomatic
where
> holding
>

Manually delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
Fabio
See sp_fulltext_catalog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fabio" <fabio@.glb.com.br> wrote in message news:eLCvRlTCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>

Managing SQL Server remotely

I've combed through SQL Help to find the answer to my question but I think it's telling me it can't be done. I work both from an office with my servers and from home. When I'm at home I would like to access my SQL server remotely using a tool such as MS SQL Server Management Studio. But it appears there is no way to access my SQL Server for management purposes using Management Studio over a remote internet connection. I can access the server using Management Studio while I'm on the internal office network but not from home.

Has anyone been able to do this or might recommend a third party tool as robust as Management Studio?

Thanks

From what I know, either you can remote into that machine and manage your SQL Server, or if your SQL Server has a dedicated IP you can register the server on your local machine and manage the server from there.

|||

I can find no place in Management Studio (linked or registered) to enter an IP number? If anyone stumbles on documentation to pull this off I'd sure appreciate it. It appears all references to 'remote' in the help files means within the same internal network.

Again, a third party tool is doable.

|||

I used File > Connect Object Explorer..., and in the 'Server Name' field, typed the IP of my Database server.

|||

So you have been able to connect to SQL off your network usng Management Studio. That is good news. However, I keep getting errors but I probably don't have all my ducks in a row on settings. I tried a raw IP, then raw IP with server name (67.81.4.38/ServerName) and then with the http (http://67.81.4.38/SeverName) and still no luck.

I get the feeling I need to open a port or two on the server. Some documentation sure would help. I'll keep at it but if anyone knows of some good documentation please let me know. Thanks.

TITLE: Connect to Server
----------

Cannot connect to XX.XX.XX.X.

----------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

|||

Hi,

You may try these steps below.

1, Enable the remote connection of your SQLServer. Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager-> select SQL Server 2005 Network Configuration, right click on the "TCP/IP", enabled.

2, you can use ping command to check if the remote server can be accessible
3, because the default port of SqlServer is 1433. So you can use telnet command (such as telnet x.x.x.x 1433) to check if the port works. If error occurs in this step, you may
try to check the following issue:

1) If the SqlServer service is running on your remote machine
2) Check if the SQL Server port number is 1433. (If not, you may use your customerize port and retry step2 ).
3) Open your firewall, check if the 1433 port has been forbidden.

4, Try to use enterprise manager or query analyzer to connect the romote server.If the error still occurs, maybe the authentication mode on your remote SqlServer is Windows Authentication Only.You should enable the SqlServer authentication and restart yourSqlServer. (Right click on your server node, choose properties, switch to security tab, choose "SQL Server and Windows Authentication Mode")

Hope that helps. Thanks.

|||

Thank you so much. I'm going to go in tomorrow and try it out. You'll probably be hearing back from me.

Management tools is installed but i cant find it...

Hello
I just installed Visual Studio 2005 Rc1, after that i installed Sql server
2005 september ctp.
Everything worked out fine and i installed everyprogram and all the tools
from sql server 2005 september ctp.
But when i try to find management studio i cant find it... its not under
Start Menu\Programs\Microsoft SQL Server 2005 CTP
the only thing thats under Microsoft SQL Server 2005 CTP is configuration
tools...
do i need to do some configuration to make management studio works?
On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:

>Hello
>I just installed Visual Studio 2005 Rc1, after that i installed Sql server
>2005 september ctp.
>Everything worked out fine and i installed everyprogram and all the tools
>from sql server 2005 september ctp.
>But when i try to find management studio i cant find it... its not under
>Start Menu\Programs\Microsoft SQL Server 2005 CTP
>the only thing thats under Microsoft SQL Server 2005 CTP is configuration
>tools...
>do i need to do some configuration to make management studio works?
>
One common cause for not having the SQL Server Management Studio is
that you installed SQL Server Express Edition. It doesn't have SQL
Server Management Studio.
Another possibility is that you didn't choose to install SQL Server
Management Studio during Setup.
If you don't already have it, find a copy of SQL Server 2005 Developer
Edition (or above). Click the Advanced button during setup and study
the options for installation on the Advanced screen carefully.
Then you should be in good shape.
Andrew Watt
MVP - InfoPath
|||Hello Andrew.
When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
after i finished that installation i installed sql server 2005 september ctp
(standard edition) in an new instance.
i have removed the sql 2005 september ctp and installed it again but its
still not there.
when im installing sql server 2005 september ctp im choosing all the
checkbox when i come to the point where i get the question what programs i
want to install.
when i read your answere i wonder if i should remove both vs2005 rc1 and sql
server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
and then install sql server 2005 september ctp again.
Best regards
J?rgen
"Andrew Watt [MVP - InfoPath]" wrote:

> On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
> One common cause for not having the SQL Server Management Studio is
> that you installed SQL Server Express Edition. It doesn't have SQL
> Server Management Studio.
> Another possibility is that you didn't choose to install SQL Server
> Management Studio during Setup.
> If you don't already have it, find a copy of SQL Server 2005 Developer
> Edition (or above). Click the Advanced button during setup and study
> the options for installation on the Advanced screen carefully.
> Then you should be in good shape.
> Andrew Watt
> MVP - InfoPath
>
|||Jorgen,
As a first step make sure you click the Advanced button during SQL
Server 2005 setup.
On the advanced screen make sure you notice the visual difference
between the install this component and the install this component and
all its subcomponent options.
It's easy to miss out some desired subcomponents.
If that works, then fine.
But if not ...
The recommended install order is SQL Server 2005 then Visual Studo
2005. So if the simpler approach doesn't work that looks like the way
to go.
Check after installing SQL Server that SQL Server Management Studio
has installed. It should be in Start|All Programs|Microsoft SQL Server
2005 CTP.
Andrew Watt
MVP - InfoPath
On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hello Andrew.
>When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
>after i finished that installation i installed sql server 2005 september ctp
>(standard edition) in an new instance.
>i have removed the sql 2005 september ctp and installed it again but its
>still not there.
>when im installing sql server 2005 september ctp im choosing all the
>checkbox when i come to the point where i get the question what programs i
>want to install.
>when i read your answere i wonder if i should remove both vs2005 rc1 and sql
>server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
>and then install sql server 2005 september ctp again.
>Best regards
>Jrgen
>"Andrew Watt [MVP - InfoPath]" wrote:
|||Andrew,
Thank you very much for you quick support.
The problem was solved when i choosed advanced options in the setup just as
you suggested.
I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
Thanks allot!
"Andrew Watt [MVP - InfoPath]" wrote:

> Jorgen,
> As a first step make sure you click the Advanced button during SQL
> Server 2005 setup.
> On the advanced screen make sure you notice the visual difference
> between the install this component and the install this component and
> all its subcomponent options.
> It's easy to miss out some desired subcomponents.
> If that works, then fine.
> But if not ...
> The recommended install order is SQL Server 2005 then Visual Studo
> 2005. So if the simpler approach doesn't work that looks like the way
> to go.
> Check after installing SQL Server that SQL Server Management Studio
> has installed. It should be in Start|All Programs|Microsoft SQL Server
> 2005 CTP.
> Andrew Watt
> MVP - InfoPath
> On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
>
>
|||You're welcome.
Andrew Watt
MVP - InfoPath
On Sat, 8 Oct 2005 04:31:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Andrew,
>Thank you very much for you quick support.
>The problem was solved when i choosed advanced options in the setup just as
>you suggested.
>I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
>Thanks allot!
>"Andrew Watt [MVP - InfoPath]" wrote: