[SQL Server] Using Dynamic SQL in Stored Procedures

1. Building Dynamic SQL In a Stored Procedure - CodeProject

   www.codeproject.com/.../Building-Dynamic-SQL-In-a-Stored-Procedure - Cached

   9 Oct 2007 ... A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set
   of statements stored in a variable and executed using a SQL ...
   

2. Using Dynamic SQL in Stored Procedures - SQLTeam.com

   www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

   7 Mar 2011 ... Dynamic SQL allows stored procedures to ?write? or dynamically generate their
   SQL statements. The most common use case for dynamic SQL ...
   

3. Using Dynamic SQL Statements in Stored Procedures ...

   www.4guysfromrolla.com/webtech/102300-1.shtml - Cached - Similar

   23 Oct 2000 ... We've all likely created ASP pages that contain dynamic SQL statements within
   the ASP pages... SQL statements that are constructed on the fly, ...
   

4. Dynamic SQL Stored procedure - MSDN - Microsoft

   social.msdn.microsoft.com/.../sqlserver/.../action?...dynamic-sql-stored- procedure - Cached

   13 Oct 2012 ... I am trying to write a dynamic SQL stored procedure. I am not successful in doing
   so. I am new to this so please kindly help. Below is my Stored ...
   

5. SQL injection: Dynamic SQL within stored procedures - The What ...

   https://blogs.msdn.com/.../sql-injection-dynamic-sql-within-stored-procedures .aspx - Cached

   5 Sep 2007 ... SQL injection: Dynamic SQL within stored procedures ... This is in spite of using
   parameterized input in data access code to call the stored ...
   

6. Using dynamic sql inside Oracle stored procedure - Database ...

   dba.stackexchange.com/.../using-dynamic-sql-inside-oracle-stored-procedure - Cached

   Assuming I have the following procedure. CREATE PROCEDURE foo ... The only
   reason why I might do that is if I needed to address an object ...
   

7. The curse and blessings of dynamic SQL

   www.sommarskog.se/dynamic_sql.html - Cached - Similar

   In the introduction, I presented various strategies for data-access for an
   application, and I said that ...
   

8. Coding Dynamic SQL Statements - Oracle Documentation

   docs.oracle.com/cd/B10501_01/appdev.920/.../adg09dyn.htm - Cached

   For example, dynamic SQL lets you create a procedure that operates on a table
   ... "A Dynamic SQL Scenario Using Native Dynamic SQL"; "Choosing Between
   Native ..... to native dynamic SQL inside PL/SQL stored procedures and functions.
   

9. c# - Using dynamic SQL in stored procedure? - Stack Overflow

   stackoverflow.com/questions/.../using-dynamic-sql-in-stored-procedure - Cached

   I'm creating a stored procedure for MySQL in a C# app. The stored ... If it makes
   any difference, I'm using MySQL Connector Net v6.5.5, MySQL ...
   

10. Stored Procedures - Executing & Dynamic SQL - SQL Server - SQL ...

    www.toadworld.com/.../sql.../10260.stored-procedures-executing-dynamic- sql.aspx - Cached

    Stored Procedures - Executing & Dynamic SQL. See Also: ... SQL Server will
    inform you that you must declare @qty variable before using it. Similarly, any ...
    

11. Dynamic SQL

    www.benkotips.com/pages/DynamicSQL.aspx - Cached

    Dynamic SQL vs. Stored Procedures. The purist DBA view will point out that
    using stored procedures would be better because of the advantages they provide
    .
    

12. Database - Advanced Stored Procedures Cursors and Dynamic SQL ...
    ► 13:19	www.youtube.com/watch?v=B9zKi8H_IUs5 Feb 2013 - 13 min - Uploaded by Ron Eaglin A demonstration of using cursors and dynamic SQL to solve more complex reporting and ...

13. Stored procedure - Wikipedia, the free encyclopedia

    en.wikipedia.org/wiki/Stored_procedure - Cached - Similar

    Also, some DBMSs will check the parameter's type. A stored procedure that in
    turn generates dynamic SQL using ...
    

14. Generate dynamic SQL statements in SQL Server - TechRepublic

    www.techrepublic.com/.../generate-dynamic-sql-statements-in-sql-server/ - Cached

    10 Mar 2008 ... While these stored procedures can be made to take into account every ....
    Although I am not a huge fan of using dynamic SQL statements, ...
    

15. Permissions With Dynamic SQL Within Stored Procedure : The ...

    forums.asp.net/t/next/1017556 - Cached

    I have a CLR stored procedure which does some dynamic SQL building based
    on values sent in via XML. It's a CLR stored procedure using ...
    

16. Speed Test: Dynamic SQL vs Stored Procedures - BlackWasp

    www.blackwasp.co.uk/SpeedTestSqlSproc.aspx - Cached - Similar

    25 May 2008 ... Two options are dynamically-generate SQL statements and stored procedures.
    Does either ... By using the site you accept the cookie policy.
    

17. Using Dynamic Sql Stored Procedure | Teradata Developer Exchange

    forums.teradata.com/forum/.../using-dynamic-sql-stored-procedure - Cached - Similar

    Hi ,I am trying to write a dynamic sql using Stored procedure wherein I am using
    the underlying table as one of the input parameters.Below is the SP which I am ...
    

