MS SQL Server :: Problems Publishing My Personal Website

DTS Package Manually/locally It Runs Fine. But The Scheduled Job Run And Fails
I was facing some issue on MP. Yesterday I changed the SQL services to use the local admin account. That didn't help my MP issue. But it may have created another issue.
When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string.
---------------------------------
Executed as user: IL06EDM00SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed.
--------------------------------------------------------------

Any ideas.

View Replies !

Works Locally But Not On Host

I am very confused. I've finally gotten everything to work the way I want on my pc, but when I ftp to the Internet (URL is hosted on GoDaddy), everything goes haywire. I had to create a new database on the host server, and when I use an aspx login, I get the following message:

The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.

I know what the connection string to the database is, but where do I enter that information? I'm obviously new at asp.net and sql, and I need major help.

Thanks. Linda

View Replies !

Connection Problem, MS Personal Website Starter Kit
I have had a local Website up and running based on the MS Personal Website Starter Kit using Visual Studio 2005 Professional and SQL Server Express.

I am now trying to get the databases to run on SQL Server 2005 Standard Edition but can't get the connection to work.

I have changed the connection string in the Web.Config file from:

connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"

To

connectionString="Data Source=.scastle;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"

But am getting the following error:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

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.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

Line 5: Sub Application_Start(ByVal sender As [Object], ByVal e As EventArgs)
Line 6: AddHandler SiteMap.SiteMapResolve, AddressOf Me.AppendQueryString
Line 7: If (Roles.RoleExists("Administrators") = False) Then
Line 8: Roles.CreateRole("Administrators")
Line 9: End If

I am able to connect OK in both Server Management Studio (Server Name: SCastle, Windons Authentication) and clicking Connect to Database in Server Manager in Visual Studio (Server Name: (local)).

Have I got the connection string wrong or do I need to set up additional permissions in the database?

Thanks for your help.

Stephen

View Replies !

View Works Locally But Not On GoDaddy
I have an application that utilizes aspnet for membership.
The application utilizes the same database (in SQLServer) for storage of other things. I have a view as follows:

