Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts

Wednesday, March 28, 2012

Problems with SqlExecutionModes.CaptureSql

After setting the Server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql and run an object.Alter() the only thing that shows up in Server.ConnectionContext.CapturedSQL.Text is the Servername and Instance twice.I was wondering if anyone else was having similar problems or what I might be doing wrong.

Thanks

Bradley

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{

Server s = new Server(@.".\SQLExpress");
s.ConnectionContext.LoginSecure = true;
s.ConnectionContext.Connect();
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Table t = s.Databases["DBToolsA"].Tables[0];
Column c = new Column(t, "Giddy", DataType.Bit);
t.Columns.Add(c);
t.Alter();

foreach (string str in s.ConnectionContext.CapturedSql.Text)
{
Console.WriteLine(s);
}
Console.ReadLine();

}
}
}

Okay so the code would work if I changed

foreach(string str in s.ConnectionContext.CapturedSql.Text)
{

Console.WriteLine(s);

}

To:

foreach(string str in s.ConnectionContext.CapturedSql.Text)
{

Console.WriteLine(str);

}

Guess that's what happens when you look at the same problem for to long.

Bradley

Wednesday, March 21, 2012

Problems with Oracle character setting

Hi there,

I am using an ADO Oracle Connector to store Oracle data to an SQL Server.
When I map the input data to the OLEDB destination I get the Error msg that
unicode (DT_WSTR) cannot be mapped to 1292 character code (DT_STR).

To solve this I put a Data Conversion Transformation between Source and Destination.
That would mean a lot of work if I couldn't map the data directly from Source to Destination
for all the tables of my project.

I checked the NLS-Settings from the Oracle db:
select * from sys.v_$nls_parameters
NLS_CHARACTERSET -> WE8ISO8859P1

It's really a mystery to me why the DataReader converts Latin-1 to unicode which has to be
converted to Latin-1 again.
Could someone please help me out?

Fridtjof
The problem here is with managed code. The CLR doesn't have a ANSI type string so all strings are converted to Unicode. So in actuality, it is not the datareader that does the conversion but Oracle's ADO.NET connector. Obviously, knowing this doesn't really help you out all that much though. Is there any way you can make the SQL Server table be unicode instead of ANSI. If not then data conversion is the only solution other than to get an OLEDB driver for Oracle, since OLEDB does support ANSI directly.

HTH,
Matt|||Matt,

you're right. I've tried to download Oracle data via OLEDB which nags that it cannot read Oracle's character setting and assumes it to be 1292 (or was it 1252?). But it downloads the data correctly.

Unfortunately I cannot set the password property in a Package configuration. See this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161564&SiteID=1

This way I'm not getting much further. One have to ponder between the different restrictions :(
Thanks anyway
Fridtjof

Problems with multiple constraints

Hi all,

I am having a problem setting multiple constraints over a table from other table. To be more clear, I have the following code:

ALTER TABLE Solicitud_Servicio

DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio;

ALTER TABLE Solicitud_Servicio

DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio1;

ALTER TABLE Solicitud_Servicio ADD CONSTRAINT

FK_Solicitud_Servicio_Domicilio1 FOREIGN KEY

(

ID_Domicilio_Envio

) REFERENCES Domicilio

(

ID_Domicilio

) ON UPDATE CASCADE;

ALTER TABLE Solicitud_Servicio ADD CONSTRAINT

FK_Solicitud_Servicio_Domicilio FOREIGN KEY

(

ID_Domicilio_Facturacion

) REFERENCES Domicilio

(

ID_Domicilio

) ON UPDATE CASCADE;

As you see, I want an update on the field Domicilio.ID_Domicilio to change the fields Solicitud_Servicio.ID_Domicilio_Envio and Solicitud_Servicio.ID_Domicilio_Facturacion from my point of view there should be no problem and the weird part is that if I try this with only one constraint (either one) it works, as soon as I try to apply the other constraint I get a very annoying

“The referential relationship will result in a cyclical reference that is not allowed”

Maybe Sql Server Mobile 2005 does not support multiple constraints on a table from the same other table?

Thanks,

Flaker

Thats true. SqlCE doesn't support multiple cascade update/delete in this case. How ever it could be one update and one delete.

Thanks

Raja

Problems with multiple constraints

Hi all,

I am having a problem setting multiple constraints over a table from other table. To be more clear, I have the following code:

ALTER TABLE Solicitud_Servicio

DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio;

ALTER TABLE Solicitud_Servicio

DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio1;

ALTER TABLE Solicitud_Servicio ADD CONSTRAINT

FK_Solicitud_Servicio_Domicilio1 FOREIGN KEY

(

ID_Domicilio_Envio

) REFERENCES Domicilio

(

ID_Domicilio

) ON UPDATE CASCADE;

ALTER TABLE Solicitud_Servicio ADD CONSTRAINT

FK_Solicitud_Servicio_Domicilio FOREIGN KEY

(

ID_Domicilio_Facturacion

) REFERENCES Domicilio

(

ID_Domicilio

) ON UPDATE CASCADE;

As you see, I want an update on the field Domicilio.ID_Domicilio to change the fields Solicitud_Servicio.ID_Domicilio_Envio and Solicitud_Servicio.ID_Domicilio_Facturacion from my point of view there should be no problem and the weird part is that if I try this with only one constraint (either one) it works, as soon as I try to apply the other constraint I get a very annoying

“The referential relationship will result in a cyclical reference that is not allowed”

Maybe Sql Server Mobile 2005 does not support multiple constraints on a table from the same other table?

Thanks,

Flaker

Thats true. SqlCE doesn't support multiple cascade update/delete in this case. How ever it could be one update and one delete.

Thanks

Raja

Saturday, February 25, 2012

Problems with Autogrowth feature

I cannot via SSMS change the autogrowth setting for a database. It is currently set to restricted and I want it set to unrestricted. I make the change, click ok and no error appears but when I open it again the change is not there.

I also tried creating a new database and noticed that the autogrowth setting of unrestricted is not copied from the model db to the new one.

This happens only with the log file. I have SQL 2005 SP1. Any help is appreciated.

What recovery model are you using, Simple or Full?|||I'm using Full recovery mode.|||

Hi Heather,

The maximum size for a log file in 2005 is 2TB. I'm betting that although filesize is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB). So, within the confines of capacity limitations, it is unlimited so to speak

Cheers,
Rob

Problems with Autogrowth feature

I cannot via SSMS change the autogrowth setting for a database. It is currently set to restricted and I want it set to unrestricted. I make the change, click ok and no error appears but when I open it again the change is not there.

I also tried creating a new database and noticed that the autogrowth setting of unrestricted is not copied from the model db to the new one.

This happens only with the log file. I have SQL 2005 SP1. Any help is appreciated.

What recovery model are you using, Simple or Full?|||I'm using Full recovery mode.|||

Hi Heather,

The maximum size for a log file in 2005 is 2TB. I'm betting that although filesize is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB). So, within the confines of capacity limitations, it is unlimited so to speak

Cheers,
Rob