Help with connection string

I have a connection string in my web.config file:

<add key="cnString" value="data source=ServerName;initial catalog=DatabaseName;password=Password;persist security info=True;user id=UserName;packet size=4096" />

and it works just fine with the SQL production server.
Recently the testing SQL server was created and I can login to it with Enterprise Manager.

But when I try to change the connection string just putting a new server name, database, user name, and password, it gives me the error:

Login failed for user 'UserName'

What should I look in the database or SQL server that is different from a production one? Or what should I ask since I don't have admin rights to both servers?

BTW, I've already tried to play with Trusted_Connection parameter (True/False) and it didn't help.

Thank you in advance for your help.You should check whether the user you are specifying in the connection string exists on the sql server and whether that user has access to the database that you are setting as "Initial catalog"

Hope that helps
Kashif|||Yes, the user exists and has an access to that database.

Help With Another Query

Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>

Help With Another Query

Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
Chuck
Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>

Help With Another Query

Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>

Help with a special stored procedure

Hi

I have a table there have a primary key, this value have i also in another
table as a forreignkey, so good so far.

Here it is
If the number in may primarykey in table 1 , dosn't exits in table 2 then
delete records in table 1

I have made this in a ASP page with a view there list all records where the forreignkey in table 2 are NULL and then delte all records in table one

Can i made this as a stored procedure ?

regards

alvin

You really want a trigger on table 1 to check table 2 before insert.

Research INSTEAD OF Insert Triggers.

What this will do is instead of inserting a new record into table 1, it will check table 2 for a match. If there is no match, no record is inserted into table 1.

It's better to catch the record before it goes in, than to insert the record and then have to remove it later.

Adamus

|||

Hi

I don't believe you understand

When i made a post in table one i also make a post in table 2

All works fine

But tabel 2 i also connected with table 3 and here i have in my diagram a cascade delete

So when i delete a record in table 3 it's delete a record or more in table 2
then i have my record in table 1, this i can't delete when i delete the record in table 3
so what i want is to delete all record in table 1 if the record in table 2 are deleted

hop you understand ?

Alvin

|||

Ok table 3 is new...but the answer is still the same...triggers not sp's

When you delete from any table, also delete from other tables...correct?

So you want INSTEAD OF DELETE

Adamus

|||

I try to explain and maybe you can help

In table 1 i have a ID theis ID can bee many times in table 2
Table 2 have also a int there connect to table3

all works fine

When a date field in table3 is over current date then i delete the record in table
And when it does this it also delete the record in table2.

after sometime all the records in table2 there have the ID from table 1 is been deleted
and when there are no more record in table2 there in my feild have the same numbers
as the ID in table 1 then i wnt to delete the record in table 1

Like:

Delete all records in table 1 if table1.ID <> from table2.field

In vbscript i can make a loop to check if the Id from table1 are in table2
if the catch this number = do nothing
if the find the number = delte record

i Can't explain it better. Sorry

Alvin

|||

It looks to me like you're wanting something like this:

create procedure delete_records_from_table1

as

delete from table1

from table1

left join table2

on table1.ID = table2.ID

where table2.ID is null

The left join will include everything in table1, regardless of whether there's a matching record in table2, and then the where clause excludes all of the records where there is a match in table2. This just leaves those records where there's no match in table2, and these can be deleted.

Is this the sort of thing you were looking for?

Iain

|||

Yes yes yes

Thanks

Alvin

sql

Help with a query