USE [DB_Name]
GO
/****** Object: View [dbo].[Authorization] Script Date: 06/29/2007 15:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW Authorization
AS
SELECT dbo.Domain.Domain_ID, dbo.Domain.Domain_Name, dbo.Alias.Alias_Name,
dbo.Alias.Alias_Authority, dbo.Domain.Domain_ProcessType, dbo.Domain.Domain_Status,
dbo.aspnet_Users.UserName AS Owner_Name
FROM dbo.Alias INNER JOIN
dbo.Domain ON dbo.Alias.Alias_Parent_ID = dbo.Domain.Domain_ID INNER JOIN
dbo.aspnet_Users ON dbo.Domain.Domain_Owner_ID = dbo.aspnet_Users.UserId

This works fine locally. However, I am hosting this on GoDaddy and though it allows me to create the table, it seems to have a problem (I'm guessing because it is referencing differing schemas?) and I get an error of: Incorrect syntax near the keyword 'Authorization'.through the SQLServer Web interface whenever I attempt to view the data from this view.
I don't doubt that there is some kind of difference between the manner that SQLServer has set up the schemas and how they are set up on my local version.
Any help would be greatly appreciated. I am at a loss of how to get this info and truly want to utilize the view as it would improve performance over a query - at least I believe so. My thinking is that although there may be a performance drop when the item is being added, I gain on the back-end where I really need the performance over utilizing a query.

Thanks,
Steven Henley

View Replies !

SQL Server Error - Trying To Deploy Personal Website Package
After using FTP to transfer my site to Dotster, I get an error upon running the site. Here are the source error and stack trace respectively:
Line 5: void Application_Start(object sender, EventArgs e) {Line 6: SiteMap.SiteMapResolve += new SiteMapResolveEventHandler(AppendQueryString);Line 7: if (!Roles.RoleExists("Administrators")) Roles.CreateRole("Administrators");Line 8: if (!Roles.RoleExists("Friends")) Roles.CreateRole("Friends");
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
The host provider says I must create the database [on their server] using their on-line utility, Is this true? Or do I need to configure the server to allow remote connections? If the latter is true, how is it accomplished?
Thanks in advance.... any help is appreciated.

View Replies !

Calling DTS Via ASP.NET Works Locally But Not When Published To Intranet
I have a DTS package that I am calling in my code-behind (vb) of one of my asp.net pages on my intranet. The package takes a FoxPro table that is on our network and inserts it into a SQL Server table in one of our databases. The package gets called and executed properly when I am working on it in development on my local workstation, but when I publish it to our intranet, which is in the same domain and behind the same firewall, the package does not execute. No error shows up on the published page, but also nothing gets executed. Has anyone encountered this? Here is my code, which is pretty straightforward:
oPkg = Server.CreateObject("DTS.Package")oPkg.LoadFromSQLServer("[servername]", "sa", "[password]", DTSSQLStgFlag_Default, "", "", "", "Invoice Import")oPkg.Execute()
Any help would be appreciated. Thanks.

View Replies !

Accessing Data Connection Works Locally, But Not When Deployed
Hi all, I hope this is a simple issue that I'm overlooking something or have contradicting permissions set somewhere. I have a fairly basic site with a subfolder containing an aspx page. The user logs in to gain access to the subfolder. That part works fine.On another page(also within a subfolder in the subfolder), they can post form data to a database (SQL Server 2000) which exists on a different server than my web server. This page opens fine, but when submitting the data (only one value to one field for my testing purposes), it bombs and returns the error "Login failed for user"...In my connection string I've specified the username and password which I assigned to the db in Enterprise Manager (not sa, btw). I've also tried letting IIS control the UID/PW, and even specified no UID/PW in either the connection string or the db. Anonymous access enabled in IIS for this virtual directory also returns the error. (?)I should mention that in Visual Studio, the connection string was automatically set to my local development machine's workstation ID. I removed that and have tried replacing it with the server name, blank, etc.(in notepad) - to no avail. dll's are in their proper places, etc. The only thing not working is the database connection.Using: IIS v6, SQL Server 2000, and Visual Studio.NET 2003 (1.1 framework)Anyway - any suggestions? There are so many places to enable/disable permissions, it's getting confusing. I hope it's something I'm overlooking and not a problem with our SQL installation...Thanks!--Donnie

View Replies !

How To Create Multiple Personal Tables On The Fly For Each Registered User Of A Website Using VWD And SQL 2005 Express?

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

View Replies !

Access Denied To ReportServer And Reports, Works Locally But Fails Remotely

I am getting the error:

HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.
Internet Information Services (IIS)

when I attempt to connect to Reports or ReportServer from my desktop, but it works normally when I login with the same userid and run it directly on the server, using IE. It is prompting me for a login 2 or 3 times before failing.

My configuration is:

Report Server system:

Windows Server 2003 R2 SP2 - 32bit

SQL Server 2005 Reporting Services Enterprise Edition
Windows & Web Service run as a domain account
Database connection is using domain account - not in db_owner, but in RSExec roles
Database is on another server, and is Native, not SharePoint integrated
Reports & ReportServer are in a separate application pool
Reporting Services had SP2 installed before it was configured
The Rport Server is a VM

Database server

Windows Server 2003 R2 SP2 - x64
SQL Server 2005 Enterprise Edition 64bit
Version 9.00.3200.00

The Report Manager was not working at all, and I discovered that there was no entry in the .Net Framework version on the APS.Net tab in properties for the Reports virtual directory. I am not seeing any errors or anything unusual in the Event log or in the ReportServer log files.

FrontPage 2002 extensions were installed, and then removed. I noticed that this installed a SharePoint virtual directory, and that disappeared when I removed FrontPage extensions.

The domain group my userid is in is in the local Administrators group on the ReportServer system, and I have added this group as a System Administrator and Content Manager through the report Manager.

I would greatly appreciate any suggestions.

Thanks,
Bill

View Replies !

Sql Job Fails But When Run Outside Works Fine
Hi..

I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error

Error: 2008-03-24 13:52:40.22
   Code: 0xC0202009
   Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR"
   Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.".

I am able to run the package outside the sql job and also connect to the oracle.
I have oracle 9i client installed on the server and sql server is 2005.

Any help would really be appreciated..

View Replies !

Transfer Problems For 6.5 Next Week Works Fine
I have set up transfer of database which takes 6 hrs each saturday
Type--- CMDeXEC

last saturday it tooks just 7 mins and showed this error and did not coplete

2004/0619 4:00:18:42-spid14-Warning OPEN OBJECTS parameter too low

2004/0619 4:00:18:45-spit14-attempt was made to free up descriptors in localdes()

2004/0619 4:00:18:45-spid14-Run sp_configure to increase parameter value

2004/0619 4:00:19:95-spid14-Warning OPEN OBJECTS parameter too low

2004/0619 4:00:19:95-spit14-attempt was made to free up descriptors in localdes()

2004/0619 4:00:19:95-spid14-Run sp_configure to increase parameter value

OriginalTransfered
Data Size 19000(max 28105 mb)18640(max 18640)
Log Size 4000(13105 mb)4640 (max 4640)
Data Physical 18.5 gb18.5 gb
logPhysical 3.90 g b4.88 gb

looks like my data and log size should be increased correct

if yes how to do that?????

if worked this week fine (6 hrs )

View Replies !

32 Bit DTExec Fails While 64 Bit Works Fine On 64 Bit Machine
Hi,
I am executing a SSIS package using dtexec. 64 bit version of dtexec works fine. But when i use 32 bit version of dtexec, it fails. i have local admin rights. Following is error description. Please help.

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:24:30 AM
Error: 2008-03-18 09:24:32.54
   Code: 0xC0202009
   Source: IMALCRM Connection manager "IMAL SRC"
   Description: An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC020801C
   Source: Load Fund Detail V_FUND_DETAIL [16]
   Description: The AcquireConnection method call to the connection manager "IMA
L SRC" failed with error code 0xC0202009.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC0047017
   Source: Load Fund Detail DTS.Pipeline
   Description: component "V_FUND_DETAIL" (16) failed validation and returned er
ror code 0xC020801C.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC004700C
   Source: Load Fund Detail DTS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC0024107
   Source: Load Fund Detail
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:24:30 AM
Finished: 9:24:32 AM
Elapsed: 2.078 seconds

View Replies !

Job Doesn't Work But Package Works Fine
hi,

I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.

Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.

Any ideas?

View Replies !

Remote Connection Tests Fine, But Nothing Works On The Page Itself.
If this post belongs somewhere else I appologize. I have spent several days trying to solve this problem with no luck. My site is online. Hosted at NeikoHosting. I can connect to the database remotely when adding a datacontrol. It tests fine. But when running the page it won't connect. Even if I go in and change the Web.Config connection string to a local Data Source provided to me by Neiko, it still won't work. It just won't connect. Here are the two connection strings in the Web.Config, minus my login info: Only the remote string will pass testing. Neither works on the site. <add name="yourchurchmychurchDBConnectionString" connectionString="Data Source=MSSQL2K-A;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
<add name="yourchurchmychurchDBConnectionString2" connectionString="Data Source=66.103.238.206;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
Here is the stack trace, if that helps.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
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.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.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:

[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.OleDb.OleDbConnection.Open() +37
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.FormView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.FormView.EnsureDataBound() +163
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

So....... HELP!!!!

Thank you!

View Replies !

DTS Job Failing Execution When Scheduled, Works Fine Manually.
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Help!!!

View Replies !

Query Works Fine Outside Union, But Doesn't Work .. .
hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??

select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc

i've included the union query here for completeness of the question

begin
declare @current_date datetime
set @current_date = GETDATE()

select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

end

View Replies !

Using Symmetric Key Problem With Encryption, Decryption Works Fine
Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword variable. But it's not working. It doesn't give me an error but gives me wrong data...

declare @thePassword as varbinary(128)

,@inTargetPassword as varchar(255)

,@pwd3 as varchar(255)

,@theUserId bigint

set @theUserId= 124564

set @inTargetPassword = 'test'

OPEN SYMMETRIC KEY Key1

DECRYPTION BY CERTIFICATE sqlSecurity;

Select @pwd3=EncryptByKey(Key_GUID('Key1')

, @inTargetPassword, 1, HashBytes('SHA1', CONVERT( varbinary, [UserObjectId])))

from table1 where UserObjectId= @theUserId

close symmetric key Key1

View Replies !

Slow Query....drop Index Works Fine!!!!!
We are running MS RS and SQL Server 2000 SP3.

We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.

Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).

If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.

This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....

What is this. ....is it our QUERY, index or huge Transactions or no free space ???

We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?

Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????

Thanks,

View Replies !

Report Works Fine Stand Alone, But Fails When Used As Subreport
I have a report which I have tested and works fine. now I'm trying to use it as a subreport. the "outer" or main report is very simple: it just has a company standard banner and some header/footer information, and then a single subreport. there is no passing of parameters between main report and sub report. the subreport does have its own parameter to govern its dataset, and provides its own default for that.

The error that I'm getting is this:

[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜subreport1€™: An error has occurred during report processing.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. The data extension returned an error during reading the field.

[rsNone] An error has occurred during report processing.

Of course, this doesn't happen when I execute the subreport by itself. What kinds of things should I be looking at to get to the bottom of this. Thanks!

View Replies !

Multivalue Works Fine In The Sproc But Not In Bids Or Reportserver
Hi,

I have a report which has multivalue parameters enabled and If i give NULL it displays everything correctly. But if I give different ClientId it doesnt do it in the report.. But if i run my sproc in VS2005 and in ssms it works the way i want it. this is my sproc

Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]

@StartDate datetime,
@EndDate datetime,
@ClientId nvarchar(max)= NULL
AS
Declare @SQLTEXT nvarchar(max)
if @ClientId is NULL
BEGIN
SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId -- and o.CreatedByUserId = cp.UserId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId --AND cp.ClientId in ('+ convert(Varchar, @ClientId) + ' )
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + convert(Varchar,@ClientId) + ')
AND
o.OrderDate BETWEEN ''' + Convert(varchar, @StartDate) + ''' AND ''' + convert(varchar, @EndDate) + '''
ORDER BY
o.OrderId DESC'
exec(@SQLTEXT)
END
--return (@SQLTEXT)

I have 2 datasets in this report one for the above sproc and other dataset that gives me the clientname and it is as follows

Code Snippet

ALTER Procedure [dbo].[usp_GetClientsAll]

@ClientId nvarchar(max) = NULL

AS

--Declare @ClientId nvarchar(max)

SELECT

NULL ClientId,

'<All Clients >' ClientName

FROM

Client

Union

SELECT

ClientId,

ClientName

FROM

Client

Where

ClientId = @ClientId

OR

(

ClientId = ClientId

OR

@ClientId IS NULL

)

In the first dataset Parameter list i have omitted ClientId but kept it in the report parameter.. So when i give select all it works.. but when i just select particular it gives me the same result as Select all..

any help will be appreciated..
REgards
Karen

View Replies !

Long Running Query In SQL 2005 But Works Fine In SQL 2000
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.

I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.

/* Update WeekEnding Date for current weeks WeeklySales Records */
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null

