Cant get a simple code to work

Can someone please help me fix this. The if statement always runs. I only want it to run if the statement is true and there is a result coming back. If the productID does not have any magazines linking to it, I don't want the If statement to run. Thanks in advance.

ASP.NET code:

If Not Magazine.GetMagazinesForProduct(ProductID) Is Nothing Then
blanklabel.Text = categoryDetails.Spacer
blanklabel.Visible = True
blanklabel2.Text = categoryDetails.Spacer
blanklabel2.Visible = True
magazinerecommendedlabel.Text = "Recommended/Featured in the following magazine(s):"
magazinerecommendedlabel.Visible = True
End If
--------

magazine class:

Public Function GetMagazinesForProduct(ByVal productID As String) As SqlDataReader
Dim connection As New SqlConnection(connectionString)

Dim command As New SqlCommand("GetMagazinesForProduct", connection)
command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@.ProductID", SqlDbType.VarChar, 50)
command.Parameters("@.ProductID").Value = productID

connection.Open()

Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function

--------

Stored Procedure:

CREATE PROCEDURE GetMagazinesForProduct
(@.ProductID varchar)
AS
SELECT Magazine.[Name], Magazine.[Issue], Magazine.SmallImagePath
FROM Magazine INNER JOIN MagazineProduct
ON Magazine.MagazineID = MagazineProduct.MagazineID
WHERE MagazineProduct.ProductID = @.ProductID
RETURN
GO

--------How about trying a while instead? I'm not sure of the exact VB syntax, maybe something like this:

dim myReader as Magazine.GetMagazinesForProduct(ProductID)
While myReader .Read()
 .. do your stuff
end while

|||for some reason, myReader.Read() always executes FALSE. Why is that?|||It means there's no data in present. Are you positive your stored proc is returning data for the id you are passing to it?

Cant fetch record from Cursor

Hi,

I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks

--

DECLARE @.CreatedByUsernchar(100),@.WorkflowIDsvarchar(50);

DECLARE @.MyVariableCURSOR;

SET @.MyVariable=CURSORFOR

SELECTisnull(Filters.WorkflowIDs,''),

isnull(FilterElements.CreatedByUser,'')

FROMFiltersRIGHTOUTERJOIN

FilterElementsON Filters.ItemID= FilterElements.FiltersItemID

WHERE FiltersItemID= @.FilterID;

OPEN @.MyVariable;

FETCH NEXTFROM @.MyVariable

INTO @.WorkflowIDs, @.CreatedByUser;

For Table A and B,

1. if you dont have any corresponding entries for a record in A in B, and still wants to display the record in A, you should be using LEFT OUTER JOIN

2. if you dont have any records in B for a record in A, but have records for other records in A and still wants to display all the records in B, then you should be using RIGHT OUTER JOIN

Thanks

-Mark the post(s) as "Answer" that helped you|||

Everything seems to be correct. I had tried using the steps u followed, I am getting results for that,

DECLARE @.storeID varchar(10), @.storeName varchar(50), @.ordNo varchar(10)
DECLARE @.MyVariable CURSOR
SET @.MyVariable = CURSOR FOR
SELECT isnull(a.stor_id,''), isnull(a.stor_name,''),isnull(b.ord_num,'') from stores a
right outer join sales b on a.stor_id = b.stor_id
Open @.MyVariable
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
while @.@.fetch_status = 0
begin
print @.storeID+''+@.storeName+' '+ @.ordNo
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
end
close @.MyVariable
deallocate @.MyVariable

|||

Thanks. It appears to be working now. I think I got the LEFT and RIGHT outer join confused. That's a newbie for you.

Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GO
UPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>
|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
----
--
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();

Can't create http endpoint - don't have permission