18. Dynamic SQL versus stored procedures - Sybase

    infocenter.sybase.com/help/topic/com.sybase.../X80489.htm - Cached - Similar

    Step 5: Deallocate the prepared statement Chapter 9: Using Directory Services.
    Chapter ... There are similarities between dynamic SQL and stored procedures:.
    

19. Dynamic SQL Examples - SQLUSA

    sqlusa.com/bestpractices/training/scripts/dynamicsql/ - Cached

    Stored procedure with dynamic SQL WHILE loop. 4. Dynamic SQL with using
    parent's #temptable. 5. Dynamic SQL for dynamic PIVOT query. 6. Dynamic
    stored ...
    

20. Stored procedures vs. dynamic SQL: When should you use each?

    searchsqlserver.techtarget.com/.../Stored-procedures-vs-dynamic-SQL-When- should-you-use-each - Cached - Similar

    Stored procedures and dynamic SQL are helpful tools depending on the
    circumstances. Read these common rules for using stored procedures and
    dynamic ...
    

[how to] Question on populating from database

---

• Question on populating from database
• db2 database alias not listed via list database directory but still unable to create that database
• db2 SQL0930N error on linux - not enough space to process this statement
• Check to see if my relation is bcnf
• How to get missing values in row order?
• Probable circular dependency, but query still runs
• Do regular ALTERs weaken the performance?
• Table data transfer using SSIS and triggers on destination table
• why I am getting an error message data type mismatch in criteria expression, error 2950? [migrated]
• Enforce a constraint problem
• Install MariaDB and MySQL in CentOS 6 32bit in same server
• Oracle: Truncate large table takes forever
• Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?
• Alter Table for Multiple Databases (same table name)
• Azure SQL VM Can't import dacpac from Azure SQL Database export
• How to optimization database for heavy I/O from updates (software and hardware)
• Update on cascade
• Why SMO's Transfer tries to login into linked server when scripting?
• Sum of different values on the same row in a table
• sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working)
• SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid
• Improve speed of index rebuild on SQL server
• How to loop through AS400 table with cursor
• Bulk insert with format file: 0 rows affected
• What are Measures and Dimensions in Cubes
• MySQL Full Text search increase relevance for exact matches?
• Registry error while trying to install Oracle 11g on Win 2008 R2
• Replication on MySQL server
• Merge Replication identity field issues
• MySQL Slaves lag behind master

Question on populating from database Posted: 16 Sep 2013 09:01 PM PDT I am trying to populate a workout from a list in a database. For example, the Bicep Curl machine has these sets and reps: bicep curl machine sets 1 reps 50 bicep curl machine sets 2 reps 25 bicep curl machine sets 3 reps 15 bicep curl machine sets 4 reps 10 bicep curl machine sets 1 reps 100 I want to randomly choose one of these rows and push it to my site. Right now there is a SetsRepsWeight table containing set_id, sets, reps, and weight Then there is a setMaster table containing machine_id and set_id, where machine_id comes from a table with all the machines available. So if my SetsRepsWeight table looks like this: setMaster contains: So bicep curl can be any of the set_id's 1-5. I want to randomly pick the row and populate it into the database but i need a recommendation on setting this up. Right now the sets, reps, and weight are in an array. How can i achieve my goal? Here is my code now: $machine_atts = array( 'bicep curl' => array( 'sets' => 1, 'reps' => 50, 'weight' => 25 ), 'cable chest press' => array( 'sets' => 1, 'reps' => 100, 'weight' => 40 ), 'lat pulldown' => array( 'sets' => 2, 'reps' => 25, 'weight' => 20 ), 'tricep extension' => array( 'sets' => 3, 'reps' => 25, 'weight' => 30 ), ); $sql1 = "SELECT m1.machine_id, m2.* FROM userPlanDetail AS m1 LEFT JOIN machines AS m2 ON m1.machine_id = m2.machine_id WHERE `user_id` = '$user_id1' AND `cardio` = 0"; $retval1 = mysql_query( $sql1, $conn ); $array = array(); $i=0; while ($row = mysql_fetch_array($retval1, MYSQL_ASSOC)) { $i++; $sets = $machine_atts[$row['machine_name']]['sets']; $reps = $machine_atts[$row['machine_name']]['reps']; $weight = $machine_atts[$row['machine_name']]['weight']; $array[] = '<tr><td width="50"><img src="client workout page/round_'.$i.'.jpg"></td><td><img src="'.$row["picture"].'" style="width:200px;height:200px;display:block;margin:20px auto;"/></td><td>'.$row['machine_name'].' <span class="blue">#'.$i.'</span><br>sets <span class="blue">'.$sets.'</span> reps <span class="blue">'.$reps.'</span><br>weight <span class="blue">'.$weight.'</span></td></tr><tr><td></td><td><a class="demo" href="'.$row["link"].'" rel="group1"><img src="client workout page/click to play video.jpg" style="width:186px;height:14px;margin:0 auto;"></a></td></tr>'; }

db2 database alias not listed via list database directory but still unable to create that database Posted: 16 Sep 2013 08:42 PM PDT I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner db2 => list database directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = /home/mylogin/sqllib Database release level = f.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = db2 => create database testdb2 SQL1005N The database alias "TESTDB2" already exists in either the local database directory or system database directory. db2 => list database directory on /home/mylogin/sqllib Local Database Directory on /home/mylogin/sqllib Number of entries in the directory = 1 Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Database directory = SQL00001 Database release level = f.00 Comment = Directory entry type = Home Catalog database partition number = 0 Database member number = 0 db2 => quit I also checked if there are any other instances that exist other than mine, but there aren't. DB20000I The QUIT command completed successfully. -bash-4.1$ db2ilist mylogin While the alias testdb2 is not listed by list database directory , db2 claims that this alias already exists in either the system database directory or the local database directory. If this is true, how do I get the equivalent of list database directory that lists testdb2 as well?