Weekly sales has approx 100,000,000 rows
Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.

I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.

One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.

Stacy

View Replies !

Stored Procedure Works Fine, But Doesnt Preview? (Beginner)
I modified this stored procedure with the code highlighted, and now it runs forever on reporting services. Can anyone tell me what might be causing this? Here's my code. When i run it in Management studio and the dataset in reporting services, it works fine. But when it comes to previewing it, it runs forever.

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN] Script Date: 06/29/2007 11:34:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN]

(@Region int = Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice' as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime, 'SALESAMP') * -1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL * -1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE Region.Region_Key = @Region

AND [Item_Ledger_Mars_Year] = 2007

AND [Item_Ledger_Amount]= 0

UNION ALL

SELECT

[GL_Entry_Posting_DateTime]

,Budget_Reporting.[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE GL_entry.[Mars_Year] = 2007

END

View Replies !

Works Fine In Designer But When I Load The Report It Doesn't Work
works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????

Item has already been added. Key in dictionary: '9' Key being added: '9'

View Replies !

Permission Issue With Tempdb Works Fine In SQL2000 But Not SQL2005
the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

IF @DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')
EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.

View Replies !

Havin Trouble Inserting Records To A Table.. Update Works Fine
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen

View Replies !

Works Fine Inside BI Dev. Studio, But Fails When Scheduling It In SQL Server 2005.
Hi!

View Replies !

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped.

I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.

There error generated is:

Server: Msg 16957, Level 16, State 4, Line 3

FOR UPDATE cannot be specified on a READ ONLY cursor

Here is the cursor declaration:

declare some_cursor CURSOR

for

select *

from part_view

FOR UPDATE

Any ideas, guys? Thanks in advance for knocking your head against this one.

PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.

View Replies !

Table Visibility Not Functioning Correctly On Server, Works Fine In Visual Studio
I have a report problem. I'm using a parameter to dynamically control visibility for two tables. If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead.

This all works fine in Visual Studio. When I publish it to my report server, the visibility controls no longer function and both tables always display. Any ideas here?

I'm running 2005, SP2 CTP.

View Replies !

DTS Fails At Customer Site With &"Too Many Columns&", Works Locally
I am having the most baffling problem with DTS.... :confused:

I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000

The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.

The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.

If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.

View Replies !

Xp_sendmail: Failed With Mail Error 0x80040111 It Works Fine When You Do A Test From Enterprise Manager

Hello I am receiving the dreaded mail error listed above. I can send out a test E-mail from Enterprise Manager to operators, but I cannot run this Transact query:

EXEC master.dbo.xp_sendmail @RECIPIENTS = araz***@***.com(removed email address),
@SUBJECT = 'test'

I receive:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111

I have stopped/restarted the SQL SERVER AGENT but haven't done much else as I haven't been able to.

Should it work through transact SQL if the test email works from Enterprise Manager?

This is SQL 2000 SP4 running on Win2K in the domain. Thank you.

View Replies !

SQL Login Control Works From Dev Environment But When I Deploy The Website It Errors
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)

-This is the error that is being generated once the site is hosted. If I run my code from my machine and access my remote sql 2005 database it works fine. It is when I try to login from my URL directly that I get the error. Any thoughts?
Thanks.

John

View Replies !

RESTORE FILELIST Is Terminating Abnormally Error When Running A DTS Package In SQL 2005. Works Fine In SQL 2000
Currently I receive the following error when executing script within a DTS package in SQL 2005 (it seems to be working in SQL 2000):

Processed 27008 pages for database 'Marketing', file 'Marketing_Data' on file 5.

Processed 1 pages for database 'Marketing', file 'Marketing_Log' on file 5.

BACKUP DATABASE successfully processed 27009 pages in 15.043 seconds (14.708 MB/sec).

(5 row(s) affected)

Msg 213, Level 16, State 7, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

The code I am using is:

-- the original database (use 'SET @DB = NULL' to disable backup)

DECLARE @DB varchar(200)

SET @DB = 'Marketing'

-- the backup filename

DECLARE @BackupFile varchar(2000)

SET @BackupFile = 'C:SQL2005 dbsMarketing.dat'

-- the new database name

DECLARE @TestDB2 varchar(200)

SET @TestDB2 = datename(month, dateadd(month, -1, getdate())) + convert(varchar(20), year(getdate())) + 'Inst1'

-- the new database files without .mdf/.ldf

DECLARE @RestoreFile varchar(2000)

SET @RestoreFile = 'C:SQL2005 dbs' + @TestDB2

DECLARE @RestoreLog varchar (2000)

SET @RestoreLog = 'C:SQL2005 dbs' + @TestDB2

-- ****************************************************************

-- no change below this line

-- ****************************************************************

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)

SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)

SET @LogFile = @RestoreLog + '.ldf'

IF @DB IS NOT NULL

BEGIN

SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')

EXEC (@query)

END

-- RESTORE FILELISTONLY FROM DISK = 'C: empackup.dat'

-- RESTORE HEADERONLY FROM DISK = 'C: empackup.dat'

-- RESTORE LABELONLY FROM DISK = 'C: empackup.dat'

-- RESTORE VERIFYONLY FROM DISK = 'C: empackup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB2)

BEGIN

SET @query = 'DROP DATABASE ' + @TestDB2

EXEC (@query)

END

RESTORE HEADERONLY FROM DISK = @BackupFile

DECLARE @File int

SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)

DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp

(

LogicalName varchar(500),

PhysicalName varchar(500),

type varchar(10),

FilegroupName varchar(200),

size int,

maxsize bigint

)

INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'

SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data

PRINT @Log

TRUNCATE TABLE #restoretemp

DROP TABLE #restoretemp

IF @File > 0

BEGIN

SET @query = 'RESTORE DATABASE ' + @TestDB2 + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +

' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +

QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)

EXEC (@query)

END

View Replies !

OR Works Fine, When I Add AND Things Go Wrong (was &"Help With Simple Query&")
Hi I really need some help I have been banging my head against a brick wall for the last day or so...

