No mapping between account name and security ID

Hi,

I created a user account on my active directory service. I then tried
to assign a service located on my SQL server to be executed by this
account. However, when I try to configure my SQL server service, I
get the following error message:

WMI Provider Error
"No mapping between account name and security ID was done"

Do you know what I am doing wrong?

thanksOn Jul 2, 8:57 pm, Zero <talltr...@.yahoo.comwrote:

Quote:

Originally Posted by

Hi,
>
I created a user account on my active directory service. I then tried
to assign a service located on my SQL server to be executed by this
account. However, when I try to configure my SQL server service, I
get the following error message:
>
WMI Provider Error
"No mapping between account name and security ID was done"
>
Do you know what I am doing wrong?
>
thanks

Hi,

Try giving the "WMI control" full permission and also the DCOM
settings as full permission.
then restart the WMI service.

No loading data from ScriptComponent.

Dear all,

I've created a Data Flow scenario as follow:

At first I've got a Flat File Source and then Script Component Task and then OleDb Destination, linked among them by arrows, of course. When I run the SSIS all of them is successfully executed except the last task. Why? I don't know but it isn't awared of nothing.

649 rows are passed to Script Component from the file but they aren't going to my Sql table.

Let me know any advice or thought regarding ths.

Thanks a lot,

We need far far more information than you have provided here.

Is it a synchronous or asynchronous compoennt?|||

Hi Jamie,

Thanks for your quick response.

You'll see the full contains for the .net script

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim valorColumna As String

valorColumna = Row.Column9.Substring(1, 1)

If valorColumna = "N" Then

Me.Output0Buffer.IMPBASE = -1 * CDbl(Row.Column10 / 100)

Else

Me.Output0Buffer.IMPBASE = CDbl(Row.Column10 / 100)

End If

Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)

Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)

Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)

Me.Output0Buffer.CODPROV = CInt(Row.Column7)

Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)

Me.Output0Buffer.NIFPERC = CStr(Row.Column3)

Me.Output0Buffer.NIFREP = CStr(Row.Column4)

Me.Output0Buffer.NOMBRE = CStr(Row.Column6)

Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)

End Sub

I haven't idea if it's asyn or syn

I think that there is some big error very very stupid in all of this.

Thanks again,

Enric

No Excel 2007 Table Support in SSIS?

When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.

In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:

Microsoft Office Access Database Engine

Hresult: 0x80004005

Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."

I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.

Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?

I see that you are using Named Ranges. What if you use the worksheet name?

The Office 12 versions of the Jet Provider and Excel Driver are rewritten, so it's possible that behavior has changed, whether intentionally or unintentionally. Previously the driver displayed both sheets and named ranges as "tables".

-Doug

|||

Apologies for not making myself clearer. I'm referring to the improved table objects in Excel 2007, discussed here:

http://blogs.msdn.com/excel/archive/2005/10/25/484915.aspx

When extracting data from an Excel 2007 source, I'm able to access all of the information on the sheet or from my named ranges without any problem -- both appear as what SSIS called "tables" in the OLE DB Source dialog box. This behavior is the exact same as it was using the JET provider.

My problem is that the Excel 2007 table objects that I've created in various sheets simply aren't accessible -- they don't show up at all. Given their structured, unifiorm nature, one would expect an Excel table to be recognized by the new ACE provider just like, say, a named range.

Why named tables instead of simple named ranges?

-- Tables can grow dynamically as you add rows, and named ranges cannot. My thinking was that if you could import an Excel 2007 table, you wouldn't have to worry if the named range was large enough.

-- Excel 2007 tables can be filtered and sorted, giving the user in the workbook a richer experience.

I think it would be very powerful and useful to access data as it is represented in an Excel 2007 table object through SSIS. I haven't been able to determine if this is even possible, or if I need to take some special steps to make it work.

|||

The driver has no knowledge of any new features in Excel 2007. This is merely the legacy driver, updated to connect successfully to Excel 2007 files, which as you know use a different file format.

-Doug

|||

Thanks for your quick reply -- looks like I'll have to go back to named references.

