MS SQL Server :: Results From Query Are TRUE And FALSE But Users Wanting YES And NO

Analysis :: Cube Dimension With Bool Filter Shows (blank) And True Instead Of False And True?

Jul 31, 2015

I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).

View 2 Replies View Related

In A Where How Can I Use True Or False

Oct 20, 2007

I have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false.
What would my where cluse look lile for this?

View 4 Replies View Related

True False

May 13, 2007

All

Can I ask what data type i use for a true false response (Boolean) in my table?

Thanks

Gibbo

View 1 Replies View Related

Return True False

Mar 20, 2008

Hi,
I need to check the existence of a row in a table.
So i am using an if condition
like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20)
AS
BEGIN

SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
return 1
end
else
begin
return 0
end

END

The stored proc is executing but on the Data Access Layer
I have this
Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));

Some how I am always getting false . How can I fix this?
Thanks

View 7 Replies View Related

How To Change From True Or False To 1 Or 0

Oct 29, 2007

Hello

I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?

I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?

Thanks a lot.

View 1 Replies View Related

Trying To Insert Checkbox True/false Into Db

Aug 15, 2005

Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen

View 2 Replies View Related

How Transaction Return True And False

Jun 10, 2008

Sir

I want to Return 1 and 0 after update , delete , Insert statement

IF Records Effected Return 1 else return 0

Pls help me out .........Sir

Yaman

View 4 Replies View Related

Setting A Bit Value To (@param='C') Ie True Or False

Nov 19, 2007

I want to pass a single char to a query and use that to set two flags.

(

@ID int,

@AssessedID int,

@CompetencyID int,

@Status char,

@Creator int

)

AS

UPDATE P4_Assessment

SET P4_Cancelled_f = (@Status = ('C')),

P4_Competent_f = (@Status = ('P')),

P4_Date = getdate(),

P4_Creator = @Creator

WHERE P4_ID = @ID

I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))

Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?

TIA

View 3 Replies View Related

Convert Function True/false Test

Apr 5, 1999

I need a way to test if a convert function will work before I process it. if it fails, I want to intercept the error and return my own error to the front end

ex
if convert(int,@x) is true then do; else do

please email me if anyone has some advice
Mike

View 1 Replies View Related

T-SQL (SS2K8) :: If Not Exists Returning False When Should Be True

Jul 3, 2014

Actually title should be returns true when should false.

I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:

IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END

The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.

View 9 Replies View Related

Insert True/False Into Boolean Field

Jun 12, 2008

When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:

INSERT INTO tblTable (IsSomething)
VALUES (False)

I've heard that a better way is to use 1 and 0, or something.

View 1 Replies View Related

Dtabase Design For Storing True/false Questions

Feb 12, 2007

Hai,

I am trying to design a database for storing trule/false questions.
I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses.
So does the following tables and fields suffice to achieve that?.

Questions_table(id(autogenerated),course_id,Questi on(text type))
id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.

Response_table(id,response)
Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.

So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.

Thanks,

View 2 Replies View Related

DropObjectsFirst=False; CopyData=True; ExistingData=Replace

May 12, 2008

Hi,
I'm using SSIS to update the destination table.

Both servers are running identical SQL Server 2005 versions.

I want to copy data and replacing existing data WITHOUT dropping the destination table first.

The SSIS package works when:
1) DropObjectsFirst = TRUE
2) CopyData = TRUE
3) ExistingData = REPLACE

The SSIS package does not work when:
1) DropObjectsFirst = FALSE
2) CopyData = TRUE
3) ExistingData = REPLACE

The error I get is the "destination table already exists". Of course, it already exists. I just want to copy/update the destination table. So, why does SSIS insist on dropping the table first?

View 3 Replies View Related

SQL 2012 :: Enable Promotion Of Distributed Transactions For RPC - Set To TRUE Or FALSE

May 14, 2015

It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.

Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:

Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:

OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.

... when set to off, the error goes away.