db2 SQL0930N error on linux - not enough space to process this statement Posted: 16 Sep 2013 08:51 PM PDT This is another newbie question on db2, related to space. How to I start diagnosing and then solving what the problem is so that users can connect to this database? This error is displayed when both connecting to an existing database as well as when creating a new database. db2 => list database directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = /home/mylogin/sqllib Database release level = f.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = db2 => connect to sample SQL0930N There is not enough storage available to process the statement. SQLSTATE=57011 db2 => !uname -a Linux myhostname 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux db2 => create database newdb SQL0930N There is not enough storage available to process the statement. SQLSTATE=57011 db2 =>

Check to see if my relation is bcnf Posted: 16 Sep 2013 08:40 PM PDT I have the original relation: PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) And I have converted it to: PET (Petname, OwnerEmail, PetType, PetBreed, PetDOB) OWNER (OwnerEmail , OwnerLastName, OwnerFirstName, OwnerPhone) With the referential integrity constraint: OwnerEmail in PET must exist in OwnerEmail in Owner. Would this be correct?

How to get missing values in row order? Posted: 16 Sep 2013 05:32 PM PDT In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the value of first available id for next INSERT. Here I want to get the id = 2. Something like SELECT id+1 FROM table WHERE CLAUSE // pointing to the least available value // or x = id +1 (after SELECT) INSERT INTO table (id, ....) VALUES ('x', ....)

Probable circular dependency, but query still runs Posted: 16 Sep 2013 04:19 PM PDT I built this ER diagram in MySQL Workbench and it will run and generate the database. My concern is that the relationship between the employees, departments, positions, and employment_statuses is a circular relationship. Because I know people will ask, this is something I was working on in a class. I'm not asking for help with homework, this is something we were working on in class and I took a different route than the professor. I'm asking for clarification about database modelling theory.

Do regular ALTERs weaken the performance? Posted: 16 Sep 2013 08:39 PM PDT In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the approach to keep the database healthy and efficient? I was thinking of the following approaches: OPTIMIZE the tables CREATE a new database with the final architecture, and INSERT the entire data The database is mysql(5.5) with InnoDB.

Table data transfer using SSIS and triggers on destination table Posted: 16 Sep 2013 05:57 PM PDT I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do that. I've few triggers on destination table. I'm now confused whether these triggers will be fired or not when I transfer the data from source table to destination table. If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer? Giving me some information on the above will be greatly appreciated. Update: Selected rows will be added to the destination table. Triggers will update data in some other table in the destination server based on newly inserted data. For example: CREATE TRIGGER [MyTableChange] ON [dbo].[DestinationTable] AFTER INSERT,UPDATE AS BEGIN declare @newid as int select @newid=newid FROM INSERTED Declare @total as int declare @lookatme as int select @total=total, @lookatme from DestinationTable where newid=@newid update anothertable set total=total+@total where lookwhere=@lookatme END GO Above trigger is kind of example not exactly same logic which I've in original trigger. But it gives info of what exactly I'm trying to do. I'll be accessing newly inserted row details to update another table value. Please do not take this as correct trigger in terms of syntax and way it is written. I just tried to give essence of original trigger.

why I am getting an error message data type mismatch in criteria expression, error 2950? [migrated] Posted: 16 Sep 2013 04:32 PM PDT this is my code: If Isnull([RECRUITMENT_NR]) Then Beep End if If Not Isnull([RECRUITMENT_NR]) Then open form formname frmHR_RECRUITMENT view Form where condition =="[RECRUITMENT_NR]=&[RECRUITMENT_NR] Data mode Window mode Dialog please note we run in access 2010 Error message: Data type mismatch in criteria expression error# 2950 any suggestion is greatly appreciated!

Enforce a constraint problem Posted: 16 Sep 2013 12:11 PM PDT This schema is for a case management database. Work is created with a phase with a child first stage and progresses to an end stage where it is closed. Problem: You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1 Is there anyway to enforce these business rules? CREATE TABLE PHASE_FIRST_STAGE ( PHASE_ID NUMBER(9) NOT NULL, --PRIMARY KEY STAGE_ID NUMBER(9) NOT NULL, --FOREIGN KEY to STAGE table ); ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01 FOREIGN KEY (PHASE_ID) REFERENCES PHASE (ID), FOREIGN KEY (STAGE_ID) REFERENCES STAGE (ID)); COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.'; CREATE TABLE STAGE ( ID NUMBER(9) NOT NULL, --PRIMARY KEY PHASE_ID NUMBER(9) NOT NULL, --FOREIGN KEY DISABLED NUMBER(1) DEFAULT 0 NOT NULL, --CHECK IN (0,1) IS_START_STAGE NUMBER(1),--CHECK IN (0,1) IS_END_STAGE NUMBER(1) --CHECK IN (0,1) ); COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';