Also, for anyone wanting to do a similar thing with "Lists" in Excel 2003 (the precursor to Excel 2007) a quick test seems to indicate that the Excel driver (for pre-2007 versions) does not support them, either.

No Email

Hello.
I have created a domain account, granted the account Administrator access on
the report server machine, have changed the two windows services to run as
that account, and have created an Exchange mailbox for the domain account.
I created a simple report that has no data source. I created a shared
calendar that runs every 1 minute. I subscribed to the report on that
schedule, no report.
The report is not being emailed.
Any thoughts?
Thanks in advance,
MikeAlso, SQL server instance is on a different machine.
I don't think that the report is even running. Does reporting services use
the SQL agent?
"mike" <milop@.slomins.com> wrote in message
news:Or03HXtuFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hello.
> I have created a domain account, granted the account Administrator access
> on the report server machine, have changed the two windows services to run
> as that account, and have created an Exchange mailbox for the domain
> account.
> I created a simple report that has no data source. I created a shared
> calendar that runs every 1 minute. I subscribed to the report on that
> schedule, no report.
> The report is not being emailed.
> Any thoughts?
> Thanks in advance,
> Mike
>|||No,Mike it does not.
"mike" wrote:
> Also, SQL server instance is on a different machine.
> I don't think that the report is even running. Does reporting services use
> the SQL agent?
> "mike" <milop@.slomins.com> wrote in message
> news:Or03HXtuFHA.2076@.TK2MSFTNGP14.phx.gbl...
> > Hello.
> >
> > I have created a domain account, granted the account Administrator access
> > on the report server machine, have changed the two windows services to run
> > as that account, and have created an Exchange mailbox for the domain
> > account.
> >
> > I created a simple report that has no data source. I created a shared
> > calendar that runs every 1 minute. I subscribed to the report on that
> > schedule, no report.
> >
> > The report is not being emailed.
> >
> > Any thoughts?
> >
> > Thanks in advance,
> >
> > Mike
> >
>
>|||As Per my knowledge an SQL Agent job is created for each schedule. SQL Server
Agent is very much required to run the Scheduled SRS Reports.
Ashish
"ProJester" wrote:
> No,Mike it does not.
>
> "mike" wrote:
> > Also, SQL server instance is on a different machine.
> >
> > I don't think that the report is even running. Does reporting services use
> > the SQL agent?
> >
> > "mike" <milop@.slomins.com> wrote in message
> > news:Or03HXtuFHA.2076@.TK2MSFTNGP14.phx.gbl...
> > > Hello.
> > >
> > > I have created a domain account, granted the account Administrator access
> > > on the report server machine, have changed the two windows services to run
> > > as that account, and have created an Exchange mailbox for the domain
> > > account.
> > >
> > > I created a simple report that has no data source. I created a shared
> > > calendar that runs every 1 minute. I subscribed to the report on that
> > > schedule, no report.
> > >
> > > The report is not being emailed.
> > >
> > > Any thoughts?
> > >
> > > Thanks in advance,
> > >
> > > Mike
> > >
> >
> >
> >sql

No Data-Driven Subscription button

I have created a report and published it on the reporting server. My connection is working, and I have followed the steps in the BOL tutorial for "Defining a Data-Driven Subscription" i.e. Credentials stored securely on report server, using windows credentials when connecting to the data source. When I click on "Subscriptions", I only see "New Subscriptions". However, when I look at other reports (not even created by me), I see the option to create data driven subscriptions. Can somebody tell me what I need to do to see this?I got it; the report cannot be in the "My Reports" folder.

No data is displayed in a Report on Win98

Hi,

I have created an OCX control in VB6.0 that contains the crystal report viewer.

On development machine (i.e. Win2K), my OCX control shows the crystal report properly. But, when I install my application on the client machine (Win98) no data is displayed in the report.

I have created the report using ADO connection and I am using 'SetDataSource' method of report object to pass the recordset to the report.

I have installed Crystal Report Application on the client machine. Through crystal report application I could see the data in the report.

Can any one help me?

With regards,
PinakinI use CrystalRyport. I want to print records in the same line in succession, how do i do

No data in Data Mining cube.

Hi,