I need some assistance with a query. Maybe what I am trying to do is not possible but I am sure it is.

I have a ASP page with a SQL 2005 Express backend. I would like to query a table based on a dropdownlist and checkboxes on my form.

The perameter in the drop down list is a service so I am querying records based on a service. The checkboxes are locations such as north,east, south and west. So I am building OR statements with these. For example I am looking for plumbers in the north and east. The OR part works fine. Its when I add the service into the query things go wrong.

Here is my tables

Company
---------

PK CompanyID
ComapnyName

Service
--------

PK ServiceID
FK CompanyID
ServiceName

Region
--------

PK RegionID
FK CompanyID
North
East
South
West

The below works fine when querying companies in different locations:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.North, TblRegion.East, TblRegion.South, TblRegion.West
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID
WHERE (TblRegion.Scotland = @Pram1) OR
(TblRegion.Wales = @Pram2) OR
(TblRegion.NorthEast = @Pram3) OR
(TblRegion.NorthWest = @Pram4)

Now the problem is when I add in the service table. Sometimes I get all records returned. So even if I query a plumber in the south and east I get records for companies that dont even have a particular service in the perameter.

I can provide more info if need - I am guessing my design is all wrong. The way I see this happening from a GUI is a dropdown being the service WHERE perameter and the truefalse BIT tick boxes being the OR perameters - any help would be great - many thanks in advance.