Install MariaDB and MySQL in CentOS 6 32bit in same server Posted: 16 Sep 2013 11:56 AM PDT I'm trying to install MariaDB with MySQL in the same server but got this errors: Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package MariaDB-client.i686 0:5.5.32-1 will be installed --> Processing Dependency: MariaDB-common for package: MariaDB-client-5.5.32-1.i686 ---> Package MariaDB-server.i686 0:5.5.32-1 will be obsoleting ---> Package mysql-server.i686 0:5.5.33-1.el6.remi will be obsoleted --> Running transaction check ---> Package MariaDB-common.i686 0:5.5.32-1 will be installed --> Processing Dependency: MariaDB-compat for package: MariaDB-common-5.5.32-1.i686 --> Running transaction check ---> Package MariaDB-compat.i686 0:5.5.32-1 will be installed --> Processing Conflict: mysql-5.5.33-1.el6.remi.i686 conflicts MySQL --> Finished Dependency Resolution Error: mysql conflicts with MariaDB-server-5.5.32-1.i686 You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest I read docs here and here but that doesn't help since those guides are for alone installation I though, how I can install MariaDB and MySQL both in the same server by using yum?

Oracle: Truncate large table takes forever Posted: 16 Sep 2013 01:29 PM PDT Problem I'm truncating a very large table ( +210 million rows ). It's been hours since it started and I have no idea when it will be done. There're several tables like that, that I want to truncate. The database is Ora9i. Question How can I do to make truncates faster ? Also, Is there a way to see the progress of the truncate operation ?

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard? Posted: 16 Sep 2013 10:58 AM PDT MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I set concurrent connections to 800 in the dialogue window, I see max_connections=1023 in my.ini. Why? Is this normal?

Alter Table for Multiple Databases (same table name) Posted: 16 Sep 2013 11:40 AM PDT Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them? the Tables are all the same and the column added will be the same as well. Thanks!

Azure SQL VM Can't import dacpac from Azure SQL Database export Posted: 16 Sep 2013 10:54 AM PDT When attempting to set up an Infrastructure as a Service POC for our business, I am encountering problems importing the database exported from our Azure SQL Database implementation. I'm getting messages about trying to DROP objects such as [PRIMARY]. I only get this in our application database, not our error logging database, which imported without error. The database is created, and all objects are there, but there are differences in nearly every table related to constraint checking of the following nature: Export Source: ALTER TABLE [USER_MANAGEMENT].[ACTION_PAGE_LK] ADD CONSTRAINT [FK_ACTIONPAGELK_ICONGUID] FOREIGN KEY ([ICON_GUID]) REFERENCES [USER_MANAGEMENT].[ICON_LK] ([ICON_GUID]) GO Import Target: ALTER TABLE [USER_MANAGEMENT].[ACTION_PAGE_LK] WITH NOCHECK ADD CONSTRAINT [FK_ACTIONPAGELK_ICONGUID] FOREIGN KEY ([ICON_GUID]) REFERENCES [USER_MANAGEMENT].[ICON_LK] ([ICON_GUID]) GO ALTER TABLE [USER_MANAGEMENT].[ACTION_PAGE_LK] NOCHECK CONSTRAINT [FK_ACTIONPAGELK_ICONGUID] GO

How to optimization database for heavy I/O from updates (software and hardware) Posted: 16 Sep 2013 02:03 PM PDT The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some directions on where to start improving. Here is a picture of how the situation looked the past 3 months: As you can see, update operations accounts for most of the disk utilization. Here is another picture of how the situation looks in a more detailed 3 hour window: As you can see, the peak write rate is around 20MB/s Software The server is running ubuntu 12.04 and postgresql 9.2. The type of updates are small updated typically on individual rows identified by ID. E.g. UPDATE cars SET price=some_price, updated_at = some_time_stamp WHERE id = some_id. I have removed and optimized indexes as much as I think is possible, and the servers configuration (both linux kernel and postgres conf) is pretty optimized as well. Hardware The hardware is a dedicated server with 32GB ECC ram, 4x 600GB 15.000 rpm SAS disks in a RAID 10 array, controlled by an LSI raid controller with BBU and a Intel Xeon E3-1245 Quadcore processor. Questions Is the performance seen by the graphs reasonable for a system of this caliber (read/writes)? Should I hence focus on doing a hardware upgrade or investigate deeper into the software (kernel tweaking, confs, queries etc.)? If doing a hardware upgrade, is the number of disks key to performance?

Update on cascade Posted: 16 Sep 2013 11:31 AM PDT I have two tables: service (id_service, libel, status) agent (id_agent, name, surname, id_service, status). The status field values ​​present in both tables have only two values ​​0 or 1 (0 = inactive and 1 = active). I want that when the field status of the department table is updated (i.e. if it is set to 0), the status of the agent table field is automatically set to 0 as well.

Why SMO's Transfer tries to login into linked server when scripting? Posted: 16 Sep 2013 12:28 PM PDT Consider this code sample: var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword"; var serverConnection = new ServerConnection { ConnectionString = sourceDatabaseConnectionString }; var server = new Server(serverConnection); var builder = new SqlConnectionStringBuilder(sourceDatabaseConnectionString); var database = server.Databases[builder.InitialCatalog]; var transfer = new Transfer(database); // setting transfer options transfer.CopyAllObjects = true; transfer.CopyAllSynonyms = true; transfer.CopyData = false; transfer.Options.WithDependencies = true; transfer.Options.DriAll = true; transfer.Options.Triggers = true; transfer.Options.Indexes = true; transfer.Options.SchemaQualifyForeignKeysReferences = true; transfer.Options.ExtendedProperties = true; transfer.Options.IncludeDatabaseRoleMemberships = true; transfer.Options.Permissions = true; transfer.PreserveDbo = true; // scripting database var script = transfer.ScriptTransfer(); It tries to get a blank from existing database. Some objects in this database depend from objects, located on linked servers. When this code tries to make a script for such objects, ScriptTransfer fails with "Login failed for user 'MyLogin'". Log analysis shows, that error happens when MyLogin tries to log into liked server. But what is the need for this? After all, I just want to get a bunch of CREATE ... scripts for MyDatabase, I don't want to read any data from linked server. My SQL Server and SMO version is 2012 SP1.