I've created a Cluster data mining model and seems to correctly return data. However I've created a data mining dimension and cube but when I go to query the cube it doesn't return any data if I select any members on the data mining dimension. Any suggestions to where I can look to resolve this?

Thanks

Sanjay

Hi,

I found out how to see the data in the cube. I was using the data mining dimension in relation with the dimension it was based on. So now I can see data for the years that I've trained my model on. Is there anyway to make the cube predict the values for the other years in the cube with data?

Thanks

Sanjay

|||

Are you saying that for example you've created a cluster model on a set of items, and you want to see the cluster values for items not in the set? And you want the slicing behavior to work against those values as well?

If this is the case, unfortunately, that's not the way it works. The cluster dimension is only on the cases for which it was trained. You could apply the clustering to the data outside the cube, write the results to a table and process it as a regular dimension, though.

|||

Hi,

Ideally yes. For example if I generate a cluster model and train it with a couple of years data, potentially I would then like to use the data mining dimension and cube to predict or classify the current years data. I will look to use your suggestion.

Thanks

Sanjay

no data handling

I have created a sales checking report which will only show the items below
the sales target, now I subcribe this report by email daily basis, but even
there are no any data on the report, it will also sumbit a blank report to
me. it is possbile to control no data handling for reporting or subcription.
Thanks for any suggestions.On Jul 7, 5:28 am, "Sampras" <sampras...@.hotmail.com> wrote:
> I have created a sales checking report which will only show the items below
> the sales target, now I subcribe this report by email daily basis, but even
> there are no any data on the report, it will also sumbit a blank report to
> me. it is possbile to control no data handling for reporting or subcription.
> Thanks for any suggestions.
As far as I know, through the Report Manager there is not. In order to
have this kind of flexibility, you would most likely need to create a
custom ASP.NET/Winforms application called via a scheduled task, etc.
Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

No connection could be made because the target machine actively refused it

Hi,

I have successfully created a project (using VS.Net 2003 and SQL Server Analysis Services 2000) which is dynamically retrieving dimensions, levels, and members, and i generating and executing a query as per user needs and requirements. However when I tried to deploy the project on another machine, even though the apps is running fine, and the query getting generated, when its trying to show the data in the report-display page using ADOMD.Net, the following error was shown.

No connection could be made because the target machine actively refused it

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SocketException (0x274d): No connection could be made because the target machine actively refused it]

System.Net.Sockets.Socket.Connect(EndPoint remoteEP) +307

System.Net.Sockets.TcpClient.Connect(IPEndPoint remoteEP) +24

System.Net.Sockets.TcpClient.Connect(IPAddress address, Int32 port) +81

Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo) +208

[AdomdConnectionException: A connection cannot be made. Ensure that the server is running.]

Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo) +384

Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo) +51

Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) +398

Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection+IXmlaClientProviderEx.ConnectXmla() +124

Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) +144

Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() +601

cubicdimension.Reportdisp.Page_Load(Object sender, EventArgs e) in E:\Inetpub\wwwroot\cubicdimension\Reportdisp.aspx.vb:65

System.Web.UI.Control.OnLoad(EventArgs e) +67

System.Web.UI.Control.LoadRecursive() +35

System.Web.UI.Page.ProcessRequestMain() +731

May I mention here, that the computer where I tried deploying, has SQL Server 2000 as well as SQL Server 2005. I tried reinstalling MSXML, but the computer already has a higher version of MSXML.

Any help in this regard would be appreciated.

Best Wishes.

Check Programs/SQL Server 2005/ Configuration tools/SQL Server Surface Area Configuration.

In "Surface Area Configuration for services and connection", you click on the service and remote connections.

See that you have local and remote connections activated.

HTH

Thomas Ivarsson

No connection

Hello,

New development machine with Sql Server Express and Visual Web Developer.

I created a database then tried to connect from a test page on localhost and it worked. Then I tried from the internet domain and could not. So under the

Surface Area Configuration, I change the Remote Connections to Using both TCP/IP and named pipes and restarted the Database Engine. But

I still could not connect. So I restarted the machine. Now I can't view the page on localhost either. I can view a different web page so the Web server is

working.

