Hi,
I have a situation where I am loading data into a staging
table for multiple data sources. My next step is to pick up the
records from the staging table and compare with the data in the
database and based on the certain conditions, decide whether to insert
the data into the database or update an existing record in the
database. I have to do this job as an sp and schedule it to run on the
server as per the requirements. I thought that cursors are the only
option in this situation. Can anyone suggest if there is any other way
to achieve this in SQL 2005 please.
select name,id,case when maths>=35 then 'pass' else 'fail' end as maths, case when science>=35 then 'pass' else 'fail' end as science, case when tamil>=35 then 'pass' else 'fail' end as tamil , case when english>=35 then 'pass' else 'fail' end as english, case when social>=35 then 'pass' else 'fail' end as social from student
I dont want to use case in my querey? Is there any way can i replace case with something? Thanks in advance!
I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
hi i have a view in oracle. in that i am using decode function. same query i want to write it in sqlserver. what it is the alternate to decode.
this is a cross tab query
SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2, SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4 FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
-- tested schema below ---- create tables --create table tbl_test(serialnumber char(12))gocreate table tbl_test2(serialnumber char(12),exportedflag int)go--insert data --insert into tbl_test2 values ('123456789010',0)insert into tbl_test2 values ('123456789011',0)insert into tbl_test2 values ('123456789012',0)insert into tbl_test2 values ('123456789013',0)insert into tbl_test2 values ('123456789014',0)insert into tbl_test2 values ('123456789015',0)insert into tbl_test2 values ('123456789016',0)insert into tbl_test2 values ('123456789017',0)insert into tbl_test2 values ('123456789018',0)insert into tbl_test2 values ('123456789019',0)insert into tbl_test values ('123456789011')insert into tbl_test values ('123456789012')insert into tbl_test values ('123456789013')insert into tbl_test values ('123456789014')insert into tbl_test values ('123456789015')-- query --Select serialnumber from tbl_test2where serialnumbernot in (select serialnumber from tbl_test) andexportedflag=0This query runs quite fast with only the data above but when bothtables get million plus rows, the query simply bogs down. Is there abetter way to write this query?
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users. these html files that are generated are processed via a JOB in sql server. I am in process of replacing of the web assistant procedures. Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure. Is there any other possible way to do it?
"Update STR_RGSTR_DFLT " & _ " Set GST_SVY_FREQ_P = " & iNewSurveyPct & _ " Where dhc_co_c = " & iCo & " and store_i = " & iStore & _ " And RGSTR_PROTO_C not in (0,3,4,8,10,11,12,13)"
in this the values in the where clause 'not in' i.e (0,3,4,8,10,11,12,13) are directly given but we want some alternate solution. the entire script is writen in VBScript. these values are already populated in an array like proto_code_ary = {0,3,4,8,10,11,12,13} shall we use that array directly without hard coding the values in the query?
In times past I connected my web app to SQL Server by embedding the following in the web.config file: <connectionStrings> <add name="XyzApp_DB" connectionString="Data Source=MyServerSQLExpress;Database=XyzApp;User ID=sa;Password=secret_password"/> </connectionStrings> And it worked fine. But it was pointed out to me that this wasn't a very proper way to do things. So I was advised to set things up with a different connection string: <connectionStrings> <add name="XyzApp_DB" connectionString="Data Source=MyServerSQLExpress;Database=XyzApp;User ID=Xyz_Admin;Password=different_password"/> </connectionStrings> To get this to work, I've followed these instructions: A more secure login than “sa� is required to access your application. In the “connectionStrings� section of “Web.config� you’ll find a User ID called “Xyz_Admin�. Here are the steps to ensure that your DB has this login id:
a. Using the Microsoft SQL Server Management tool, look in the root for the “Security� folder and open it. Inside that, open the “Logins� folder.
b. If “Xyz_Admin� doesn’t exist then right-click on “Logins� and choose “New Login�:
i. Enter “Xyz_Admin� in the “Login name� textbox.
ii. Click on “User Mapping� on the left side and in the ‘Map’ column check the box beside “XyzApp�.
iii. Go back to the “General� page and choose SQL Server Authentication.
iv. Specify any secret password you wish.
v. You can uncheck “Enforce password expiration� if you wish.
vi. Specify “XyzApp� as the default database and press OK.
c. Open “Databases�, right-click on “XyzApp� and select “New Query�. Run this query: sp_change_users_login 'update_one', 'xyz_admin', 'xyz_admin'
Now, I was pretty sure that this procedure was working fine but it doesn't seem to work for me today. I know that the client computer I'm using can connect to the DB on the server because I temporarily changed the User Id to "sa" and it connected fine. But when I change it back to "Xyz_Admin" it does not. So I'm thinking that there's something incorrect with the procedure above but I don't know what.Any ideas?Robert W.
Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
We just erected a new firewall that only allows PASV FTP. Now, my SQL Server can't call master..xp_cmdshell "ftp ...".
Does anyone know how to make the NT FTP client work in PASV (passive) mode or another FTP client that can be called from xp_cmdshell and works in passive more?
I need to join a remote table which has 20 million rows to few local tables on my SQL Server. Currently Linked server query is used but it's just hanging for ever..is there any alternative for this?
I have a production database with input from 3 locations outstation. I need to bring down this server once in a while for tuning and other administrative stuff. Right now I'm in no position to shut the server down. Is there any way I can do this.
Also how can I have 2 databases and toggle between the 2 seamlessly without effecting the users. Does it have to be a mirror on the devices ? What do the professionals usually do
I have heard about clusters. Is this a solution for my problem and what is a cluster anyway ? Vijay
Yes, I know synchronisation to alternate partners is deprecated in SQL2005 but....
In SQL2000 there is a Sync Partners tab in the publication properties dialog that allows you tick a checkbox for each co-publisher to be enabled as an alternate synchronisation partner. What is the equivalent in SQL2005?
I've set up replication in SQL2000 following these instructions http://support.microsoft.com/?kbid=321176 and it works. Now I'm trying to do the same thing in SQL2005 but I can't find a substitute for steps 10 & 11 in the section "Set Up the Alternate Synchronisation Partner". What's the answer?
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments. As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists. Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way... FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup Thanks in advance for any help you can provide!
Hi: I created following temp table in a proc as create table #tempOutput (objID int identity (1,1) primary key clustered, DriverID varchar(10) not null, StatusID int not null constraint AK_tempOutput_DriverID_StatusID unique (DriverID, StatusID), Note varchar(50))
I run the proc first time ok. but the second time got the error: AK_tempOutput_DriverID_StatusID exists already....
after trace to tempdb ----- select * from tempdb..sysobjects where name = 'AK_tempOutput_DriverID_StatusID'
name id xtype parent_obj AK_tempOutput_DriverID_StatusID 849652640 UQ 833652583
select * from tempdb..sysobjects where ID = 833652583 Now, the temp table tempOutput becomes #tempOutput_____________________________0000000002 A1
But I could not drop this table in tempDB, and I could restart the SQLserver to clean the tempdb at this time.
after I renamed the AK_tempOutput_DriverID_StatusID to AK_tempOutput_DriverID_StatusID_1 in the create sql statement, the proc recreated and exec works.
Is there a way without restart SQL server to clean the Alternate key? Is there a way without restart SQL server to clean the temp table? Also, how to prevent the issue? This happens in both SQL2005 ent and SQL2000 ent. I think the exec proc ending should drop the #temp automatically and thus corresponding PK/AK?
For a backup strategy, I was thinking of just copying over all the system and user database device (DAT) files (of course after stopping all the SQL services at a time when no one is accessing the database server). Then at the time of restoration, I would copy the files back to their original location.
Is it possible for me to login using windows login information that is separate from my current windows login? That is to say, can I login to a SQL server using windows athentication but actually specify the login and domain information? I woul like to login to a SQL server from a computer that is not part of the domain using my windows credentials and not a SQL user name.
I have a Tabular model with a situation where I want to have three alternate attributehierachies in one dimension.
Dimension FruitAndVegetables (with 4 columns: Id, Name, Fruit and Vegetable) Id Name    Fruit     Vegetable 1 Apple     Apple   2 Onion                  Onion 3 Banana  Banana 4 etc
Now I would like to put Vegetable on rows in a report without getting a blank row (with the sales of all Fruits)..I would like to supress all those Fruit records without adding a separate filter to the report, just let the user pick this Attribute should do the move.
Is there a way to set up line shading on a report that is created from a table in SRS? The report is great but I want to shade every other line to make it easy to read and being a beginner with SRS I have not been able to find a way to do this.
Hi, I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime. Thanks, np70
In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query.
It looks like you can run an SSIS package via the DTEXEC command. You can also specifiy a configuraiton file flag on the command. Is there anyway to specify a configuraiton filter if you stored it into a table vs and xml file?
One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?
During initial setup of SQL Server 2005 Standard Addition, I was not able to configure an alternate path (non C: ) for the Data component. Can anyone advise on the procedure to set the Data path to non default during initial setup.
I am unable to crosstab different alternate hierarchies of an MSAS cube from a single dimension, I'm using Cognos Powerplay (7.3 web and client) to browse the data. v 7.1 displays the same behaviour.
When I try to create the crosstab the display replaces both rows and columns to the alternate path, it overwrites the original path. Can anyone tell me if this is a Cognos issue or an Analysis services one and if there is any way around it. The only solution I have at the moment is to bodge it by creating seperate dimensions for each alternate hierarchy however this is ugly and difficult to use.