Sum of different values on the same row in a table Posted: 16 Sep 2013 08:48 PM PDT I have a table with numbers from 0-5, under different field names (Score1, Score2 etc. all the way to Score50) that need to be summed at the last column of that same row. Each row represents a different client with its own info. Is it possible and if it is, some help would be nice.

sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working) Posted: 16 Sep 2013 12:18 PM PDT I want a monitor on my replica (destination) server to message me: a) once a day, telling me: "Data as of is ### minutes old" b) any time data is older than N minutes (indicating log shipping has gone down) I typically have tlogs moving every 5 minutes, on a very active database. Thank you!

SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid Posted: 16 Sep 2013 11:35 AM PDT I am trying to install and start db2 on my linux machine using the following script: #!/usr/bin/env bash MKDIR=/bin/mkdir RM=/bin/rm TAR=/bin/tar DB2_SERVER_PROGRAM_PATH=/home/alok/Documents/Installers DB2_SERVER_PROGRAM=v10.5_linuxx64_expc.tar.gz echo "changing to $DB_SERVER_PROGRAM_PATH" cd ${DB2_SERVER_PROGRAM_PATH} echo "removing expc" ${RM} -rf expc echo "untarring ${DB2_SERVER_PROGRAM}" ${TAR} zxf ${DB2_SERVER_PROGRAM} echo "changing to expc" cd expc echo "Installing db2 as root" sudo ./db2_install echo "Adding user group db2grp1 as root" sudo groupadd db2grp1 echo "Adding user group dasadm1 as root" sudo groupadd dasadm1 echo "Adding user group db2fgrp1 as root" sudo groupadd db2fgrp1 echo "Adding user db2inst1. Please provide password for new user db2inst1." sudo useradd -g db2grp1 -G dasadm1 -m db2inst1 sudo passwd db2inst1 echo "Adding user dasusr1. Please provide password for new user dasusr1." sudo useradd -g dasadm1 -G db2grp1 -m dasusr1 sudo passwd dasusr1 echo "Adding user db2fenc1. Please provide password for new user db2fenc1." sudo useradd -g db2fgrp1 -m db2fenc1 sudo passwd db2fenc1 cd /opt/ibm/db2/V10.5/instance echo "Creating DAS as root via user dasusr1." sudo ./dascrt -u dasusr1 echo "Creating database instance as root via user db2fenc1 and db2inst1." sudo ./db2icrt -u db2fenc1 db2inst1 echo "Setting communication protocol to tcpip as user db2inst1." sudo -u db2inst1 ../adm/db2set DB2COMM=tcpip echo "Setting SVCENAME 50000 for dbm cfg as user db2inst1." sudo -u db2inst1 ../bin/db2 update dbm cfg using SVCENAME 50000 echo "starting db2 as user db2inst1." sudo chmod +x ../adm/db2start sudo -u db2inst1 ../adm/db2start netstat -an | grep 50000 Everything works fine, till I get to the penultimate line: sudo -u db2inst1 ../adm/db2start SQL1641N The db2start command failed because one or more DB2 database manager program files was prevented from executing with root privileges by file system mount settings. Now I checked /etc/fstab and it says: proc /proc proc nodev,noexec,nosuid 0 0 /dev/sda1 / ext4 errors=remount-ro,user_xattr 0 1 UUID=d4d794eb-1e68-4ddf-a9ec-249a22f084fb none swap sw 0 0 /swapfile swap swap defaults 0 0 My installation path, /opt/ibm/db2/V10.5/adm is basically on /dev/sda1 and it's not mounted using the nosuid option. Here is the output of mount: alok@hostname:~$ mount /dev/sda1 on / type ext4 (rw,errors=remount-ro,user_xattr) proc on /proc type proc (rw,noexec,nosuid,nodev) sysfs on /sys type sysfs (rw,noexec,nosuid,nodev) none on /sys/fs/fuse/connections type fusectl (rw) none on /sys/kernel/debug type debugfs (rw) none on /sys/kernel/security type securityfs (rw) udev on /dev type devtmpfs (rw,mode=0755) devpts on /dev/pts type devpts (rw,noexec,nosuid,gid=5,mode=0620) tmpfs on /run type tmpfs (rw,noexec,nosuid,size=10%,mode=0755) none on /run/lock type tmpfs (rw,noexec,nosuid,nodev,size=5242880) none on /run/shm type tmpfs (rw,nosuid,nodev) binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,noexec,nosuid,nodev) shmfs on /dev/shm type tmpfs (rw,size=2048m) gvfs-fuse-daemon on /home/alok/.gvfs type fuse.gvfs-fuse-daemon (rw,nosuid,nodev,user=alok) This again indicates that / is not mounted in nosuid mode. So why am I getting error SQL1641N? Is it a different problem throwing out this message instead?