Hi,
I'm new to fulltext search and am having a problem formulating a query.
I have a table with key, title, description within it. I'm trying to do
a search, but weight results in the title higher than the description.
I've only written this so far.
select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
rank desc
Any help on doing a similar query, but ranking results in title higher
appreciated.
Spondishy,
Could you post back with the full output of the following SQL code as this
is helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Additionally, have you tried using the WEIGHT parameter on the title column?
Review the SQL Server BOL for either CONTAINSTABLE or FREETEXTTABLE for more
info on Weight.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Spondishy" <spondishy@.tiscali.co.uk> wrote in message
news:1131363042.622970.188750@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm new to fulltext search and am having a problem formulating a query.
> I have a table with key, title, description within it. I'm trying to do
> a search, but weight results in the title higher than the description.
> I've only written this so far.
> select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
> title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
> rank desc
> Any help on doing a similar query, but ranking results in title higher
> appreciated.
>
|||I'd try something like this
CREATE TABLE HTML
(pk int not null identity CONSTRAINT htmlPK PRIMARY KEY,
TITLE Varchar(20),
DESCRIPTION Varchar(20)
)
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('this is a test','this is a
test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('nada','test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','nada')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test nada','nada test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test test','test test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','test test test test')
exec sp_fulltext_catalog N'test', N'create'
GO
exec sp_fulltext_table N'[dbo].[HTML]', N'create', N'test', N'htmlPK'
GO
exec sp_fulltext_column N'[dbo].[HTML]', N'TITLE', N'add', 1033
GO
exec sp_fulltext_column N'[dbo].[HTML]', N'DESCRIPTION', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[HTML]', N'activate'
GO
sp_fulltext_catalog 'test','start_Full'
SELECT pk, TITLE, DESCRIPTION, TITLE.RANK, DESCRIPTION.RANK FROM HTML
JOIN (SELECT * FROM CONTAINSTABLE(HTML, DESCRIPTION,'test')) AS DESCRIPTION
ON DESCRIPTION.[KEY]=HTML.PK
JOIN (SELECT * FROM CONTAINSTABLE(HTML, TITLE,'test')) AS TITLE ON
TITLE.[KEY]=HTML.PK
ORDER BY TITLE.[RANK] DESC, DESCRIPTION.RANK DESC
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spondishy" <spondishy@.tiscali.co.uk> wrote in message
news:1131363042.622970.188750@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm new to fulltext search and am having a problem formulating a query.
> I have a table with key, title, description within it. I'm trying to do
> a search, but weight results in the title higher than the description.
> I've only written this so far.
> select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
> title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
> rank desc
> Any help on doing a similar query, but ranking results in title higher
> appreciated.
>

Help using Lookup

Hi

I am trying to use lookup to see if a item esists in my table ( 3 key fields ). If the lookup fails I want to insert the records. If it succeeds I have put a recordcount to catch the items that are not required. I don't think that I understand the settings for failed rows. I have tried setting the Configure Error Output to redirect, but this does not seem to work. I have the below errors.

[SQL Server Destination [151]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: component "SQL Server Destination" (151) failed the pre-execute phase and returned error code 0xC0202071.

Can someone please advise me how to set up this component to work for my application

Thanks

ADG

SQL Server Destinations only work on the machine that have the actual SQL Server installed locally. Is this the case?|||

Hi Phil

I have SQL Server 2005 Developer Edition installed on a stand alone machine at the moment. Previous two task flows use a SQL Server Destination (same table) and work OK.

I did not know that I could not use a SQL Server Destination on a network. Eventually I will migrate my solution to one of our group servers, currently I am developing the solution, or rather battling to learn SQL server. I guess that maybe I should use another type of destination once the above bug is ironed out.

Regards

ADG

|||

Bug fixed

I deleted the Look up and set it up again and all is well. Not sure what i did first time, ( I fiddled with too many settings I suspect

Help Using Asymetric/Symetric key in a Scalar UDF

The error message I get is as follows:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
&
Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.

Here is the code I am trying to implement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase]
(
)
RETURNS varchar(30)
AS
BEGIN
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
DECLARE @.passphrase varchar(30)
SELECT @.passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
RETURN @.passphrase
END

Anyone have any suggestions? TIA

You cannot use OPEN SYMMETRIC KEY in a function. Write a procedure instead - you can use an OUTPUT argument to return the passphrase. For an example, see http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Laurentiu,

I found your article after I posted that message. I am still getting this error though:

SELECT permission denied on object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'.

Here is what I have done so far. I moved OPEN SYMMETRIC KEY to a stored procedure (listed below):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspPassPhraseGet]
@.pss varchar(30) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
SET @.pss = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
END

I granted execute permissions to the role that contains the user I am using to access this stored procedure. I am calling this stored procedure from within another stored procedure to access the encrypted passphrase contained in a table encrypted by the Symmetric/Asymetric keys. See example below:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspFreeFormList]
@.userid int
AS

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT
...
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
...
FROM
...
WHERE
...

I know the permissions to the stored procedures are correct because if I set the @.pss output parameter in uspPassPhraseGet to a static string everything works fine. It is when I am accessing the symmetric key that I don't have select granted on sys.symmetric_keys. I have gone in and explicitly granted SELECT for the role I am using to sys.symmetric_keys. However this isn't working. What am I missing? Is there some archane setting I am missing? Also, on a more academic note, is this the right approach to protect a passphrase used in the DecryptByPassPhrase/EncryptByPassPhrase function or is there a better suggestion/scenario to use?

Thanks for your time and attention,

Mike

|||

It looks like you have explicitly denied SELECT permissions on the sys.symmetric_keys catalog to either the use or one of the roles he belongs to.

To see to whom you have denied the permissions, execute the following query in your database:

select user_name(grantee_principal_id) from sys.database_permissions where state = 'D' and major_id = object_id('sys.symmetric_keys')

Then, take the result and execute

revoke select on sys.symmetric_keys to grantee

replacing grantee with the result of the previous query.

Regarding the code, why do you retrieve that GUID in uspPassPhraseGet? And why do you use a passphrase for encryption instead of using the symmetric key itself?

Thanks
Laurentiu

Help to optimize query

Hi,
I have these two tables in a Database

ITEMS
IDnumeric (Primary key)
ZDIDnvarchar 3 (not null)
IDF_Familynumeric(not null)
Descriptionnvarchar40 (not null)