I get the following as the exception:Error reading the database: Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

My connection string is: "Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=|DataDirectory|Externalresearch.mdf;User Instance=True"

Any suggestions?

Okay I found out why. If I have Web Developer open (with the database connection open) I cannot connect from the browser. But

if I close Web Developer then I can connect from the browser (ie, view my test page).

Is this because it's the same machine that I am both viewing the web page and hosting Sql server (ie, the user can only be used once)?

|||

Hi,

It's a little strange that it says you're using the Network Service to connect to SQL Server.

Are you connecting to the SQL Server through Internet with the windows authentication? If so, that's not supported, since the windows identity cannot be passed through internet.

|||

Kevin,

Everything including the database is on one machine. I'm using Windows authentication. So when I'm in Visual developer and connect

to the database, my web page (again hosted on the same machine) cannot connect to the database. It's a new installation of everything

with defaults setup. How can I work on the database while my web pages can still access the database? I just downloaded Managament

Studio Express, will this do?

Thanks for any help

|||

Hi,

Yes, you can use Management Studio to grant database access permission to the Network Service account.

By default, it does not have permission to the SQL Server. After granting the access, it will be working fine, I think.

sql

No COLLATE definition

Hi,
during the initialization of the subscriber from a mergepublication the
COLLATE definitions were not defined in the created tables of the subscribers.
In the snapshot directory I searched the right schema script and I saw that
the collate definition in the create table command was not defined.
Is this a bug or is it a result of a wrong setting of the publisher or
subscriber ?
How can I solve the problem ?
Best regards
Axel Lanser
On the article properties, snapshot tab there is a checkbox for Collation -
by default it is not taken from the publisher to the subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
my snapshot has more then 200 articles. Is there a way to set the collation
for all articles automaticaly.
Best regards
Axel Lanser
"Paul Ibison" wrote:

> On the article properties, snapshot tab there is a checkbox for Collation -
> by default it is not taken from the publisher to the subscriber.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Nightly Maintenance Backup Plan fails -- no trace of why

We have been running a backup plan (created using the sql 2005 Maintenance wizard) for some time. It failed last night but all databases seemed to be backed up.

I looked at the ERRORLOG in <mssql2005>\mssql.1\log as well as

"Servname DB Backup Plan_20070118184358.txt"

but found no errors. Where else can I look to see what went wrong?

TIA,

barkingdog

You can also check the logs of the Sql Server Agent job corresponding to the maintenance plan.

Thanks,

Kuntal

|||

>>> check the logs of the Sql Server Agent job

Where are these located? (Are you referring to SQLAgent.OUT? It had nothing of interest.)

Barkingdog

|||

In Sql 2005, for every maintenance plan an Sql Agent job is created by the same name. You can view its logs as Sql Server Agent -> Jobs -> select the job & right click to View History.

Hope that helps

Kuntal

|||

What was the error under the scheduled job for this maintenance plan?

Also try to run thsi manually from SSMS to see the execution, also ensure the SQLAgent account has required privileges to run this job. ALso check what changes have been since last time when this job was running succesfsully.

next 30 days or all values

Hi,
I have a dataset and I need to be able to filter that data based on
Completion Date.
I have created a user parameter is SSRS with the following values:
0 = All Dates
1 = Next 30 days
I am trying to filter my dataset but I don't know how to filter this
based on my user parameter.
Help!
JasonI figured it out
if the user selects "All" then Parameter!Completion.Value = 0
if the user selects "Next 30 Days" then Parameter!Completion.Value = 1
so my filter looks like:
=iif (Parameters!Completion.Value=1, date.Today.AddDays(30),
date.MaxValue)

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql

Newly created stored procedure => master db