View Replies !

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.

We use rectangles to force page breaks to page 2 and to page 3.

When running the report on the report server, it shows and prints fine.

When running the report from the QA website internally, it shows and prints just fine.

When running the report from the production website from a machine internally, it shows and prints just fine.

When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.

I need help in determining where I even begin with trouble shooting this!

View Replies !

MS Access Back-end Works Fine, But SQL Server Back End NOT Working For Access Project
Hi

I have 2 forms, each associated with its own table - they are linked together only by the fact that they share a common field, CustomerID....

I create a form (for table 1, called Customers) that brings up another form (for table 2, called Projects) based on the CustomerId that is selected in the first form from the Customers table - with a SQL back end it brings up ALL records in the second form (for the second table) with any CustomerIDs that exist in both tables - I set the link criteria [forms]![Customers]![CustomerID] and with an MS access back-end it works fine, but with a SQL back end it still returns ALL records with any ID that matches....not just the one I selected in the Customers form..... why??

thanks for reading this and for your help...

View Replies !

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP
I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!

PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View Replies !

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running

telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password

See below:

Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.

The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:

TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT

Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

I would expect this as the DNS has not been advised to encrypt the conection.

This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.

This is on a XP machine trying to connect to the remote webhosting company via the internet.

I can ping the server

I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled

I do not have any aliases set up

No I do not force encryption

I wonder if you have any further suggestions to this problem?

View Replies !

Can't Connect, Even Locally
So, I was trying to run the aspnet_regsql file and it wouldn't connect to the database, looked around and it said to open up remote connections. So I followed instructions to do so, the first step being enabling TCP/IP in the SQLEXPRESS Protocols.

After I did this, I tried to restart and it wouldn't restart. And now I can't connect at all, even locally and can not change the tcp/ip to disabled to even see if that's what caused it.

What should I do?

View Replies !

How Can I Create A Db To Run Locally On My Pc
How can i create a new db to run locally on my pc using sql server.
I need also to install a copy of this new db in another pc. Do i need the sql server installed in the other machine also? I am thinking to create an application in vb to play with the db. Every time i start the sql server i see the window telling to connect do database engine, may be i am wrong but with this window i can create just a db in the server? i couldn't create a db locally yet, anyone can help me?

View Replies !

Cannot Connect Locally
Hello Forum,
after installing 2005, if trying to connect to local server it is giving me the following error:

cannot connect to ols00038

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

View Replies !

Cant Create Db Locally
Im running sql 2005 developer edition on my xp pro workstation. For some reason I can no longer create a database. I get an error such as the following:
"CREATE DATABASE permission denied in database 'master' (Microsoft sql server error: 262)"

ideas ?

View Replies !

How Can I Create A New Db To Run Locally On My Pc Using Sql Server.
How can i create a new db to run locally on my pc using sql server.I
need also to install a copy of this new db in another pc. Do i need the
sql server installed in the other machine also? I am thinking to create
an application in vb to play with the db. Every time i start the sql
server i see the window telling to connect do database engine, may be i
am wrong but with this window i can create just a db in the server? i
couldn't create a db locally yet, anyone can help me?

View Replies !

Using MSDE Locally On A Webserver
Currently, we have our SQL database behind our firewall.
Our App is required to log each page hit to a DB table with some additional data that we would not get from the regular IIS logfiles. So every page hit means a trip through the firewall...