Improve speed of index rebuild on SQL server Posted: 16 Sep 2013 08:50 PM PDT I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import. Now I want to re-enable the indexes, and I'm wondering if there is anything that I can do to optimize this. There are > 100 tables and almost 2,000 indexes to be rebuilt. The database is 200GB in size. The key section of the script I'm running is this: declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for select 'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild' from sys.indexes as i Inner Join sys.objects o On o.object_id = i.object_id Where o.is_ms_shipped = 0 And i.index_id >= 1 and i.type > 1 and i.is_disabled = 1 I considered setting ONLINE=OFF for the alter index statement, but as the indexes start out disabled I wasn't sure that this setting would have any effect. I also considered setting SORT_IN_TEMPDB = ON, but as the tempdb files are on the same drive as the .mdf files of the databases I assumed that there was also no benefit to doing that. Whilst running the rebuild script I have noticed that I have a lot of CXPACKET wait types. I don't really understand why that would be or if it's a problem that I should be looking to address. One final point that may be relevant: my entire server is currently inactive other than this import of data into the database. There is no other user activity to consider or worry about; my only concern is importing the data into the database in the shortest possible time.

How to loop through AS400 table with cursor Posted: 16 Sep 2013 12:31 PM PDT I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for one person ordered by the points. The first row with the highest points, I leave alone. On subsequent rows the point values are cut in half and I do an update. I am currently using a cursor to open the table and doing a Loop to read through the rows. I am getting an -508 error stating : An UPDATE or DELETE statement with a WHERE CURRENT OF CL was attempted, but the cursor is not positioned on a row or is positioned on a row, but the row is not locked because a COMMIT HOLD or ROLLBACK HOLD statement released the lock on the row. A FETCH statement must be issued to position the cursor on a row and lock the row. Recovery . . . : Issue a FETCH statement to position the cursor on a row and lock the row; then, try the request again. Part of my code is below: DECLARE V_LNAME CHAR ( 30 ) ; DECLARE V_LNAMEHOLD CHAR ( 30 ) ; DECLARE V_FNAME CHAR ( 15 ) ; DECLARE V_FNAMEHOLD CHAR ( 15 ) ; DECLARE V_DOB DATE ; DECLARE V_DOBHOLD DATE ; DECLARE V_TRANSNMBR CHAR ( 9 ) ; DECLARE V_TRANSNMBRHOLD CHAR ( 9 ) ; DECLARE V_POINTS NUMERIC ( 5 ) ; DECLARE V_POINTSHOLD NUMERIC ( 5 ) ; DECLARE V_POINTSEQ NUMERIC ( 5 ) ; DECLARE FIRSTRECORD CHAR ( 1 ) ; DECLARE CL CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; DECLARE CLHLD CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; OPEN CLHLD ; FETCH CLHLD INTO V_LNAMEHOLD , V_FNAMEHOLD , V_DOBHOLD , V_TRANSNMBRHOLD ; close clhld; OPEN CL ; SET FIRSTRECORD = 'Y' ; LOOP FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ; IF TRIM ( CHAR ( V_LNAME ) ) = TRIM ( CHAR ( V_LNAMEHOLD ) ) AND TRIM ( CHAR ( V_FNAME ) ) = TRIM ( CHAR ( V_FNAMEHOLD ) ) AND V_DOB = V_DOBHOLD AND V_TRANSNMBR = V_TRANSNMBRHOLD AND FIRSTRECORD = 'N' THEN SET V_POINTSEQ = V_POINTS * .5 ; UPDATE DB_TRANSDATA SET POINTSEQ = V_POINTSEQ WHERE CURRENT OF CL ; SET V_LNAMEHOLD = V_LNAME ; SET V_FNAMEHOLD = V_FNAME ; SET V_DOBHOLD = V_DOB ; SET V_TRANSNMBRHOLD = V_TRANSNMBR ; ELSE UPDATE DB_TRANSDATA SET POINTSEQ = V_POINTS WHERE CURRENT OF CL ; SET V_LNAMEHOLD = V_LNAME ; SET V_FNAMEHOLD = V_FNAME ; SET V_DOBHOLD = V_DOB ; SET V_TRANSNMBRHOLD = V_TRANSNMBR ; SET FIRSTRECORD = 'N' ; END IF ; END LOOP ; CLOSE CL; END ;

