Re: SQL Server login problem - Database Discussion Boards

Re: Sql query problem

i.j.russell26-Aug-09 21:46

i.j.russell

26-Aug-09 21:46

SELECT person
FROM [tablename]
GROUP BY Person
HAVING COUNT(bar) > 1

SQL:what is wrong in following query

xodeblack25-Aug-09 22:07

xodeblack

25-Aug-09 22:07

what is wrong in following query.
datatype of @Name is text.
@Name is parameter passing in stored procedure

select * from students
       where first_name  in (  +  @Name + )

Re: SQL:what is wrong in following query

Blue_Boy25-Aug-09 22:55

Blue_Boy

25-Aug-09 22:55

Which error message do you get?

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: SQL:what is wrong in following query

xodeblack25-Aug-09 23:00

xodeblack

25-Aug-09 23:00

Error:

Msg 102, Level 15, State 1, Procedure GetFinancialInfo, Line 48
Incorrect syntax near ')'.

Re: SQL:what is wrong in following query

Sebastian Br.26-Aug-09 0:41

Sebastian Br.

26-Aug-09 0:41

I would take a look on the SQL statement directly before executing.

Re: SQL:what is wrong in following query

Ashfield26-Aug-09 1:35

Ashfield

26-Aug-09 1:35

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

SQL Server login problem

mlowry25-Aug-09 11:35

mlowry

25-Aug-09 11:35

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?

Re: SQL Server login problem

Vimalsoft(Pty) Ltd27-Aug-09 0:04

Vimalsoft(Pty) Ltd

27-Aug-09 0:04

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/

Re: SQL Server login problem

cdpace28-Aug-09 11:33

cdpace

28-Aug-09 11:33

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 Wink | ;)

Re: SQL Server login problem

mlowry29-Aug-09 6:32

mlowry

29-Aug-09 6:32

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.

xml schema

Ebube25-Aug-09 6:19

Ebube

25-Aug-09 6:19

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

Re: xml schema

Henry Minute25-Aug-09 6:56

Henry Minute

25-Aug-09 6:56

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.”

Creating the XML Schema

Ebube25-Aug-09 1:15

Ebube

25-Aug-09 1:15

how can i create xml schema for the for a typed xml datatype using management studio(sql server 2005)

Re: Creating the XML Schema

David Mujica25-Aug-09 3:13

David Mujica

25-Aug-09 3:13

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.

ADO.NET / Update Column in memory

Sebastian Br.24-Aug-09 23:42

Sebastian Br.

24-Aug-09 23:42

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.

Re: ADO.NET / Update Column in memory

N a v a n e e t h25-Aug-09 2:05

N a v a n e e t h

25-Aug-09 2:05

DataColumn has a DefaultValue property.

Navaneeth

How to use google | Ask smart questions

Re: ADO.NET / Update Column in memory

Sebastian Br.25-Aug-09 2:55

Sebastian Br.

25-Aug-09 2:55

It works fine, thanks.

CHECKING NULL VALUES

dotnetcsharpdev24-Aug-09 23:31

dotnetcsharpdev

24-Aug-09 23:31

HI,

I WANT TO GET A COUNT OF ALL THE VALUES FOR A COLUMN AS NULL IN A STORED PROCEDURE.
CAN U HELP PLZ.

Re: CHECKING NULL VALUES

dotnetcsharpdev25-Aug-09 0:19

dotnetcsharpdev

25-Aug-09 0:19

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>

Re: CHECKING NULL VALUES

Ashfield26-Aug-09 1:43

Ashfield

26-Aug-09 1:43

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

regarding foreignkey data

kheer24-Aug-09 23:12

kheer

24-Aug-09 23:12

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

Re: regarding foreignkey data

dotnetcsharpdev24-Aug-09 23:33

dotnetcsharpdev

24-Aug-09 23:33

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.

Re: regarding foreignkey data

kheer24-Aug-09 23:58

kheer

24-Aug-09 23:58

thats wat i am saying

when i enter other values i want to generate an popup message showing error

Re: regarding foreignkey data

dotnetcsharpdev25-Aug-09 0:16

dotnetcsharpdev

25-Aug-09 0:16

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

Re: regarding foreignkey data

Ashfield26-Aug-09 1:44

Ashfield

26-Aug-09 1:44

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.