DATAS
IDnumeric(Primary Key)
IDF_Itemnumeric(Foreign key)
IDF_Referencenumeric(Foreign Key)
[Date]smalldatetime(not null)
Containernchar10(not null)
Averagedecimal(not null)
[%Compliance]decimal(not null)
[%OutOfRange<MinTg]decimal(not null)
[%OutOfRange>MaxTg]decimal(not null)
Targetdecimal(not null)
[Min]decimal(not null)
[Max]decimal(not null)

The table DATAS has 4000000+ records

I'm running this query:

SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description
FROM Items as I, Datas as D
WHERE D.IDF_Item = I.ID AND I.IDF_Family = 84
AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'

it's taking 4-5 minutes to run.
The result is correct, there is no thing on that date.
I've done a reindex, but still the same thing.

What can I do?

Thanksbie2 (Francois.Tardif@.gmail.com) writes:
> I have these two tables in a Database
> ITEMS
> ID numeric (Primary key)
> ZDID nvarchar 3 (not null)
> IDF_Family numeric (not null)
> Description nvarchar 40 (not null)
> DATAS
> ID numeric (Primary Key)
> IDF_Item numeric (Foreign key)
> IDF_Reference numeric (Foreign Key)
> [Date] smalldatetime (not null)
> Container nchar 10 (not null)
> Average decimal (not null)
> [%Compliance] decimal (not null)
> [%OutOfRange<MinTg] decimal (not null)
> [%OutOfRange>MaxTg] decimal (not null)
> Target decimal (not null)
> [Min] decimal (not null)
> [Max] decimal (not null)
>
> The table DATAS has 4000000+ records
> I'm running this query:
> SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description
> FROM Items as I, Datas as D
> WHERE D.IDF_Item = I.ID AND I.IDF_Family = 84
> AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'
> it's taking 4-5 minutes to run.
> The result is correct, there is no thing on that date.
> I've done a reindex, but still the same thing.

A shot in the dark: change 84 to convert(numeric, 84). If that does not
cut it, please answer the questions below:

How many rows are there in Items?

Exactly what indexes are there on the table? Please indicate which
indexes that are clustered.

Can you run the query preceeded by SET STATISTICS PROFILE ON, and
post the output?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>A shot in the dark: change 84 to convert(numeric, 84). If that does not
>cut it, please answer the questions below:
Nothing changed.

>How many rows are there in Items?
30

>Exactly what indexes are there on the table? Please indicate which
>indexes that are clustered.
On DATAS ID is a clustered Index

>Can you run the query preceeded by SET STATISTICS PROFILE ON, and
>post the output?
01SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description FROM
Items as I, Datas as D WHERE D.IDF_Item = I.ID AND I.IDF_Family =
convert(numeric, 84) AND D.Date BETWEEN '5/18/2006' AND
'5/18/2006'210NULLNULLNULLNULL18.225489NULLNULLNULL63.874825NULLNULLSELECT0NULL
01 |--Nested Loops(Inner Join, OUTER
REFERENCES:([D].[IDF_Item]))231Nested LoopsInner JoinOUTER
REFERENCES:([D].[IDF_Item])NULL18.2262020.07.6185526E-511063.874825[I].[Description],
[I].[IDF_Family], [I].[ZDID], [I].[ID]NULLPLAN_ROW01.0
01 |--Sort(DISTINCT ORDER BY:([D].[IDF_Item]
ASC))243SortDistinct SortDISTINCT ORDER BY:([D].[IDF_Item]
ASC)NULL18.2262021.1261261E-24.4788996E-41663.866585[D].[IDF_Item]NULLPLAN_ROW01.0
01 | |--Clustered Index
Scan(OBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM'))254Clustered Index
ScanClustered Index
ScanOBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM')[D].[Date],
[D].[IDF_Item]41.49002556.7212834.96644977861.687733[D].[Date],
[D].[IDF_Item]NULLPLAN_ROW01.0
00 |--Clustered Index
Seek(OBJECT:([AccessReporting].[dbo].[Items].[PK_Items] AS [I]),
SEEK:([I].[ID]=[D].[IDF_Item]), WHERE:([I].[IDF_Family]=84) ORDERED
FORWARD)263Clustered Index SeekClustered Index
SeekOBJECT:([AccessReporting].[dbo].[Items].[PK_Items] AS [I]),
SEEK:([I].[ID]=[D].[IDF_Item]), WHERE:([I].[IDF_Family]=84) ORDERED
FORWARD[I].[Description], [I].[IDF_Family], [I].[ZDID],
[I].[ID]1.06.3284999E-37.9603E-51018.1532737E-3[I].[Description],
[I].[IDF_Family], [I].[ZDID], [I].[ID]NULLPLAN_ROW018.226202|||Tried this simple query

SELECT D.ID
FROM Datas as D
WHERE D.Date BETWEEN '5/18/2006' AND '5/18/2006'

And still took me 3 minutes, So maybe the problem is with the index on
DATAS|||Solved Created a non clustered index for Date and IDF_Items.