I have upgraded my SQLExpress to SP1 but since doing this I am not able to
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on the
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created the
new proc and then opened it, but the Connect to Database Engine dialog box is
displayed and I connect to the master db.
"Uri Dimant" wrote:
> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> > create stored procedures under the database that I created. It seems as if
> > any new procs are created in the master database.
> >
> > If I create a new proc, it is not displayed under the stored procedure
> > folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> > Studio Express\Projects along with the other procs that I'd created prior
> > to
> > the upgrade.
> >
> > If I open the newly created proc then I'm asked to login to the SQL
> > Express
> > again (I already have Management Studio Express open and logged in to my
> > own
> > database). Once the proc is open , the combo box "available databases" on
> > the
> > toolbar displayes, 'master'. So it seems I've logged in to the master
> > database where the new proc has been created. But it is not displayed in
> > the
> > Stored Procs of the master either.
> >
> > What has gone wrong? I should be able to create new stored procs in my
> > database and not in the master database.
> > Can someone help - thanks
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>> Lisa
>> What is happenening if you issue?
>> USE mydb
>> GO
>> CREATE PROC mmmm
>> AS
>> .....
>>
>> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
>> message
>> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
>> >to
>> > create stored procedures under the database that I created. It seems as
>> > if
>> > any new procs are created in the master database.
>> >
>> > If I create a new proc, it is not displayed under the stored procedure
>> > folder. Iit is displayed in the folder c:\mydocuments\SQL
>> > ServerManagement
>> > Studio Express\Projects along with the other procs that I'd created
>> > prior
>> > to
>> > the upgrade.
>> >
>> > If I open the newly created proc then I'm asked to login to the SQL
>> > Express
>> > again (I already have Management Studio Express open and logged in to
>> > my
>> > own
>> > database). Once the proc is open , the combo box "available databases"
>> > on
>> > the
>> > toolbar displayes, 'master'. So it seems I've logged in to the master
>> > database where the new proc has been created. But it is not displayed
>> > in
>> > the
>> > Stored Procs of the master either.
>> >
>> > What has gone wrong? I should be able to create new stored procs in my
>> > database and not in the master database.
>> > Can someone help - thanks
>>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend. :(
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there when
I open the proc again.
"Uri Dimant" wrote:
> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> > Thanks for your response. Unfortunately it makes no difference. I created
> > the
> > new proc and then opened it, but the Connect to Database Engine dialog box
> > is
> > displayed and I connect to the master db.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Lisa
> >> What is happenening if you issue?
> >> USE mydb
> >> GO
> >> CREATE PROC mmmm
> >> AS
> >> .....
> >>
> >>
> >> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
> >> message
> >> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
> >> >to
> >> > create stored procedures under the database that I created. It seems as
> >> > if
> >> > any new procs are created in the master database.
> >> >
> >> > If I create a new proc, it is not displayed under the stored procedure
> >> > folder. Iit is displayed in the folder c:\mydocuments\SQL
> >> > ServerManagement
> >> > Studio Express\Projects along with the other procs that I'd created
> >> > prior
> >> > to
> >> > the upgrade.
> >> >
> >> > If I open the newly created proc then I'm asked to login to the SQL
> >> > Express
> >> > again (I already have Management Studio Express open and logged in to
> >> > my
> >> > own
> >> > database). Once the proc is open , the combo box "available databases"
> >> > on
> >> > the
> >> > toolbar displayes, 'master'. So it seems I've logged in to the master
> >> > database where the new proc has been created. But it is not displayed
> >> > in
> >> > the
> >> > Stored Procs of the master either.
> >> >
> >> > What has gone wrong? I should be able to create new stored procs in my
> >> > database and not in the master database.
> >> > Can someone help - thanks
> >>
> >>
> >>
>
>

Newly created stored procedure => master db

I have upgraded my SQLExpress to SP1 but since doing this I am not able to
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on th
e
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created th
e
new proc and then opened it, but the Connect to Database Engine dialog box i
s
displayed and I connect to the master db.
"Uri Dimant" wrote:

> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend.
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there whe
n
I open the proc again.
"Uri Dimant" wrote:

> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
>
>

newid() question

when you use newid() is that a new uniqueidentifier for the database or for the table?

I have a changelog that captures newly created users first and gives them a newid, then i want to approve that change and copy the newid generated for that user into the user table. Will I run into duplicate id's that way?The NEWID() function creates aGUID. This number should be, statistically-speaking, unique.

NEWBY question - filtering dates

Im not sure how to explain this or if this is the correct forum but here it goes.

I have created a report from a view that gives me court hearing dates on a defendent. When I run the report/view I get two different dates for the person such as, 12/14/2006 and 12/15/2006. How can I get it to show only the later date (12/15/2006)? I pull information from a mainframe that almost always gives me 2 different dates, but we only need the most recent date.

Here is my query for the view

SELECT dbo.CASES.CaseID, dbo.CASENUMBERS.CaseNumber AS Arrest_Number, dbo.CASENUMBERTYPES.CaseNumTypeCode AS Arrest_NumType,
CASENUMBERS_1.CaseNumber AS JN_Number, CASENUMBERTYPES_1.CaseNumTypeCode AS JN_NumType, dbo.ROLETYPES.RoleTypeDesc,
dbo.ACTORS.SIDNum, dbo.ACTORLOCATIONS.ActorLocDesc, dbo.CHARGENUMBER.ChargeDescription, dbo.DEFCHARGES.OffenseDate,
dbo.fnNameReverse(dbo.ACTORNAMES.LastName, dbo.ACTORNAMES.FirstName, dbo.ACTORNAMES.MiddleName, dbo.ACTORNAMES.Suffix)
AS NameRev, dbo.EVENTS.ev_StartDate AS PreHearingSetDate, dbo.OFFENSELEVELS.OffenseLevelDesc, dbo.CASES.UnitID
FROM dbo.CASES INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.CASENUMBERS AS CASENUMBERS_1 ON dbo.CASES.CaseID = CASENUMBERS_1.CaseID INNER JOIN
dbo.CASENUMBERTYPES AS CASENUMBERTYPES_1 ON CASENUMBERS_1.CaseNumTypeID = CASENUMBERTYPES_1.CaseNumTypeID INNER JOIN
dbo.CASEPARTIES ON dbo.CASES.CaseID = dbo.CASEPARTIES.CaseID INNER JOIN
dbo.ROLETYPES ON dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID AND
dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID INNER JOIN
dbo.ACTORNAMES ON dbo.CASEPARTIES.ActorNameID = dbo.ACTORNAMES.ActorNameID INNER JOIN
dbo.ACTORS ON dbo.ACTORNAMES.ActorID = dbo.ACTORS.ActorID INNER JOIN
dbo.ACTORLOCATIONS ON dbo.ACTORS.ActorLocationID = dbo.ACTORLOCATIONS.ActorLocationID INNER JOIN
dbo.DEFCHARGESUMMARY ON dbo.CASES.CaseID = dbo.DEFCHARGESUMMARY.CasesCaseID INNER JOIN
dbo.DEFCHARGES ON dbo.DEFCHARGESUMMARY.DefChargeID = dbo.DEFCHARGES.DefChargeID INNER JOIN
dbo.CHARGENUMBER ON dbo.DEFCHARGES.ChargeNumberID = dbo.CHARGENUMBER.ChargeNumberID INNER JOIN
dbo.COURTS ON dbo.CASES.CaseCourtID = dbo.COURTS.CourtID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID INNER JOIN
dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN
dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND
(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND (dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET') AND
(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

In the 'PRE-HEARING SET' table is where I receive the two dates. Is there a way to filter out the first date? I just want to show the most recent date.

Hi,

as you do not SELECT anything from the events table what about:

--dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN

dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID

WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND

(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND

(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

AND EXISTS(SELECT * FROM dbo.EVENTTYPES WHERE EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID AND dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Newboe: table containing XML schemas in database

I just created a schema in the AdventureWorks DB (the place for newbies). Where are these schemas stored and what t-sql statement can list all XML Schemas defined in a database\table\column?

TIA,

Barkingdog

select x.name, t.name, c.name
from sys.column_xml_schema_collection_usages cx,
sys.tables t,
sys.columns c,
sys.xml_schema_collections x
where x.xml_collection_id = cx.xml_collection_id
and t.object_id = cx.object_id
and c.column_id = cx.column_id
and c.object_id = t.object_id

The best way to learn this (catalog views) is thru SQL Server Management Studio -> Object Explorer -> Database -> master -> Views -> System views. Just do select * from every one of them.