Creating an ADSI Linked Server

I am having quite a bit of difficulty setting up a linked server with ADSI.
I have used the code list in the various MSDN and BOL articles
(sp_addlinkedserver...) but any queries return the following error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
I know I am missing some key piece in my linked server set up here. Any
information would be appreciated.
Chris Whinihan
chrisw@.hma.regence.comHi Chris
There are quite a few posts for this error message!
http://tinyurl.com/b9fcf
Have specified created a mapping for the logins using sp_addlinkedsrvlogin?
John
"Chris Whinihan" wrote:

> I am having quite a bit of difficulty setting up a linked server with ADSI
.
> I have used the code list in the various MSDN and BOL articles
> (sp_addlinkedserver...) but any queries return the following error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> I know I am missing some key piece in my linked server set up here. Any
> information would be appreciated.
> Chris Whinihan
> chrisw@.hma.regence.com

Creating an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

EndWith

Catch ex As Exception

MsgBox(ex.ToString)

EndTry

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating a windows Authentication DSN

Can this code be changed to use the windows Authentication instead of the
SQL Server login?
http://www.mvps.org/access/tables/tbl0014.htm
You need to add the keyword/value of Trusted_Connection=Yes
so you could try modifying the SQLConfigDataSource code by
including:
"Trusted_Connection=Yes" & Chr(0)
You can find the ODBC API reference for SQLConfigDataSource
at:
http://msdn.microsoft.com/library/de...dbc_c_99yd.asp
-Sue
On Mon, 23 Jan 2006 11:24:02 -0000, "Chris" <a@.b.com> wrote:

>Can this code be changed to use the windows Authentication instead of the
>SQL Server login?
>http://www.mvps.org/access/tables/tbl0014.htm
>

Creating a windows Authentication DSN

Can this code be changed to use the windows Authentication instead of the
SQL Server login?
http://www.mvps.org/access/tables/tbl0014.htmYou need to add the keyword/value of Trusted_Connection=Yes
so you could try modifying the SQLConfigDataSource code by
including:
"Trusted_Connection=Yes" & Chr(0)
You can find the ODBC API reference for SQLConfigDataSource
at:
_99yd.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_99yd.asp
-Sue
On Mon, 23 Jan 2006 11:24:02 -0000, "Chris" <a@.b.com> wrote:

>Can this code be changed to use the windows Authentication instead of the
>SQL Server login?
>http://www.mvps.org/access/tables/tbl0014.htm
>

Creating a View with detailed informations

Hello,
I have a code for creating view in T-SQL. I want to ask you guys, i want to make this result set should grouped by DepoAdi column and StokKodu (this is an alias sure you can get it from code). Did i make it on group by line? My second problem is i want to add 2 columns to this query. This 2 column will calculate some values with SUM function and - operator. At CRM.Depolar.DepoBilgileri table i have a column named Miktar (this one stores int type datas) and i have a column named islemturu(this one stores 1 or 0). I want to calculate Miktar values which rows has islemturu column 0 and subtract them from which rows has islemturu column 1 value and this computing action must be based on Grouped columns.

DepoAdi | StokKodu | Miktar | IslemTuru
ABS SK101 5 0
ABS SK101 3 1
ABS SK102 4 0
ABS SK102 3 1

This is the table and i'm imaging view what i want now...

DepoAdi | StokKodu | Miktar
ABS SK101 2
ABS SK102 1

How can i add this resultset to my code..

Thanks for reading... Waiting your answers.. Happy coding...

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT DepoBilgileri

FROM [CRM.Depolar]