View 8 Replies View Related

Wanting To Move An Entire Database Using Backup? Export? That Takes All Users, All Data, All Permissions

Apr 25, 2008

I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,

View 2 Replies View Related

Different Query Results For Different Users?

Apr 22, 2008

I have an SQL2K5 database where a group of users share a custom database role and access the database via trusted connections (Windows authentication).

I have a stored procedure which reports all of the users in this database role using an SQL statment something like this:

select name
from sysusers
inner
join sysmembers on
sysmembers.memberuid=sysusers.uid
and sysmembers.groupuid=7

As the programmer, and the dbo, when I log in to execute this query, I get the list of a dozen, or so, users.

When one of the users executes the same query, they only see a return of their own username.

I'd like to have the users be able to execute this query and get the full list of users. Can someone tell me how to accomplish this?

I assume that this is some security, or permissions, thing involving system tables, or Windows authentication security.

Thank you.

BTW--the users never actually directly execute the stored procedure I mentioned; They are using a VB client application that affects the execution.

View 4 Replies View Related

Minimizing Penalty (weighted Sum Of False Positives Plus False Negatives)

May 25, 2006

I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.

How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).

View 5 Replies View Related

Select Query To Nullable=false

Mar 6, 2008

greetings

i am use this query to select the primary field colums in a table
"select Column_Name as PrimaryKeycolumn
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'tbl_Activity'
and Constraint_Name like 'PK_%'"

but i want to select the fields which have a nullable=false
for that i want know the information schema for null

thank u

View 1 Replies View Related

Submit Users ID With Results.

Mar 8, 2007

Hi
I am trying to create a Quiz system and i need to put the user's Unique ID in the Result Data Table, so that the when they submit there results it puts there Unique ID by the side so that they can view it in there profile, and not anyone elses.
Thanks
Gareth Cork

View 1 Replies View Related

Is It Possible To Restrict The Users To Export Report Results Only To Excel ?

Jan 2, 2007

Is it possible to restrict the Users to Export Report Results only to Excel ?

So instead of the default drop down options of csv, pdf, tiff,Web Archive,Web Archive etc... can we get it to just display Excel.

Any help would be appreciated.

View 1 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!

View 1 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Wanting To Use Impersonation

May 2, 2007

Hi,

I like to use impersonation using multiple databases and a user with no login.

I'm working with Powerbuilder 10. I can change users using the command Execute Immediate "EXECUTE AS USER = 'username'". Unfortunately, I can't execute the command 'REVERT' from Powerbuilders Execute Immediate command. The Execute Immediate command prefixes the 'REVERT' command with a exec. ie. exec REVERT.

I thought I could encapsulate the REVERT command in a procedure and run the procedure using Execute Immediate. But, I'm new to SQL Server and I'm not sure if I can.

Does anyone know how to solve this problem? Thanks.

TF

View 3 Replies View Related

Newbie Wanting To Learn

Sep 11, 2004

Hi All,
I am totally new to databases. I am starting from the absolute beginning. I want to learn MS-SQL and was wondering how/where to start. I need info on everything from installation and set-up on an Windows XP PC to programming in SQL. Is there a book or website that can guide me. I am fairly decent at programming and am able understand technical books. Installation and set up are my main concerns right now, since I believe that once I have a stable system to learn on, SQL should be easy.
Thanks!

View 6 Replies View Related

SqlDataReader Object Not Wanting To Close

May 4, 2007

Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. "
on the lne:
Line 95: DR_IndJobPostings = oComm_IndPostings.ExecuteReader();
I have closed the DR_IndJobPostings object after every use of it as see n on line number 175