Since this type of activity is limited purely to DB inserts, i'm curious what the thoughts are on using a copy of MSDE on the webserver itself to store this data.

What is the lesser of 2 evils

-- Take a trip through the firewall for each page to log the hits in a secure SQL DB

-- Log the Hit in a less secure / limited version of a SQL DB

View Replies !

Export Db Tables For Use Locally On Another Pc
Hi,I've used SQL Enterprise Manager to Export my selected db's locally.My main question is how do I move the export to another pc - whichfiles need to be moved, etc?ThanksLouis

View Replies !

Opening My Client's DB Locally
My client has given me a DB file (.MDF) and I need to open it in order to export it to the remote DB.
In Enterprise Manager I go to 'New Database' - create a new DB and click on the 'Data' tab to browse to my clients file. Enterprise Manager recognises the file and everything seems hunky dory. but the resulting DB seems to have no tables.
The DB file is 1600K and has some stored procedures but if I try to export the tables there is nothing. His ASP files refer to tables in the SQL as you would expect.
Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!

View Replies !

EM Won't Connect Locally On SQL2k Box
Here is a strange one. Have a sql2k box that I upgraded from 6.5 back. Conversion went fine, everything was ok with the sql install. Created and scheduled maintenance jobs while logged on to the box. A few days after the conversion, Enterprise Manager cannot register the server(error message is 'Data source name not found and connection libraries do not exist'). This inablilty to register the server only occurs when working on the server itself. You can register the server while connecting from another box with EM.

So, do some checking and find that the box itself does not recognize SQLSRV32.dll (etc) as even existing on the box BUT IT DOES EXIST in the normal places, it was a working install just a few days prior. As far as the box is concerned, the network libraries do not exist. Therefore, cannot register the server locally, cannot create a Data Source using the SQLSRV32 driver, cannot start the Sql Server Agent(so no scheduled back ups, etc).

Went so far as to re install the connectivity and client tools, which did not fix the issue.

Does anyone have any ideas?

View Replies !

Locally Connecting To An SQL File
I am trying to loaclly connect to an sql file, i have installed SQl server management studio express, and have downloaded the file, so that i can access it locally, i have trawled about the whole of this site, but can't seem to find anything that helps.
The problem i have is i have a couple of guys telling me that i need to download the backup of teh database, which is in a .BAK format, then rename to .sql, and the open using sql 2005. However when i do this sql 2005 opens, but the a copy of notepad open infront of this, and the .sql file opens up in that. I am a total noob when it come to SQL however, with the help of some online tutorials have managed to teach myself SQL code, and a bit of basic PHP, but nowhere does it go into any great detail about the software surrounding SQl, or accessing the database, all the information i could find is on the code.

Any help would be greatly appreciated as my employers are getting a little bit anxious about all of this now, i know that once these creases are ironed out then i don't have a problem, but getting to the point that i am competent at is the trouble.

View Replies !

What To Install First To Run My Sql Server Locally
Hi i am a total newbe at asp
My goal is to be able to make survey on web pages related to my website and store info on my sql server 2005

I want to use my sql server 2005 as a database, and from what i understand so far i will be using visual web developer to prepare the commands on my webpage to get info colected from the survey sheet to the sql server, am i corect?.
What i need to know is where can i find the tutorial on what to do first.
I would like to install my sql on my computer as a local server first, so what do i have to install in order to do this.
From the main menu i see that i can install sql server "services" or sql server "network" or "native client". Wich one do i install first to be local??
Thanks
Yves

View Replies !

SSIS Runs Locally, But Not On Server - Help!
My team is using SqlServer 2005 SSIS to develop package for Consolidated Views. This works fine on my machine in Visual Studio when they run it as a script from command prompt from their desktop. However, when they move this package to ETL box, the package runs fine Visual Studio environment but fails when run from the script with the errors:

Started: 12:57:45 PM
Error: 2006-08-04 12:57:45.87
   Code: 0xC0011007
   Source: {519D338B-8874-40A8-A385-0D53E868A57C}

Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error

--
Error: 2006-08-04 12:57:45.87
   Code: 0xC0011002
   Source: {519D338B-8874-40A8-A385-0D53E868A57C}

Description: Failed to open package file "C:SM_BI_EXTRACTS_SHAREDSharedInt
egrated ViewsIntegrated ViewsCV_Product.dtsx" due to error 0x800C0006 "The system cannot locate the object specified.". This happens when loading a package a nd the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error