WHERE Id IN (SELECT Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT ObjeGrubu

FROM [CRM.Objeler]

WHERE Id IN (SELECT Id

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE Id IN (SELECT KartBilgileri

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

Select DepoAdi

, StokKodu

, (Giren - Cikan) As Miktar

From

(

Select Depo.DepoAdi

, Depo.StokKodu

, Sum(Depo.Miktar) As Giren

, 0 As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 0

Group By Depo.DepoAdi, Depo.StokKodu

Union All

Select Depo.DepoAdi

, Depo.StokKodu

, 0 As Giren

, Sum(Depo.Miktar) As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 1

Group By Depo.DepoAdi, Depo.StokKodu

) As Core

|||

Thanks for your reply.. I solved problem with making some changes in my code.

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Miktar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Miktar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Miktar,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Tutar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Tutar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Tutar

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT [CRM.Depolar].DepoBilgileri

FROM [CRM.Depolar]

WHERE [CRM.Depolar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT [CRM.Objeler].ObjeGrubu

FROM [CRM.Objeler]

WHERE [CRM.Objeler].Id IN (SELECT [CRM.StokKartlar].Id

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT [CRM.StokKartlar.KartBilgileri].StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE [CRM.StokKartlar.KartBilgileri].Id IN (SELECT [CRM.StokKartlar].KartBilgileri

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartlar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

Creating a VIEW

I'm trying to create a view in EM.
SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
ORDER BY dbo.wf_styles.code
The error message I get was "Incorrect syntax near '100'". I didn't put "TOP
100 PERCENT" there (not sure what it does...) but EM puts it back after I
delete it. How can I get this to work?
Thanks!!
But theWill,
The ORDER BY clause cannot be used in a view definition without the TOP
clause listed. Do you really want to include the ORDER BY in the view
definition? Could cause unexpected results when querying the view and using
a different ORDER BY clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||Will,
Also move
AND dbo.wf_bom.raw_type = 'F'
to the WHERE clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||(a) write your VIEW in Query Analyzer, not Enterprise Mangler
(b) update your database to not be in 6.5 compatibility mode, where TOP
wasn't supported.
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
No - this is the unpreserved table in an outer join. Doing this changes the
semantics of the query.|||I need that to be part of the JOIN condition because it's a LEFT JOIN.
"Jerry Spivey" wrote:

> Will,
> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok, I took out the ORDER BY, now I'm getting:
Error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_styles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_bom'.
Query:
SELECT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
Thanks.
"Jerry Spivey" wrote:

> Will,
> The ORDER BY clause cannot be used in a view definition without the TOP
> clause listed. Do you really want to include the ORDER BY in the view
> definition? Could cause unexpected results when querying the view and usi
ng
> a different ORDER BY clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok...that I've never seen before. Would you mind elaborating on that for my
understanding?
Thanks Scott.
Jerry
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> No - this is the unpreserved table in an outer join. Doing this changes
> the semantics of the query.
>|||wf_styles and wf_bom has a 1-to-many relationship. One line in wf_styles and
many wf_bom lines for each item (bom = Bill of Materials, what each item
contains). I only want to join where the wf_bom.raw_type = 'F'. If it's not
F, return NULL. If you put it in the WHERE condition, the results returned
would only be items that have F, which is not really what I wanted.
Basically, I want a list of all the styles in my table. If I have the amount
of fabric yards that style uses, display that too. That's why I need the LEF
T
JOIN. If you were to put that clause in the WHERE, it would be "Display all
styles in the table and the fabric yards it uses". But that won't work for m
e
since I have some styles where I don't have the fabric yards. Does that
explain it?
"Jerry Spivey" wrote:

> Ok...that I've never seen before. Would you mind elaborating on that for
my
> understanding?
> Thanks Scott.
> Jerry
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
>
>

Creating a User in Sql....

By Default the sql have the user Sa...
I want to create a user with permissons to create a
database...
How can i do it?
If you have some code sample i would apreciate a lot!
Thanks in advance> By Default the sql have the user Sa...

quote:

---

> I want to create a user with permissons to create a
> database...
> How can i do it?
> If you have some code sample i would apreciate a lot!

---

You should check and learn about the security as soon as possible. Using sa
is a very bad practice. Start with "Managing Security" module in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm). You will find out that you need to
create a login (use Windows logins, if it is possible) and put the login in
the dbcreator fixed server role.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks
Try:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
ThanksTry:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
ThanksTry:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
--
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven

creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

Creating a table from .NET

I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @.tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.

Here is my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@.tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END

What went wrong?

Thank you.

DECLARE @.SQL VARCHAR(500)

SET @.SQL = 'CREATE TABLE ' + @.TableName + ' (bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)'

EXEC @.Sql

-- It should be noted that you SHOULD SANITIZE your parameter and make sure that a user does not put anything non-alphanumeric and _ because a malicious user could potentially execute an sql injection if you did not.

If you search for sp_execsql I believe you will find tons of postings.

|||

To be honest, I wouldn't expect the SProc you've listed to work the way you've described in SQL Server either. What you're looking for is dynamic SQL, which has been discussed a number of times in the past weeks in this forum. I suggest you read the information at http://www.sommarskog.se/dynamic_sql.html before you proceed to ensure that you understand the security implications of using SPs with dynamic SQL before implement it.

If the possibility of SQL Injection is not an issue for you, or you've figure out how to mediate it, that same document also has some recomendations on possible implementations.

Mike

|||Thank you both, marcD and Mike.|||Hi,

you should better use the SMO classes which expose an interface (.NET API) for a developer to manage SQL Server objects. You don′t need to care about syntax or semantics, as SMO is object oriented and can be easily used within C# and Visual Studio (with Intellisense) to produce a SQL-injectionfree code (if used the right way :-) ) The API is the successor of the DMO classes, formerly used in SQL Server 2000 and below.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

creating a table dynamically in SQL Server Express with asp.net and vb

Hi,

Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?

Thanks Matt

sure you can. Create a command object, sent the text as

CREATE

TABLE [dbo].[userdata](

[userID] [int]

IDENTITY(1,1)NOTNULL,

[varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[name] [varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL

)

ON [PRIMARY]

execute the command you will get your table in the database,

Hope this help.

|||

Thank You. That works great. It is exactly what I was looking for.

Matt

creating a subscription via an application

Hello,

I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.

John

There are SOAP APIs that lets you create subscriptions programmatically. Is that what you are looking for? See the following link for details.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

Thanks,

Sharmila

|||This was it. Thank you!!

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>