----------- Code--------------1 /// <summary>
2 /// Will generate and email job alerts based on the frequency
3 /// </summary>
4 /// <param name="frequency">WEEKLY or MONTHLY</param>
5 /// <param name="oServerIN">Instance of Server from ASPX page</param>
6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN)
7 {
8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]);
9 oConn.Open();
10
11 SqlCommand oComm;
12
13 emailSystems oEmail = new emailSystems();
14 HttpServerUtility oServer = oServerIN;
15
16 bool validCall = false;
17 bool industryHasPostings = false;
18 string sEmail = "";
19 string sEmailTemplate = "";
20 string sVacListForEmail = "";
21
22 int IJPost_VacId = 0;
23 int IJPost_EmpId = 0;
24 string IJPost_Req = "";
25 string IJPost_KeyRes = "";
26 string IJPost_VacTitle = "";
27 string IJPost_VacJobTitle = "";
28 string IJPost_VacUrl = "";
29
30 int loopCounter1 = 0;
31
32 string CandEmailAddress = "";
33
34 oComm = new SqlCommand();
35 oComm.Connection = oConn;
36 oComm.CommandType = CommandType.Text;
37
38 SqlCommand oComm_IndPostings = new SqlCommand();
39 oComm_IndPostings.Connection = oConn;
40
41 SqlDataReader DR_Industries;
42 SqlDataReader DR_IndJobPostings;
43 SqlDataReader DR_AlertList;
44
45 if (frequency == "WEEKLY" || frequency == "MONTHLY")
46 {
47 validCall = true;
48 }
49
50 if (validCall)
51 {
52 if (frequency == "WEEKLY")
53 {
54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt");
55 }
56
57 if (frequency == "MONTHLY")
58 {
59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt");
60 }
61
62 sSql = "" +
63 "SELECT [id],[industry] FROM S_Utils_Industries " +
64 "WHERE [active] = 1";
65 oComm.CommandText = sSql;
66 DR_Industries = oComm.ExecuteReader();
67
68 if (DR_Industries.HasRows)
69 {
70 //
71 // Loop through each active industry
72 //
73 while (DR_Industries.Read())
74 {
75 industryHasPostings = false;
76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0);
77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString();
78
79 // Get all active vacancy postings for this
80 // industry
81 sSql = "SELECT [id]," +
82 "[emp_id], " +
83 "[vac_Requirements]," +
84 "[vac_KeyResp]," +
85 "[vac_VacTitle]," +
86 "[vac_VacJobTitle]," +
87 "FROM [S_Vacancies] " +
88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " +
89 "[status] = 1 AND " +
90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " +
91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'";
92
93 oComm_IndPostings.CommandText = sSql;
94
95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader();
96
97 //
98 // If there are job vacancy postings for the industries
99 //
100 if (DR_IndJobPostings.HasRows)
101 {
102 industryHasPostings = true;
103 sEmail = sEmailTemplate;
104
105 //
106 // Loop through the job postings for this industry
107 //
108 while (DR_IndJobPostings.Read())
109 {
110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0);
111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1);
112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString();
113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString();
114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString();
115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString();
116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs";
117
118 sVacListForEmail += IJPost_VacTitle + @"
119
120 Job title: " + IJPost_VacJobTitle + @"
121
122 Key Responsibilities
123 " + IJPost_KeyRes + @"
124
125 Requirements
126 " + IJPost_Req + @"
127
128 " + IJPost_VacUrl + @"
129
130 ============================================================
131
132 ";
133 }
134
135
136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail);
137
138 // If there are job postings for this industry
139 // get all the people who signed up for a job alert
140 if (industryHasPostings)
141 {
142 sSql = "SELECT [S_JobAlerts].[IndustryId]," +
143 "[S_JobAlerts].[candidateEmail] " +
144 "[S_Cv_Status].[Cv_Online], " +
145 "[S_Cv_Status].[usingShortResume], " +
146 "[S_Cv_Status].[iHasHadIntro] " +
147 "FROM [S_JobAlerts] " +
148 "INNER JOIN [S_Cv_Status] ON " +
149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " +
150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " +
151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + "";
152
153 oComm.CommandText = sSql;
154
155 DR_AlertList = oComm.ExecuteReader();
156
157 // If there are candidates who signed up
158 // for a job alert
159 if (DR_AlertList.HasRows)
160 {
161 //
162 // Loop through each job alert for this industry
163 //
164 while (DR_AlertList.Read())
165 {
166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString();
167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail);
168 }
169
170 }
171 DR_AlertList.Close();
172
173 }
174 }
175 DR_IndJobPostings.Close();
176
177 }
178
179 }
180 DR_Industries.Close();
181
182
183 oConn.Close();
184 } // END: if (validCall)
185
186 }

