I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???
would it be a problem with the sdf file? help please!!!
select distinct
a.Patients,
b.Patients,
a.pct,
b.pct
from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null
(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Hi all,
I have a table with 2 columns (simplified for this question):
Date and Action
Date is normal datetime, Action is varchar and can be either Sent or Received. The data can look like this:
1. '2008-04-02 15:09:09.847', Sent
2. '2008-04-02 15:09:10.125', Sent
3. '2008-04-02 15:09:11.125', Received
4. '2008-04-02 15:09:12.459', Received
5. '2008-04-02 15:09:15.459', Received
6. '2008-04-02 15:09:24.121', Sent
7. '2008-04-02 15:09:28.127', Received
I want to find a pair of rows Sent-Received with the Max Date difference, where Received follows immediately after Sent.
It means in our example, valid are only lines
2. and 3. or
6. and 7.
In this example, rows 6. and 7. have bigger difference of Dates, so the result of the query should be
6. '2008-04-02 15:09:24.121', Sent, 7. '2008-04-02 15:09:28.127', Received
It is probably easy, I just cannot work it out. Suprisingly finding MIN was quite easy. Thanx for any tips!
Regards,
alvar
i am new at T-SQL and am trying to delete entries in a column that are not integers. i have the following code. why does it not work.
T-SQL
select invoicenumber from [control register] where convert(int, invoicenumber) like '%'
if @@error > 0
begin
delete from [control register] where invoicenumber = invoicenumber
end
go
gives me the error in Query analyser:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '10-3-05' to a column of data type int.
@@error is supposed to pick up the error. why is it not.
please could you reply urgently at chris@captivedesign.co.za
thanks in advance
Chris Morton
can someone tell me why this sql statement doesnt work?
SQL = "SELECT (Count(department_id) as 'totals' FROM nonconformance WHERE department_id = '7'),(Count(department_id) as 'totals2' FROM nonconformance WHERE department_id = '1') FROM nonconformance"
How do I fix it?
Thanks
Can someone tell me why this thing in the end return Null?
declare @uz_id uniqueidentifier
set @uz_id=null
set @uz_id=
case @Uz_ID
when null then '00000000-0000-0000-0000-000000000000'
else @Uz_ID
end
select @uz_id
...it should return '00000000-0000-0000-0000-000000000000' in my opintion
P.S. When I say return i mean the value that is shown after 'select @uz_id' is executed:)
This is an insert statement i'm trying to run. I want it to only insert this recird if the corresponding EMPLOY_REF exists in the EMPLOYEE table. Heres my statement:
INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE)
VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000')
WHERE '8971' IN (SELECT EMPLOY_REF FROM EMPLOYEE)
This is the error message i'm getting.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
Any help would be greatly appreciated. Thanks
I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable: Dim cmd As New SqlCommand("SELECT '+@LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCode", MyConnection) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCode)) cmd.Parameters.Add(New SqlParameter("@LanguageColumnName", LanguageColumnName))I have tried '+@LanguageColumnName+' and also just @LanguageColumnName but this variable isnt replaced for some reason.The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:SELECT de FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCodeWhat am I doing wrong?
View 1 Replies View RelatedHi,
I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.
I am using the technique from here : http://www.sqlis.com/311.aspx
Is this an SSIS bug ??? It is driving me up the wall.
Help would be much appreciated please.
Thanks
Jon
HI All,I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.1.)is there any systable still to be altered???2.) i have even tried alter database, doesnt work.3.)The master files have been taken from server1 (where tempdb points to d:data) to server2 ( where i need to point tempdb to point E:mssqldata). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path., too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path???
View 5 Replies View Relatedhi there,
i've made a report containing a subreport. the subreport is in a table. the table headers should repeat. but if the subreport expands more than one site, the table header doesnt repeat.
okay. i could place the table header in report header, but in report header i cant use fields from a dateset.
thanks for any idea, tobi
hi, i hope this is the correct forum?i have developed a small asp.net 2.0 web site with vwd using sqlExpress which appears to just bean data file and a log file in my app_data directory. there are two databases, the users one that is created by the login manager and my own that i created to store data. this all works fine on my laptop. i have an xp-pro machine setup with IIS and the frontpage web extentions, i have also installed the .net framework v2.0 on it and done all the windows updates. i have given app_data read/write access for the aspnet and network service users.i used the copy site feature of vwd to install on the xp box and although the site seems to workwhen it needs to bind a gridview to my sqldatasource it gives an error Cannot open user default database. login failed.Loginfailed for user 'rlxpaspnet'Any ideas? i have googled and tried all sorts, but to no avail, so i throw my self on your mercy!Cheers,russ
View 5 Replies View RelatedHello all!
I've a problem with my database. Till yesterday the option for Auto Grow of Database (10 %) was working very fine, but now it seems to be some problems with it. Finally I had to specify a restricted size for the database and then it again startd to give me some space in the database to write in. Ideally it should have worked automatically, isnt it ???
There is no problem with the space on the drive, I still have some 76 gb of free space there ...
Thanks in advance ...
Anjä
Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.
I am an administrator on the RS server, even on the report folders.
I can access via IE to the report manager.
Hi ,
In the below procedure in emp table name column is not null.
so when i try to update null value it should rollback previous transactions and should not commit.
but it is updating the previous queries and raising error on 3rd query. not rolliing back. I need to rollback previous queries.
the xactstate i get here is 1.
Please help me with this.
ALTER PROCEDURE [dbo].[tran]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
declare @name varchar(50)
set @name = null
update dept set dname='ddd' where id=10
update emp set name='eee' where id=354
update emp set name=@name where id=354
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE())=-1 ROLLBACK TRANSACTION
END CATCH
END
venp---
Hi ,
Hello, I tried this and it doesnt work, I just started up the service with -T flag on default instance but I havent been able to start it with -T flag 1400 on named instances.
How can I Know that the directory MSSQL.4 is the MIRROR instance??
C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBinn>sqlservr.exe -T
1400 MIRROR
I tried LUCHOMIRROR, MSSQLSERVER$MIRROR
and It doesnt work
Thanks
Another question I have, in the documentation it says that mirroring is only for evaluation purposes and not for production environments, what does it suppose to mean? MSFT didnt test the mirroring feature enough? If its not suppoerted why its included..?
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.
Hello everybody,
have following problem:
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt....
the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
Hello, I am trying to make this.
CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]
@nmpersona int,
@sortorder varchar(20)
AS
BEGIN
select nmpersona, dsprimernombre, dssegundonombre,
dsprimerapellido, dssegundoapellido
from personas
order by @sortorder
END
But I got this error. Please help
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.
The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.
I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.
Thanks
Hello all,
I need help concerning a differential backup on a MSDB database.
I received this message when I tried to backup it
Log from Windows
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=GlobalData Protector_(DEFAULT)_msdb_14_00_21.
Log from DataProtector
Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;
[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
Thank you for your help
in both vs2008 I added a ReportViewer control to my WinForm, intending to render an RS 2005 report remotely. In the design pane (Report Viewer Tasks Popup) I specify <server report>,
URL http://server name/reportserver$instance name
path /folder/report name
and everything is fine.
When I set these values in c# as
reportViewer1.ProcessingMode = ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri("http://server name/reportserver$instance name");
reportViewer1.ServerReport.ReportPath = "/folder/report name";
I started getting a msg saying "the source of the report definition has not been specified". If I hit refresh, the report renders. Before I started playing with the designer setting, and only had the c# stuff specified, I wouldnt even get the message, the control would appear with all RS buttons (eg page forward arrow etc) disabled.
Does anybody know why the message is appearing and how to get c#'s settings to work immediately like designer settings?
Hi,
I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric.
The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321.
I'm using Oracle connector from SQL to Oracle.
I've 64 bit SQL Server.
Attach the code, the Numeric field is SCORE.
select *
FROM
OPENROWSET('ORAOLEDB.ORACLE',aaaa;bbbb;cccc,
'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,
CAST(SCORE AS Varchar(250))
FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2')
Anyone can advice on resolution?
Thanks,
Assaf
While installing SQL Server 2005 Enterprise Edition on Windows Vista Ultimate
the setup alerts me "there are known compatibility problems, please
install SP2 after this". So ok, all went nice and installed without error.
IIS 7 installed fully, so all green in setup display.
(But after installation i do not see any service which begins with SQL***...)
At the end, the setup wanted to start the administration Tool.
Ok, but my name was not listed, so i could not add me to the permitted users.
I can not use any tool, because i have no rights.
Some Ideas?
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services-Clienttools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16546
Microsoft .NET Framework 2.0.50727.312
Betriebssystem 6.0.6000
thanks Frank
I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub
I am kicking myself, but cannot seem to get a simple stored procedure to return ANY records. I know it's soming that has to do with the Joins. Don't know what. I ran query analyzer and passed it a valid city and nothing was returned, but no errors either. If I run the query without any parameters, all the records are retrieved fine. ANY IDEAS?
CREATE PROCEDURE spUnitsbyCity
@city varchar
AS
SELECT A.unitcity, A.unitid, B.radioip, B.radiomac, C.videoserverip
FROM tbl_units as A
INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
WHERE A.unitcity = @city
GO
Hello,
Im having a problem with this query....SELECT a.ref, COUNT(b.entrypage) AS counter
FROM SiteCore.dbo.Campaigns AS a LEFT OUTER JOIN
webStats AS b ON b.entrypage LIKE '%?ref=partners%'
GROUP BY a.ref
But i want it to look more like this, so it pulls the approprite rows its selfSELECT a.ref, COUNT(b.entrypage) AS counter
FROM SiteCore.dbo.Campaigns AS a LEFT OUTER JOIN
webStats AS b ON b.entrypage LIKE '%?ref=' + a.ref + '%'
GROUP BY a.ref
The first one return the correct count where as the latter doesnt retuen anything, could any one shed any light as to what the problem could be?
Thanks
Bart
Can someone please help me fix this. The if statement always runs. I only want it to run if the statement is true and there is a result coming back. If the productID does not have any magazines linking to it, I don't want the If statement to run. Thanks in advance.
ASP.NET code:
If Not Magazine.GetMagazinesForProduct(ProductID) Is Nothing Then
blanklabel.Text = categoryDetails.Spacer
blanklabel.Visible = True
blanklabel2.Text = categoryDetails.Spacer
blanklabel2.Visible = True
magazinerecommendedlabel.Text = "Recommended/Featured in the following magazine(s):"
magazinerecommendedlabel.Visible = True
End If
-------------------------
magazine class:
Public Function GetMagazinesForProduct(ByVal productID As String) As SqlDataReader
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand("GetMagazinesForProduct", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@ProductID", SqlDbType.VarChar, 50)
command.Parameters("@ProductID").Value = productID
connection.Open()
Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function
-----------------------
Stored Procedure:
CREATE PROCEDURE GetMagazinesForProduct
(@ProductID varchar)
AS
SELECT Magazine.[Name], Magazine.[Issue], Magazine.SmallImagePath
FROM Magazine INNER JOIN MagazineProduct
ON Magazine.MagazineID = MagazineProduct.MagazineID
WHERE MagazineProduct.ProductID = @ProductID
RETURN
GO
--------------------------
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger:
------------------------------------------------------------
ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END
SET NOCOUNT ON
***Disclaimer***
This is homework, just need a hint.
I compare this to other scripts and can't find any differences. I'm getting a syntax error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Any hints would be great! Thanks.
USE StateUBookstore
ALTER TABLE dbo.Classes
ADD( InstructorID char (5) NOT NULL,
Credits int NOT NULL);
GO
ALTER TABLE dbo.Students
ADD( PhoneNO char(14) NULL,
Email char (50) NULL),
DROP COLUMN BookStoreEmp;
GO
ALTER TABLE dbo.Enrollment
ADD( Semester int NOT NULL,
EndDate DateTime NOT NULL);
Hi,
I tried to create a simple application based on the Sql Server CE samples and, as is typical when I play with databases, the program failed. In this case it failed to even load. The program has a form and a data source that I dropped onto the form. When I run the program the program breaks at this line:
Code Snippetthis._connection = new global::System.Data.SqlServerCe.SqlCeConnection();
The error is:
An unhandled exception of type 'System.DllNotFoundException' occurred in System.Data.SqlServerCe.dll
Additional information: Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
My first attempt at 'fixing' this was simply to copy the requisite DLLs to my bindebug folder. I found the DLLs here:
C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5 and copied these - sqlcecompact35.dll, sqlceca35.dll, sqlceme35.dll, sqlceoledb35.dll, sqlceqp35.dll and sqlcese35.dll, sqlceer35EN.dll - to my bindebug folder.
Now instead of the first error, I get this error:
An unhandled exception of type 'System.BadImageFormatException' occurred in System.Data.SqlServerCe.dll
Additional information: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
I am running Vista Business (x64) and using (the desktop version of) Sql Server Compact Edition 3.5 with Visual Studio 2008 Standard Edition. The test 'program' is written in C#. (Incidentally I can play with the database with no problems in Visual Studio 2008's server explorer. I can look at data, add data, etc.)
Can anyone please help?
Thanks.
I want to insert values from the querystring but nothing happens with this code (the sp works great from the Query Analyzer):
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"InsertCommand="spSearch_row_insert" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:QueryStringParameter Name="CustomerID" QueryStringField="1" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="SearchID" QueryStringField="2" DefaultValue="1" Type="String"/>
<asp:QueryStringParameter Name="SearchDate" QueryStringField="3" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="IP" QueryStringField="4" DefaultValue="1" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</form>
I'm very grateful for help!// G
I'm trying to work my way through the steps of using a User Id and Password in a connection string.
I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.
I thought it would be simple enough that I could post the whole thing (below)
The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:
{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:MyPathApp_DataVSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}
This occurs on the cn.Open statement below.
It gets past the login, so I know that the SQL User and password match up correctly.
==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServerSQLEXPRESS;AttachDbFilename=E:MyPathApp_DataVSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>
=========================
Page Code Behind (no controls on page)
------------
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())
Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())
End Sub
End Class
=================
The DB
Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)
I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer
This shows up in Count = 1 from running the page when Integrated Security = True
=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)
I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)
I attach the .mdf
I add VSST to the Database Users, and give it db_owner
I add VSST to the Table with all permissions checked.
=====================================
I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.
Any help on this would be greatly appreciated.
Thanks!