We are moving a web project from one IIS sever to another and have encountered a problem. On the new IIS server when the code hits the IDataReader it is stopping and bringing up an application error. Any idea why, is there configuration or something else that needs to be loaded on the new IIS server???
Thanks for any help....
In my data access layer class I have Populate methods on the bottom.One of the the objects, color, is an ArrayList, how do I write that? private Product PopulateProduct(IDataReader r) { Product product = new Product(); product.BrandId = Convert.ToInt32(r["brandId"]); product.BrandName = Convert.ToString(r["brandName"]); product.Color = whatGoesHere? (r["color"]); return product; } thanks
View 4 Replies View RelatedCode Block
string commandString = "SELECT Id,Name FROM [DatabaseTable];";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(commandString, conn);
conn.Open();
IDataReader rdr = cmd.ExecuteReader();
IList ids = new List();
IList names = new List();
while (rdr.Read())
{
ids.Add((int)rdr[0]);
names.Add((String)rdr[1]);
}
rdr.Close()
}
What i want to know is if there is any better way of obtaining the 'id' and 'name' data
values then just assuming that id is the first returned object and name is the second returned object in each reader record.
ie. Is there any way to retrieve a specific field from the reader in a Dictionary-type manner...?
Code Block
while(rdr.Read())
{
ids.Add( (int)rdr["Id"] );
names.Add( (String)rdr["Name"] );
}
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedHi all,I just asked some people to help me out and phone microsoft with thefollowing information, kindly they refused unless we setup a supportcontract with them first, for pre-sales information. (That really doesnot sound like good business sense to me - anyway here is our problem,if anyone could help thanks)."To tell and ask microsoft:We will be setting up a microsoft sql server 2000 instance running on awindows 2003 server.1) We need to check this can run alongside a microsoft 2003 sql server(either workgroup or standard edition), on the same machine. Are thereany .dll clashes if we do this? If there are can we run SQL Server2000, in a virtual machine running windows 2000 professional. (I have alicenced copy we can use for this).2) If we run one instance of 2000, and one of 2003 of the sql servers,can one use the processor licence model, and one use the CAL licencemodel."Thanks for any help, and any idea why they actually force you to usenews groups for pre-sales information?David
View 4 Replies View RelatedI am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
Hi, I am able to create reports(.rdl) using Faircom c-treeSQL Server ODBC driver which in turn connects to the Faircom SQL Server.
However I'm facing issues while creating the Report model for the report builder. I would like to know if it is possible to create report models with Faircom SQL Server?
Thanks,
Satish
SQL Server Compact Server Agent is not working in IIS6, when i put de link http://myserver/myweb/sqlcesa35.dll?diag i recive de message
Service UnavailableThe problem is in windows server 2003 IIS6
please a need help....
select isnull(comments,'-') as comments from drawing_version where drawing_code='ACC -Arch -IS-2008-54642' the comment value value is empty i want to display as "-"
View 3 Replies View RelatedI have a weird problem. I started my laptop this morning and was unable to start my SQL Server. Every time I try to start it I get the below error:
A connection could not be established to (local)
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registeration properties.
Any ideas?
Thanks
I have had my site running for several months now. My pages retrieve data from the SQL Server on the same machine. Today my users are are getting the message "SQL Server does not exist or access denied"
I have made sure that the credentials are correct. I can use those credentials to create a DSN on the server. So I don't know where to look for the problem. Help please!
I have setup ODBC to connect to a SQL Server 200 database (evaluation version) so I can link tables into an Access database. In the ODBC setup, I selected SQL Server Authentication versus Windows Authentication. However, when I link a table into Access it still uses Windows Authentication first.
I found an article here that discusses the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q279526
It suggested that I run an MDAC update to version 2.6. So I did that and I still have the same problem.
I tried to link a table through some code in Access by changing the connect string to "Trusted_Connection=No" but it still linked up with "Trusted_Connection=Yes".
So how do I use SQL Server Authentication only?
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??
Please bring me some help.
I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were fine but suddenly one day things stopped working when I have tested the connection for link server. It failed and gave me this error. What could be the possible error .
The source it is linked is a System DSN which is linking is a remote server running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it the driver that is the cause of error or what ???
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale categories.". (.Net SqlClient Data Provider)
How can I devise best further to my query's and tables (without my users must stop with these data to consult)?
View 12 Replies View RelatedI created a job using the SQL Server Agent of type Operating System Command (CmdExec). The command was to open a web page that will execute a series of batch programs. I tried running the job I created several times and according to the history of the job, it was successful. However, I checked the supposed changes to some records in the database and no changes were done. I also checked the trace log of the web page but there was no information logged.
I'm convinced that the problem is not on the web page since when I tried manually opening the web page through the browser, the changes were done successfully.
For reference, below is the script of the job:
USE [msdb]
GO
/****** Object: Job [try] Script Date: 05/05/2007 11:06:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/05/2007 11:06:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'try',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=3,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'AZEUSPHazuser',
@notify_email_operator_name=N'AZEUSPHazuser',
@notify_netsend_operator_name=N'AZEUSPHazuser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [try] Script Date: 08/07/2006 11:06:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'try',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'start http://shikra/QSCO/pages/bat/scheduler1.aspx',
@flags=4,
@proxy_name=N'jco2'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Thanks in advance!
Regards.
An Access 97 linked server in SQL Server 2K has recently stoppedworking. In Enterprise Manager, the following error message appearswhen attempting to view the list of tables in the linked server:Error 7399:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].This linked server has not been used recently and in that time Win2KSP4 and SQL Server 2K SP4 have both been applied. We now need to usethis linked server on a regular basis so any help will be muchappreciated.
View 4 Replies View RelatedI have the Visual Studio 2005 Team Suite trial version and the SQL Server 2005 trial (which came with team suite) installed in my machine.
When I installed the SQL Server 2005 I installed it in the Windows Authentication mode. So every time I open SQL Server 2005 it doesnt ask for Username/Password (Its greyed out). The only thing available were:
Server Type: Database Engine
ServerName: ServerSue
Authentication: Windows Authentication.
Here is my Problem:
I created a small application in C#. In the web.config file I created the following:
<appSettings>
<add key="resumecon" value="SERVER=ServerSue;database=Resume;"/>
</appSettings>
Then in the Code behind I created the following string:
string strResCon = ConfigurationManager.AppSettings["resumecon"].ToString();
Then in method (for a Login page) I created the connection string as follows and wrote code to insert some values into the Resume database. I called this method on a button click event.
SqlConnection conLogin = new SqlConnection(strResCon);
When I run the page and when I click the button I get the following error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I think some thing is wrong in the addkey of webconfig? How do I change this?
I wrote some views in sql2000 and all was well. I upgraded to sql 2005 and non of the order by clauses work when I run the views directly. If I open the view up in desing mode and run them, they work.
Any Ideas?
Thanks,
Rick
I'm trying to add records to a table that includes datetime value using INSERT query.
No matter what date value I'm giving the query, the value saved is 1/1/1900 00:00 .
Also, I'm interested in saving only the short date, without time. I thought changing the type from datetime to smalldatetime would do the job, but it didn't help.
Hi,
I was accessing my SQL Server DB through .aspx pages, but somehow suddenly SQL Server stoped allowing .aspx pages to access DB.
Heres the error message.
(Cannot open database requested in login 'lmpdb'. Login fails, Login failed for user 'akr'.)
this user and connection string were perfectly working. I tried recreating the same user and also creating new won, but the same result.
I also tried re-connecting the SQL Server.
Kindly help me to solve this problem.
Regards
We have transfered some jobs to new server using DTS 'export jobs' task, but they are failing when trying to execute DTS packages in them saying 'login failed for user----though everything is correct with the user!
Any help is appreicated..
I was able to take a set of Full backups and script my publication beofre they took the server down. they rebuilt the Server on different hardware with the same IP address and Server name. I then reinstalled my Named Instance and recovered system databases, distribution database and then my user Database with replication. everything seemed fine.
last night, a couple of jobs that use sp_smtp_sendmail failed with this error:
DTSStep_DTSExecuteSQLTask_2, Error = -2147217900 (80040E14) Error string: Cannot load the DLL xpsmtp80.dll, or one of the DLLs i... Process Exit Code 2. The step failed.
I googled it and I found that it may have something to do with DLLs not registered properly. Does anyone know how to fix this?
I'm unable to gain connectivity to two stand- alone PC's having WIN XP PROFESSIONAL, v. 2002, through Microsoft SQL SERVER 2000. :eek:
Both the PC's are stand-alone and have both have WIN XP PROF. I installed Microsoft SQL SERVER 2000 in which only the Client Components were successfully installed.
I have the Administrator Rights on both the PC's.
The Problem on both the PC's that I'm facing is as follows:-
On starting SQL Server Enterprise Manager, the PC was not detected as a Server under the SQL Server Group, and thus I selected the option 'New SQL Server Registration', then I gave my full computer name (as appears in the properties of 'my computer'), selected Windows Authentication and then selected OK to start or connect.
It did not connect and then I got the following error message:
-------------------------------------------
A connection could not be established to 'COMPUTER NAME'.
Reason: SQL Server does not exist or access denied.
ConnectionOpen(Connect())
Please verify SQL Server is running and check your SQL Server registration property (by right click on the COMPUTER NAME_node) and try again.
OK
-------------------------------------------
On starting the SQL Query Analyzer, firstly the message connecting to 'COMPUTER NAME' was seen and then it also gave the following error message:
-------------------------------------------
Unable to connect to server 'COMPUTER NAME':
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory] SQL Server does not exist or access denied.
OK
-------------------------------------------
where the 'COMPUTER NAME' stands for the name of the particular system that can be seen in System Properties-> Computer Name-> Full computer name.
I had installed the MDAC Components from the Windows Update website.
I checked the registration properties and have tried many times but everytime the same error messages were received in both the systems and all the efforts bore no fruits.
Both the systems are:
Intel P4
WINXP PROF. v2002
128MB DDR RAM
40 GB HDD
Please help soon as I am studying SQL Server!
THANKS. ;)
Hi
I have a VB project in VS 2005 Express that uses SQL 2005. I use Click Once to deploy the project. The s/w installs and installs the SQL 2005 prerequisit on XP and Vista machines without problem.
Problem: As soon as the apps tries to use SQL 2005 on a Vista machine it says sqlservr.exe has stopped working and has to close. XP machines work like a dream.
Any ideas please? Any help would be welcomed.
Dim sql As String = "CREATE DATABASE " + dataBaseName + " ON (NAME = " + dataBaseName + ", FILENAME = '" _
+ UKHRdataFolder + "" + dataBaseName + ".mdf')"
Dim myCommand As New SqlCommand
Dim myAdapter As New SqlDataAdapter
Dim myData As New System.Data.DataTable
Dim conn As New SqlConnection
conn.ConnectionString = _
"Data Source=.SQLEXPRESS;AttachDbFilename=;Integrated Security=True;Connect Timeout=30;User Instance=True"
myCommand.Connection = conn
Try
conn.Open()
Try
myCommand.Connection = conn
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
Catch myerror As SqlException
End Try
Catch myerror As SqlException
Finally
If conn.State <> ConnectionState.Closed Then conn.Close()
End Try
conn.Close()
conn.Dispose()
I will try to explain this the best I can and even include the code. I have the following:
DECLARE @x XML
SELECT @x =cast(bulkcolumn as XML)
FROM OPENROWSET(BULK 'C:databasexmldtdyahoostore3.xml', SINGLE_BLOB) AS x
USE yahoostore
-- create a table variable
CREATE TABLE PRODUCTS (ProductID VARCHAR(255), Code varchar(255), ProductDesc VARCHAR(255), Url VARCHAR(255),
Orderable varchar(255), Taxable varchar(255), HTMLPath varchar(255),
Caption varchar(255), Thumb varchar(255), Picture varchar(255),
BasePrice varchar(255), LocalizedBasePrice varchar(255), OriginalPrice varchar(255), LocalizedOriginalPrice varchar(255),
SalePrice varchar(255), LocalizedSalePrice varchar(255),
Availability varchar(255), Weight varchar(255), OptionLists varchar(255))
INSERT INTO Products (ProductID, Code, ProductDesc, Url, Orderable, Taxable, HTMLPath,
Caption, Thumb, Picture, BasePrice, LocalizedBasePrice, OriginalPrice, LocalizedOriginalPrice,
SalePrice, LocalizedSalePrice, Availability, Weight, OptionLists)
SELECT
x.value('@Id[1]','varchar(255)') AS id,
x.value('Code[1]', 'VARCHAR(255)') AS code,
x.value('Description[1]','VARCHAR(255)') as description,
x.value('Url[1]','VARCHAR(255)') as url,
x.value('Orderable[1]', 'VARCHAR(255)') AS orderable,
x.value('Taxable[1]', 'VARCHAR(255)') AS taxable,
x.value('Path[1]', 'VARCHAR(255)') as htmlpath,
x.value('Caption[1]', 'VARCHAR(255)') as caption,
x.value('Thumb[1]', 'VARCHAR(255)') AS thumb,
x.value('Picture[1]', 'VARCHAR(255)') AS picture,
x.value('(Pricing/BasePrice)[1]', 'MONEY') AS baseprice,
x.value('(Pricing/LocalizedBasePrice)[1]', 'MONEY') AS localizedbaseprice,
x.value('(Pricing/OriginalPrice)[1]', 'MONEY') AS originalprice,
x.value('(Pricing/LocalizedOrignalPrice)[1]', 'MONEY') AS localizedoriginalprice,
x.value('(Pricing/SalePrice)[1]', 'MONEY') AS saleprice,
x.value('(Pricing/LocalizedSalePrice)[1]', 'MONEY') AS localizedsaleprice,
x.value('Availability[1]', 'VARCHAR(255)') AS availability,
x.value('Weight[1]', 'VARCHAR(255)') AS weight,
x.value('OptionLists[1]', 'VARCHAR(255)') as optionlist
FROM @x.nodes('/StoreExport/Products/Product') s(x)
--SELECT * FROM Products
This workings fine, but when I add it to the server agent to run nightly it fails.
The only thing I see for the error is:
Message
Executed as user: NT AUTHORITYSYSTEM. INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Can some one explain what happened and how to fix this
Dee
SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')
View 1 Replies View RelatedHi,
I am at a loss here, unless I misunderstand the whole point about server encryption. My 2005 SQL server has a certificate from a trust CA, I have turned on the 'force encryption' flags on the server. My understanding is any client will be "force" to connect with encryption?? I found out that unless I turn on encryption on my clients, the server will allow connections without the requiring encryption. Am I missing something here? Thanks for any help you can provide.
Hi,
We just restored our database from SQL Server 2000 to SQL Server 2005 for me to test things out. Right off the bat I started seeing errors where I could not select data from a table. I would get the "Invalid object" error unless I specified the schema of the object. I found this confusing because I was connected to the server using SQL Server Authentication as the schema_owner ( i.e user name and schema name were identical). I thought this was due to the fact that my DEFAULT_SCHEMA was not set but when I checked, user 'testuser' had schema 'testuser' as default_schema. I also made sure I was connected to the Database using SQL Server Authentication as user 'testuser'.
But the following would fail:
SELECT * FROM Employees
The following would however succeed.
SELECT * FROM TestUSer.Employees
I was really confused and when I read some posts and forums, I decided to check SYSTEM_USER and to my dismay, it returned my Domain login as the value as opposed to "TestUser". I re-connected using my Domain Account and SYSTEM_USER returned the Domaim Login as expected but when I switched to Sql Server Authentication and connected as user "testuser", SYSTEM_USER would still return my Domain Account. I believe this is the source of my problems. Can you tell me what's going on.?
I am a member of the sysadmins group and CURRENT_USER returns DBO. Please help as this is driving me nuts!
Thanks
Hi,
I've been trying to get my merge replication to work with a sql ce 2.0 on
sql server 2005, but it keeps generating shapshot scripts my pocket pc can't
execute.
Example of my table.sch file in my snapshot folder:
drop Table [dbo].[Application]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application](
[AppID] [nvarchar](20) NOT NULL,
[AddOnInfo] [nvarchar](50) NULL,
[MaxClients] [int] NULL,
[AppName] [nvarchar](255) NULL,
[NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory]
DEFAULT (10),
[NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture]
DEFAULT (10),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Applicati__rowgu__4C364F0E] DEFAULT (newid())
)
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
I've selected that I was setting up a merge replication that needs to be
compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains
80RTM...
I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
Even if I make a publication for sql mobile 2005 it generates the same
script.... It's almost as if SQL 2005 is ignoring my settings...
Thanks,
Desperate Natasja
I recently loaded a new machine with my php, database, and MICROSOFT SERVER EXPRESS 2005. I have this same set of programs working on my other computer (both run WINDOWS XP PRO). I can see the database in the MICROSOFT SQL SERVER MANAGEMENT STUDIO EXPRESS. I see the tables and can get the tables while in SQL SERVER EXPRESS MANAGEMENT STUDIO, so I know the table is out there.
SELECT [ID]
,[SubmitterID]
,[DateSubmitted]
,[NumPostDays]
,[NoticeText]
,[ExpirationDate]
,[LastEditDate]
FROM [CabrilloDJD].[dbo].[Notices]
gets me the correct table information
I've looked at the permissions and users I set up to use the database and all seems well (I think I'd get a different error if I put in bad username, password).
What I get when I run my standard program from index.php on the mssql_connect() statement: (works on my other system):
2007-05-03 18:42:04 Ece Error: Level=E_WARNING mssql_connect(): Unable to connect to server: VAIO-MUSICSQLEXPRESS in file D:Deb ....oDJDglobalVariables.php on line 617
I tried:
select * from sysservers
and got 0 1089 VAIO-MUSICSQLEXPRESS SQL Server SQLOLEDB VAIO-MUSICSQLEXPRESS NULL NULL 2007-05-03 10:16:40.810 0 0 NULL NULL 0 0 VAIO-MUSICSQLEXPRESS 1 1 0 0 0 0 1 0 0 0 1 0 NULL 0
I run in loopback mode using localhost. For example,
http://localhost/CabrilloDJDSite/test.php
gets me the correct PHP INFO. I include here the section for MSSQL:
mssql
MSSQL Support
enabled
Active Persistent Links
0
Active Links
0
Library version
7.0
Directive
Local Value
Master Value
mssql.allow_persistent
On
On
mssql.batchsize
0
0
mssql.compatability_mode
Off
Off
mssql.connect_timeout
5
5
mssql.datetimeconvert
On
On
mssql.max_links
Unlimited
Unlimited
mssql.max_persistent
Unlimited
Unlimited
mssql.max_procs
25
25
mssql.min_error_severity
10
10
mssql.min_message_severity
10
10
mssql.secure_connection
Off
Off
mssql.textlimit
Server default
Server default
mssql.textsize
Server default
Server default
mssql.timeout
60
60
This is exactly what comes up on my working system.
I've compared many of the screens on TCP and SQL Server and they are the same except for system names and base disk name. (I use the same directory structure on both, though one has C: drive as the base and one is on D
I've set this up a few times. I'm primarily a php programmer, but I've put together a few systems (built from components) and loaded a lot of software on systems. I've taken the MICROSOFT for administrators course but still have very seldom installed a server. I work on a little home system (2-3 computers) and do not have a static IP. Everything just runs on local host.
Anyway, the localhost and php are both working or I wouldn't be able to run the test.php file which just does phpinfo() if those aren't working.
Any hints greatly appreciated. deb.sites@sbcglobal.net
Hi - I have developed a database using Windows Authentication in Management Studio (CTP) but now need to set up logins for users externally - crucially NOT using windows authentication. I want to resrict these new logins to only run specific stored procs and completely deny all other privileges.
I've created a login 'bc_user' but when I try and connect using management studio (ctp) I get:-
Cannot connect to [server_name]SQLEXPRESS.
Login failed for user 'bc_user'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).
(Connecting in code from VS produces a less specific message).
My user creation T-SQL looks like this:-
USE master
go
CREATE LOGIN bc_user WITH PASSWORD = 'test_pwd', DEFAULT_DATABASE = testdb, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
go
USE testdb
go
CREATE USER bc_user FOR LOGIN bc_user
go CREATE ROLE restricted_users
go
EXEC sp_addrolemember 'restricted_users', 'bc_user'
go
USE testdb
go GRANT EXECUTE ON dbo.example_stored_proc TO restricted_users
DENY ALTER ON dbo.example_stored_proc TO restricted_users
DENY CONTROL ON dbo.example_stored_proc TO restricted_users
DENY TAKE OWNERSHIP ON dbo.example_stored_proc TO restricted_users
DENY VIEW DEFINITION ON dbo.example_stored_proc TO restricted_users
I have searched for this error code and it is documented as a 'bug' in SQL 2000, fixed in SP2. Any ideas what might be causing this or what I have missed? I there a setting in SQL Server (express) that causes this? I have both TCP/IP and Named Pipes connections available and Windows authentication works fine from both VS and Management Studio (CTP).
Thanks
David