View 1 Replies View Related

Linked Server Not Wanting To Connect

Sep 11, 2007

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

============= ERROR TEXT END ==============

Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

============= ERROR TEXT END ==============

Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB19', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB19',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'use remote collation', @optvalue=N'true'

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB21', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB21',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'use remote collation', @optvalue=N'true'

=========== CODE END ==========

Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.

@provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

View 5 Replies View Related

SSIS Issue With Flat Files Not Wanting To See CR/LF

Mar 14, 2008

I have an issue with an SSIS package I was hoping to get some commentary on.

I am taking a flat file, scrubbing it in SSIS, and exporting it into a different package.

The files are fixed width. Both Fixed Width, and Ragged Right settings are not working, I've tried both.

My current config on the flat file connection is set to use the file name as variable. The format is "Ragged Right". The Text Qualifier is "<none>". The Header row and Header rows to skip are <CR><LF> and 0. The column names in first row are not checked.

In the advanced tab, 43 columns are defined. The output and input have all been verified numerous times to conform to the file spec.

When previewing the file in preview, only one record shows up even though the file contains multiple.

What it's doing is the last column contains the CR and LF characters and then it continues putting the other rows in that column (it is ignoring the CR LF and not going to the next row)

WHen I click on "Columns" in the flat file connector, it displays the rows of information as it should. When I click back down to preview a second time, the rows are displayed as they should.

The initial time you preview, the rows are jacked up and all smashed onto the first row. WHen trying to execute the package, I get a truncation error because the last column is supposed to be 7 in length, and contains multiple data rows in it.

When trying the option "Fixed Width" it does the same thing. It ignores the CR LF and makes everything one row.

Can someone please explain what it is I'm doing wrong? Or why when I click to preview the first time it is broken, but when clicking on columns and then back to Preview it is fixed?

View 6 Replies View Related

Wanting To Condense A Bunch Of SELECT Statements

Aug 27, 2007

Hi All,

Not sure if this is exactly the place to post this, but here it goes anyways.

I am writing a ASP.Net/C# program and I am interacting with a MS Access database in order to derive data on user login and logout times. Basically, I am trying to create a line graph that will display the number of users over the course of a user specified timespan. Currently, I am doing this by look at the number of users that were logged on during each minute of the timespan.

My database table setup consists of a EmployeeID column (Text), Logon Date (Date/Time), and Logoff Date (Date/Time). I have also created an index on the Logon Date and Logoff Date columns.

In order to view the number of users during a minute of the timespan I use a Jet SQL query of the following format

Code Snippet
SELECT DISTINCT Count (EmployeeID) AS [User Count]
FROM ProgramName
WHERE ([Logon Date] < #August 27, 2007 11:45:00# OR [Logon Date] Is Null) AND ([Logoff Date] > #August 27, 2007 11:45:00# OR [Logoff Date] Is Null)

The problem is that using this method I have to execute 1,440 queries for each day in the timespan. Currently this takes about 25 seconds to execute if the timespan is a full workweek (7,200 queries).

Now the question. Is it possible to create a SELECT statement that will return user counts for multiple minutes? Like maybe a SELECT statement that returns a column of counts for every minute in an hour? If it is possible, does anyone have any examples? I am hoping by lowering the number of queries my program has to execute I will also cut down the time required for the code to run.

I am pretty new to SQL, so any guidance or advice is very appreciated.

Thanks!

View 4 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related