Bulk insert with format file: 0 rows affected Posted: 16 Sep 2013 12:21 PM PDT When using the BULK INSERT command in SQL Server 2008 it returns: (0 row(s) affected) I am using this command to carry out the bulk insert: BULK INSERT Test FROM 'C:\DataFiles\Tests.dat' WITH (FORMATFILE = 'C:\DataFiles\FormatFiles\TestFormat.Fmt'); GO Tests.dat contains: b00d23fe-580e-42dc-abd4-e8a054395126,48dd5dd6e3a144f7a817f234dd51469c,452eb8ce-6ae2-4e7a-a389-1097882c83ab,,, ,,,,Aria,,,160,,,86400,,2004-04-03 23:23:00.000,,2012-07-06 13:26:31.633,2012-07-06 13:27:44.650,3,,,,51B7A831-4731-4E2E-ACEC-06636ADC7AD3,,0,,0,,Field Name 1,,Field Name 2,,Field Name 3,,Field Name 4, and the format file TestFormat.fmt contains: 9.0 39 1 SQLCHAR 0 37 "," 1 Key "" 2 SQLCHAR 0 37 "," 2 TestType "" 3 SQLCHAR 0 37 "," 3 CaseKey "" 4 SQLCHAR 0 30 "," 4 Height "" 5 SQLCHAR 0 30 "," 5 Weight "" 6 SQLCHAR 0 128 "," 6 PacemakerType Latin1_General_CI_AI 7 SQLCHAR 0 0 "," 7 Diary Latin1_General_CI_AI 8 SQLCHAR 0 0 "," 8 Indication Latin1_General_CI_AI 9 SQLCHAR 0 0 "," 9 Medication Latin1_General_CI_AI 10 SQLCHAR 0 37 "," 10 RecorderType "" 11 SQLCHAR 0 100 "," 11 RecorderSerial Latin1_General_CI_AI 12 SQLCHAR 0 0 "," 12 Comments Latin1_General_CI_AI 13 SQLCHAR 0 12 "," 13 Status "" 14 SQLCHAR 0 0 "," 14 AdditionalData Latin1_General_CI_AI 15 SQLCHAR 0 37 "," 15 OrderKey "" 16 SQLCHAR 0 12 "," 16 Duration "" 17 SQLCHAR 0 12 "," 17 Age "" 18 SQLCHAR 0 24 "," 18 RecordingStartDateTime "" 19 SQLCHAR 0 128 "," 19 Ward Latin1_General_CI_AI 20 SQLCHAR 0 24 "," 20 CreatedDateTime "" 21 SQLCHAR 0 24 "," 21 UpdatedDateTime "" 22 SQLCHAR 0 21 "," 22 UserGroupBits "" 23 SQLCHAR 0 24 "," 23 LastArchive "" 24 SQLCHAR 0 128 "," 24 PointOfCare Latin1_General_CI_AI 25 SQLCHAR 0 128 "," 25 Bed Latin1_General_CI_AI 26 SQLCHAR 0 37 "," 26 DownloadFacilityKey "" 27 SQLCHAR 0 37 "," 27 AnalysisFacilityKey "" 28 SQLCHAR 0 12 "," 28 Priority "" 29 SQLCHAR 0 37 "," 29 FacilityKey "" 30 SQLCHAR 0 12 "," 30 PacemakerTypeStandard "" 31 SQLCHAR 0 128 "," 31 TestTypeName Latin1_General_CI_AI 32 SQLCHAR 0 128 "," 32 UserDefined1Name Latin1_General_CI_AI 33 SQLCHAR 0 128 "," 33 UserDefined1Value Latin1_General_CI_AI 34 SQLCHAR 0 128 "," 34 UserDefined2Name Latin1_General_CI_AI 35 SQLCHAR 0 128 "," 35 UserDefined2Value Latin1_General_CI_AI 36 SQLCHAR 0 128 "," 36 UserDefined3Name Latin1_General_CI_AI 37 SQLCHAR 0 128 "," 37 UserDefined3Value Latin1_General_CI_AI 38 SQLCHAR 0 128 "," 38 UserDefined4Name Latin1_General_CI_AI 39 SQLCHAR 0 128 "\r\n" 39 UserDefined4Value Latin1_General_CI_AI I cannot figure out why this isn't working. Other people have had similar problems because they had more fields than actual columns in their database. Or using .csv files which are not supported apparently. This works fine on every other table in the database I am importing with no errors so I can't understand why it doesn't work here.

What are Measures and Dimensions in Cubes Posted: 16 Sep 2013 01:17 PM PDT I'm very new to Microsoft Sql Server Business Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If it's possible with images)? thanks

MySQL Full Text search increase relevance for exact matches? Posted: 16 Sep 2013 01:21 PM PDT I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching "doobry institute" etc. The problem I have is that I have lots of entries like "institute of doobry canteen" and "institute of doobry alumni association". MATCH (names) AGAINST ("doobry institute") will return all of these records and will not score institute of doobry higher than the canteen. I'd sort of like to say: score it higher if the word count is similar. Is there any way to achieve this?

Registry error while trying to install Oracle 11g on Win 2008 R2 Posted: 16 Sep 2013 03:57 PM PDT I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time... It gives me error when updating registry key HKEY_LOCAL_MACHINE/software/oracle/OracleMTSRecoveryServiceProtid_0.. It says The specified home name was not found in the registry Could anyone please help me out to resolve this..

Replication on MySQL server Posted: 16 Sep 2013 04:21 PM PDT I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server On the problematic server mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: servera Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: servera-bin.000024 Read_Master_Log_Pos: 808459481 Relay_Log_File: serverb-relay-bin.000071 Relay_Log_Pos: 4 Relay_Master_Log_File: servera-bin.000024 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 808459481 Relay_Log_Space: 106 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1129 Last_IO_Error: error connecting to master 'replica@servera:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: on the problematic server: SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y'; +---------+-----------+ | user | host | +---------+-----------+ | root | localhost | | root | serverb | | root | 127.0.0.1 | | replica | servera | | replica | serverb | +---------+-----------+ on the main server: SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y'; +---------+-----------+ | user | host | +---------+-----------+ | root | localhost | | root | servera | | root | 127.0.0.1 | | replica | servera | | replica | serverb | +---------+-----------+ according to what I've read, there is a need to execute the following command om the main server: mysql> FLUSH HOSTS; What will happen then? if there is any application connected to it - will it disconnect it also?