I am trying to install an application that have following code in SQL Server
installation script
CREATE ENDPOINT RequirementsAuthoring
STATE = STARTED
AS HTTP (
SITE = '*',
PATH = '/sql/RequirementsAuthoring',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP (...
I get following error
The user 'SIATA\Shimon' does not have permission to register endpoint
'RequirementsAuthoring' on the specified URL. Please ensure the URL refers
to a namespace that is reserved for listening by SQL.
I logged as computer admin on Windows XP with IIS installed.
I tried to run
sp_reserve_http_namespace N'http://Siata:80/sql/RequirementsAuthoring'
That runs just fine. But I still get the same error when I run first script.
Any suggestions?
Thanks,
ShimonHello Shimon,
Although you are logged into Windows as an Admin, that doesn't automatically
make you a member of SA role. Can you do this:
use master
go
grant create endpoint to [your-domain\your-account]
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for fast replay.
No, I can't do it I got this
Cannot find the login 'Siata\Shimon', because it does not exist or you do
not have permission.
I don't get this but. When I am using Management Studio I get this ID as
logged in ID.
I am probably the member of BUILDIN/Administrators
Any solution?
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Well I created the log in and run your script. It worked. I still have the
same error with original script.
How can I confirm that the namespace 'is reserved for listening by SQL.'
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Shimon,
I believe HTTPCFG.EXE can do this, but I don't have it locally to work with.
You can download it from the MS website. See the following URL
http://www.microsoft.com/downloads/...&displaylang=en
(watch for wrapping, of course)
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks.
Will Try
Shimon
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b88e8c89f1e021134b0@.news.microsoft.com...
> Hello Shimon,
> I believe HTTPCFG.EXE can do this, but I don't have it locally to work
> with. You can download it from the MS website. See the following URL
> http://www.microsoft.com/downloads/...&displaylang=en
> (watch for wrapping, of course)
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Yes, I checked the namespace is reserved.
But I still keep getting this message
An error occurred while attempting to register the endpoint
'RequirementsAuthoring'. One or more of the ports specified in the CREATE
ENDPOINT statement may be bound to another process. Attempt the statement
again with a different port or use netstat to find the application currently
using the port and resolve the conflict.
I tried to run netstat but can't find anything listening to port 80 or 1433.
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b88e8c89f1e021134b0@.news.microsoft.com...
> Hello Shimon,
> I believe HTTPCFG.EXE can do this, but I don't have it locally to work
> with. You can download it from the MS website. See the following URL
> http://www.microsoft.com/downloads/...&displaylang=en
> (watch for wrapping, of course)
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

Can't create http endpoint - don't have permission

I am trying to install an application that have following code in SQL Server
installation script
CREATE ENDPOINT RequirementsAuthoring
STATE = STARTED
AS HTTP (
SITE = '*',
PATH = '/sql/RequirementsAuthoring',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP (...
I get following error
The user 'SIATA\Shimon' does not have permission to register endpoint
'RequirementsAuthoring' on the specified URL. Please ensure the URL refers
to a namespace that is reserved for listening by SQL.
I logged as computer admin on Windows XP with IIS installed.
I tried to run
sp_reserve_http_namespace N'http://Siata:80/sql/RequirementsAuthoring'
That runs just fine. But I still get the same error when I run first script.
Any suggestions?
Thanks,
ShimonHello Shimon,
Although you are logged into Windows as an Admin, that doesn't automatically
make you a member of SA role. Can you do this:
use master
go
grant create endpoint to [your-domain\your-account]
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for fast replay.
No, I can't do it I got this
Cannot find the login 'Siata\Shimon', because it does not exist or you do
not have permission.
I don't get this but. When I am using Management Studio I get this ID as
logged in ID.
I am probably the member of BUILDIN/Administrators
Any solution?
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Well I created the log in and run your script. It worked. I still have the
same error with original script.
How can I confirm that the namespace 'is reserved for listening by SQL.'
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

Cant create connection to database

I am having trouble creating a connection to an MS SQL 2000 database located on a server at the hosting company I'm using, with code running from my localhost. It works when the code is executed on the server at my hosting company, but not when it is executed locally, on my computer. Also, I can't connect to the database using Enterprise Manager. The error I get is this:

SQL Server does not exist or access denied

It has worked before, so I don't understand why it doesn't anymore. It's not a firewall issue, since I have tried to connect with my firewall disabled as well.

Any tips or ideas?have you tried those obvious things like using ip in place of server name? when this error appears and there is evidence that the server is running and nothinng is misspelled it just means that the network library selected is not the one that can reach the target (that's lame interpretation, i'm sure there'll be someone to take the trouble to translate it into unreadable scientific terms ;))|||Yup, have tried using IP instead. No luck.|||Your problem may be as simple as the SQL Server installation being on a dynamic port or as complex as a problem with the hosting companies firewall. Have you tried connecting the hosting company and asking them for outside connection parameters?

Randy
www.Database-Security.Info|||Yes, I have contacted them. Several times. And the say that they can connect to the database (on port 1433) from an external network, using the exact same username and password I'm using.

Can't connect to SQL server 2005 Express through Visual C# app

I've installed SQL server 2005 Express and set up a database called StockData. I'm running the following code in a visual C# windows app to test the connection:

System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;

DataTable tblSource = instance.GetDataSources();

string strSource = tblSource.Rows[0][0].ToString() + "/" + tblSource.Rows[0][1].ToString();

SqlConnection sqlConn = new SqlConnection();

sqlConn.ConnectionString = "Data Source=(local);Initial Catalog=StockData;Integrated Security=true";

sqlConn.Open();

I have verified that the SQLEXPRESS service is up and running. The strSource variable shows the machine and SQLEXPRESS instance name when I step through, so I know that the application is recognizing the service. But when I get to the "sqlConn.Open()" line, I get the following error message:

Message="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)"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060

Can anyone tell me what I'm doing wrong here? This is driving me nuts. Thanks.

Your connection string is specifying Data Source=(local) which is for a Default Instance of SQL Server, not a named instance, as is the case for SQL Express. You should be specifying Data Source=(local)\SQLEXPRESS.

You're pulling the server information into strSource and you mention that you've confirmed it's correct, so I'm not sure why you're not using strSource to set Data Source. Possibly you mistyped your connection string? I'm guessing you meant:

sqlConn.ConnectionString = "Data Source=" + strSource + ";Initial Catalog=StockData;Integrated Security=True"

Hope this helps.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

Cant connect to SQL Server 2000

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.I don't have a lot of experience with jdbc, but here goes nothing:

1. Should there be a space in between "database=" and "coffeepassion"?
2. Can you make a connection from the web server to the database server using a .udl file (make a file, call it anything.udl, start it and use another driver type to connect)?\
3. Are you sure you have the right password?

One other thought; please consider using a different account to connect to SQL server. Once you've started down the development path using the sa account, it gets very difficult to make the decision to change to using a different account. Using sa leaves you exposed to all sorts of mischief.

Regards,

hmscott

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.|||dear hmscott,

i've remove the space in the 'databasename= coffeepassion'..and get another error:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.

Can''t connect to local Report server, error code....

Anyone know how to get around this?

TITLE: Connect to Server

Cannot connect to OES-XP1007.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3054.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3054.00" target="_blank">Get Online Help</a></li><ul>
<li>Could not find stored procedure 'GetOneConfigurationInfo'.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

OK

Please see this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955241&SiteID=1

Someone else has a similar problem to yours.

Cant connect to db via code, but can from SQL Server.

Having an issue where I can connect to a remote SQL Database server via enterprise manager, however using the exact same db credentials, I can't connect via the .NET SqlConnection object...

Anyone have this error? Did you ever figure it out?

The error I get is: "SQL Server does not exist or access denied."

Again... I'm using the EXACT same user name, password, port number. I've setup an alias in Client Network Utility...

The SQL Server Enterprise is running on the exact same PC as the code I'm trying to execute.

Thanks for any help!Are you using Integrated Security, what does your connection string look like?|||*UGH*...

I hate it when I waste time on such a trivial error. ;)

It was a stupid mistake. In C, C#, C++, you have to escape the escape char. I had forgotten to do that in my con str.