|
I would take a look on the SQL statement directly before executing.
|
|
|
|
|
You cannot use an IN with a variable. Either use dynamic sql or, my preferred method, use a function to split the list into a table variable and join on that. A quick google will give you examples, but here is a starter
CREATE FUNCTION [dbo].[fn_DelimitedSplit]
(
@TextToSplit VARCHAR(8000), @Delimiter VARCHAR(255)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000),seq int identity)
AS
BEGIN
DECLARE @Word VARCHAR(255)
WHILE (CHARINDEX(@Delimiter, @TextToSplit, 1)>0)
BEGIN
SET @Word = SUBSTRING(@TextToSplit, 1 , CHARINDEX(@Delimiter, @TextToSplit, 1) - 1)
SET @TextToSplit = SUBSTRING(@TextToSplit, CHARINDEX(@Delimiter, @TextToSplit, 1) + 1, LEN(@TextToSplit))
INSERT INTO @SplitKeyword(Keyword) VALUES(@Word)
END
INSERT INTO @SplitKeyword(Keyword) VALUES(@TextToSplit)
return
END
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Using Visual Web Developer Express and SQL Server 2005 on an XP development machine.
Was using development server in VWDE to view pages, using following connection string:
|connectionStrings|
|add name="RHConnectionString" connectionString="server=MIKE;database=Rotary;Trusted_Connection=True;"/|
|remove name="LocalSqlServer"/|
|/connectionStrings|
Needed to test with SSL. Installed SSL certificate (using your makecert procedure, thanks). Moved web site to IIS and opened in VWDE. Development connection to database works, but pages get Login failed for user 'MIKE\IUSR_MIKE'.
SQL Server configured for Remote Connections and mixed Windows Authentication and SQL Server authentication.
Any help?
|
|
|
|
|
Go to your management Studio. Right click your Server and go to properties. in the Properties go to Security and look at the Options under Server Authentication. and tell us what is selected there.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Dont know if this may help but worth a try go to SAL Server Surface Area Configuration and then Surface are configuration for services and connections. Choose Remote connections, check Local and remote connections and choose TCP/IP only. Hope this helps 
|
|
|
|
|
Thanks for your attempts to help. I "solved" the problem by creating a new user ID and giving it the access rights I was trying to give my primary Windows login ID.
|
|
|
|
|
what is wrong with this code
create XML SCHEMA COLLECTION JointCollection
AS
N'<xs:schema
attributeformdefault="unqualified"
elementformdefault="qualified"
xmlns="http: www.w3.org="" 2001="" xmlschema"="">
<xs:element name=":Members"">
<xs:complextype>
<xs:sequence>
<xs:element maxoccurs="unbounded" name="Member">
<xs:complextype>
<xs:sequence>
<xs:element name="LastName" type="xs:string">
<xs:element name="FirstName" type="xs:string">
<xs:element name="MiddleName" type="xs:string">
<xs:element name="GenderId" type="xs:int">
<xs:element name="Telephone" type="xs:string">
<xs:element name="Email" type="xs:string">
<xs:element name="ContactAddress" type="xs:string">
<xs:element name="ContactCity" type="xs:string">
<xs:element name="ContactStateId" type="xs:int">
'
when i execute this in sql server 2005 i got error
Msg 9459, Level 16, State 1, Line 1
XML parsing: line 4, character 41, undeclared prefix
|
|
|
|
|
Judging by the example on MSDN here[^], you have missed out a whole chunk:
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
xmlns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
how can i create xml schema for the for a typed xml datatype using management studio(sql server 2005)
|
|
|
|
|
If you load the data into a datatable, you can invoke a method to create your scehema.
Dim dt As DataTable
dt.WriteXmlSchema()
Maybe that will help.
|
|
|
|
|
Okay, I'm new to the whole ADO.NET stuff, so don't punish me to hard if I ask a total bullshit question...
First, here's a piece of code (assume, aDataSet is already initialized and the table "aTable" also exists):
Dim aRow As DataRow
aDataSet.Tables("aTable").Columns.Add("isParent", System.Type.GetType("System.Boolean"))
For Each aRow In Me.mDataSet.Tables("aTable").Rows
aRow.Item("isParent") = True
Next
What I wanna do: Add the new column "isParent" to "aTable" (okay, I managed this), and initialize it with a standard value (I do this in the for-next-loop). But I thought, there must be a more elegant way to do this, without picking every single cell in a loop. Anyone know a good solution?
I don't need to write in a database, 'cause my aDataSet is build on the fly, without database representation.
|
|
|
|
|
DataColumn has a DefaultValue property.
|
|
|
|
|
|
HI,
I WANT TO GET A COUNT OF ALL THE VALUES FOR A COLUMN AS NULL IN A STORED PROCEDURE.
CAN U HELP PLZ.
|
|
|
|
|
Well replies not reqd.
I've got the query:
pasting it here incase helps someone in future:
<pre>
BEGIN
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
SELECT COUNT(ApplicantID) FROM APPLICANT
WHERE IsNull (CONVERT(DATETIME, DecryptByKey(DOB), GETDATE()),'01/01/01')= '01/01/01'
END
</pre>
|
|
|
|
|
Whats wrong with just saying where dob is null ? (allowing for your decryption)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have two tables. both tables have a common column (named=Rank)
I have made table1 column(Rank) as primary key
I have made table2 column(Rank) as foreign key
every thing is working properly
problem is when i enter some invalid data in table2(into Rank) which is not there in table1
I want to generate a pop up
is this possible
thanks in advance
|
|
|
|
|
U can enter only enter those values in table2(Rank) which are present in Primary Key.
This is the fundamental of having a foreign key. If you want to add values other than those in primary key remove the constraint.
|
|
|
|
|
thats wat i am saying
when i enter other values i want to generate an popup message showing error
|
|
|
|
|
If itz in a form, u can trap the error number and popup a window with the message.
Use "Try Catch"
and popup window in Catch
|
|
|
|
|
kiranreddy wrote: i want to generate an popup message showing error
You need to do that in your application, there is no such thing in sql.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
|
ID Name Access1 Access2 Access3 Access4 Access5
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast False False False False True
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator True True True True False
I have multiple results for 1 user. In the example I want the results to get to one row, with the final result (True, True, True, True, True).
So the query has to go through all the results and check if the Access Right is True or False. Is one of them is True the result of the column is true.
I tried to use Temp Tables but im stuck  Anyone has an idea?
I use MSSQL
|
|
|
|
|
That doesn't look the way I think it should. I'd rather have a table with more rows and fewer columns, and because of normalization I'd suggest two tables, one with names, one with access rights:
ID Name
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator
ID AccessNumber AccessRight
918a6fb8-573f-4744-b4de-019bc8322ce4 1 False
918a6fb8-573f-4744-b4de-019bc8322ce4 2 False
918a6fb8-573f-4744-b4de-019bc8322ce4 3 False
918a6fb8-573f-4744-b4de-019bc8322ce4 4 False
918a6fb8-573f-4744-b4de-019bc8322ce4 5 True
etc.
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc 4 False
which would be much easier to query (you can join on ID; you can select on right=true, etc), as well as easier to extend when new access numbers become necessary.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
The MAX operator won't work on bits, but you can cast them to integers;
SELECT MAX(CAST(Access1 AS INT))
,MAX(CAST(Access2 AS INT))
,MAX(CAST(Access3 AS INT))
,MAX(CAST(Access4 AS INT))
,MAX(CAST(Access5 AS INT))
FROM dbo.TestTableYou'd have to specify a decent WHERE-clause, though.
Another option would be the use of a function, that takes the ID as a parameter and which returns those five fields. It could then be used in a view
-- Edit --
..you should really consider Luc's post carefully, a simpeler design makes a lifetime of simple maintenance 
"Every normal man must be tempted at times to spit on his hands, hoist the black flag, and begin to slit throats."
-- H.L. Mencken, US editor (1880 - 1956)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