--
Could not load package "C:SM_BI_EXTRACTS_SHAREDSharedIntegrated ViewsIntegrated ViewsCV_Product.dtsx" because of error 0xC0011002.

Description: Failed to open package file "C:SM_BI_EXTRACTS_SHAREDSharedIntegr
ated ViewsIntegrated ViewsCV_Product.dtsx" due to error 0x800C0006 "The system  cannot locate the object specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be  the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Source: {519D338B-8874-40A8-A385-0D53E868A57C}
Started: 12:57:45 PM
Finished: 12:57:45 PM
Elapsed: 0.016 seconds

C:SM_BI_EXTRACTS_SHAREDShared>rem "C:Program FilesMicrosoft SQL Server90DT
SBinndtexec" /f "C:SM_BI_EXTRACTS_SHAREDSharedSSISIntegratedViewsIntegrat
edViewsCV_Sales_Rep.dtsx"

C:SM_BI_EXTRACTS_SHAREDShared>rem "C:Program FilesMicrosoft SQL Server90DT
SBinndtexec" /f "C:SM_BI_EXTRACTS_SHAREDSharedSSISIntegratedViewsIntegrat
edViewsCV_Sales.dtsx"

Can someone help us understand the problem? We are new to SSIS. We also want to know exactly what needs to be on the Server that will be running our SSIS ETL programs (that is, what components of SSIS -- SQL Server 2K5 is already loaded).

We are using SQL 2005 on Dell Servers running Win 2003, with the latest service packs. Microsoft SQL Server Integration Services Designer Version 9.00.2047.00

View Replies !

Cannot Connect Locally To SQL Server Express
Hi,

I'm trying to connect to a SQL Server 2005 express on my local Windows
XP Professional machine. I've searched the the MS fora and Google
but all the answers I find relate to trying to connect to a remote DB,
not local.

I run the aspnet_regsql.exe program in the interactive wizard mode, and
in the Select the Server and Database step when I click on the Database
drop-down list an error box pops up:

Connection failed

Failed to query a list of database names from the SQL server.

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)

Exactly the same error message appears when I:

* Change the 'Server' name from my computer's name to localhost, 127.0.0.1, or SQLEXPRESS. in the aspnet_regsql wizard.

* Stop my firewall (ZoneAlarm, native Windows firewall is disabled, and I switched off my cable modem at the time).

* Use the SQL Server surface area utility to enable remote connections
with both named pipes and TCP/IP), and also to enable CLR integration
in the Features section.

* Stop the SQL Server service. I did this to see if a different
error occurred, but it's as if aspnet_regsql.exe can't even see the SQL
Server.

* Have an additional instance of SQL Server running under my own username (see below).

I have an ASP.NET project (created by someone else on another computer) which attempts
to open a SQL Server database. It runs fine until the call to
System.Data.SqlClient.SqlConnection::Open() which raises an exception
with exactly the same error string as above (including what's in
parentheses). The connection string is "Data
Source=localhost;Initial Catalog=blah;Integrated Security=SSPI;" where
blah is specific to the company I'm doing this for.

I've created a test ASP.NET project to which I added a SQL
Database. My only option was to create a .mdf file in the
APP_DATA folder. The connection string is "Data
Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated
Security=True;User Instance=True" and it works fine and displays the
data I've put into the database, but in the Task Manager another
instance of sqlservr.exe appears (running under my username; the
existing sqlservr.exe runs under the NETWORK SERVICE username).
Should this happen??

If I stop the SQL Server service (or both of them), an error message in my web browser
appears (no exception is raised because there's no code in the project,
just a data-bound control). The error is the same as above,
except the parenthetical part is "(provider: SQL Network Interfaces,
error: 26 - Error Locating Server/Instance Specified)".

Edit: oh yeah, I've also tried to set up an ODBC data source connected
to SQL Server throught the Control Panel/Admin, but the ODBC applet
can't seem to see SQL Server either. I only get three options for
data sources: dBASE files (.dbf), Excel files (.xls), and MS Access
Database (.mdb). If I click on 'Add' and select "SQL Server" and
go on from there, it comes back and says the server doesn't exist or
refuses access. If I stop the service, the ODBC applet says the
sql server wasn't found.

Can anyone help please?

I'm stumped..

View Replies !

Problems Publishing My Personal Website - Works Fine Locally!