Merge Replication identity field issues Posted: 16 Sep 2013 03:21 PM PDT One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of which is live at any one time, so basically one and live one on standby. Only the live database is updated by teh applications. Every couple of months they failover between the datacentres and the standby environment become the live centre. There is an instance of SQL Server 2008 in each datacentre and merge replication is used to keep them in sync. This was all working ok until the beginning of the year when we started getting replication errors with some lTID columns in various tables that have the Identity property set. The errors were like this one: The insert failed. It conflicted with an identity range check constraint in database 'GateMain', replicated table 'dbo.tGateCalcsLog', column 'lTID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. Then after the last failover we noticed we had an issue with the lTID values in one specific table. Our application relies on the lTID value always having incremented in order such that the highest lTID value is always the newest entry in the table. We've found that due to how the identity ranges are being managed by replication that when the system is failed over that the lTID range of the now live database server may have a range of values that are lower than those already present in the table. Is there a way to manage this in merge replication so we can guarantee that the next identity value allocated to the lTID column in greater than any lTID currently in the table? Or do we need to use a different type of replication or possibly mirroring?

MySQL Slaves lag behind master Posted: 16 Sep 2013 05:21 PM PDT I have one master and four slaves. Sometimes all my slaves lag behind the master. I have implemented the heartbeat for monitoring replication lag. Now I am trying to find why the slaves are lagging behind the master. I saw the slow queries (for today) on the master and I found that the slowest query (DML) was taking 138 seconds. But the slaves were lagging about 1400 seconds and there were also no slow queries on the slaves for DML (update, delete, insert, etc.). Points to be taken into consideration: All tables are InnoDB. 68 GB of RAM (Master as well as slaves). Data size about 1 TB. Master and slave are running from a long. What may be the reason for lag?

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Update Table - By Looping Through Field Name

---

Update Table - By Looping Through Field Name Posted: 15 Sep 2013 09:50 PM PDT HI,Is it possible to achieve the following ;I have a table with say ... 200 Fields and I want to check each field for a value and possibly update if a match is found.something like...FOR EACH "FieldWithinMyTable" as FieldChecker IF FieldChecker = 'FoundIt' then update FieldChecker with new fielddata ENDIFNEXT

Log file backup growth (how long's a piece of string...) Posted: 15 Sep 2013 10:13 PM PDT If your log backup file was as follows:2,176 KB at 10 this morning10,617 KB at 11 this morning18,615 KB at 12 middaywould that seem like an unusual amount of growth to you? (I know approx 8 MB an hour might not seem like a lot, and it's early days yet, but I wondered what others thought.)It has just been switched to Full recovery model, with the log being backed up every hour. Do the figures above suggest that it needs to be backed up much more frequently than that? (The database is about 58 GB.) (2008 R2 Standard Edition)

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[Articles] If we can do it, so can you

---

If we can do it, so can you Posted: 15 Sep 2013 11:00 PM PDT Microsoft is trying to prove that development works well in the cloud by moving their own internal development to the Azure cloud.

You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[MS SQL Server] Validating large tables replicated

---

• Validating large tables replicated
• Copy File Across Servers Failing
• SQL Server Upgradation from 2005 to 2008

Validating large tables replicated Posted: 16 Sep 2013 04:58 AM PDT Hello,I am working with large tables (many millions of records) in SQL Server that are replicated from one server to another and I need to find a way to quickly compare the source table with the target. The key to this issue is that the tables do not have a primary key, otherwise, I would look for the max primary key value in both tables (source vs. target) and check the delta (difference). Applying a count of the records is not efficient. Also, checking the row count for the tables according to sys.sysindexes is not possible due to the inconsistency in which statistics are run on both servers.Is there an approach using the trasactional replication where a quick comparison can be run between the source and target tables that will provide a delta? I have already read the following article regarding replication monitoring, but it was at a higher level than I'm seeking.http://www.sqlservercentral.com/articles/Stairway+Series/72451/Thanks,Grant

Copy File Across Servers Failing Posted: 16 Sep 2013 04:40 AM PDT I have 2 servers that are in the same rack, but not on a domain. Ultimately, I want a SQL scheduled job to copy files from Server_A to Server_BLogged on to Server_B as Windows/Administrator, I want to copy backup files from Server_A to Server_B with the following SQL Statement:[code="sql"]DECLARE @cmd varchar(500)SET @cmd = 'copy /Y ' + '\\Server_A_IP_Address\f$\Backup\231_StandbyServer\' + 'MyBackupFile.bak ' + 'F:\Backups\231_Server\FULL\' select @cmdEXEC master.dbo.xp_cmdshell @cmdwhich runs this:copy /Y \\Server_A_IP_Address\f$\Backup\231_StandbyServer\MyBackupFile.bak F:\Backups\231_Server\FULL\[/code]I get "access denied"But if I put the "[font="Courier New"]copy /Y \\Server_A_IP_Address[/font]...." statement into a DOS command line, it works fine.Thoughts ?

SQL Server Upgradation from 2005 to 2008 Posted: 16 Sep 2013 02:05 AM PDT Can you please any one clarify my below doubts, because we are going to Upgrade SQL 2005 to 20081) Can we attach SQL 2005 DB .mdf file in SQL Server 2008 ?2) Can we restore SQL 2005 User Databases in SQL 2008 ?and 3) Can we restore SQL 2005 System Databses (Master,MSDB..etc) into SQL Server 2008 ?

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610