I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but looking for equivalent query in sql server?
In oracle query is like this:
select *
from tablename
where deptnumber = &department_number;
Enter Value for department_number
I am looking this query in sql serevr2008.
Hello,
I want to write a script that will allow the individual running the script (in Management Studio) to be able to insert different values to the WHERE clause. Basically I want to prompt the user for his/her input. How can I accomplish this?
Thanks for the help!
Layes
Okay,
I'm creating a small application for my local school district that will allow them to do a small management of inventory.
Is there a way to create an executable script that will create the default databases the first time? If so, what all do I need to do for the script and to make it execute?
I want to make a section in my OPTIONS area where users can put the path to their db (will not be hosted on the same PC as the software) along with the username and password for the db. I know I can store this information in a predetermined variable in the program so that it can reference it off and on.
Could someone please help me with this..... If it's in the wrong forum please let me know.
Thanks,
QWERTYtech
Hi,
I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:
INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @IntDate and also changed the parameter name but the error was the same.
Please help how to write this System Variable value in to that table.
Regards,
Samy
Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
Thank for any help in advance
Hank Lee
I would like to run a report for each month over two years. I am currentlyusing a date range like this. Then manually substitute the error_timebounds for each month and rerun the query. How can I script this so I canprogrammatically perform the substitution in a loop. Thanx in advance.select count(*) from application_errorswhere error_message like 'Time%'and error_time >= '1Apr2004' and error_time < '1May2004'
View 10 Replies View RelatedI am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure. I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container. The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure ?" within the "Execute SQL Task". The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one. I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task". How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?
View 4 Replies View RelatedHi I am trying to do something like the following:
DECLARE @Operator varchar(1)
DECLARE @Rate float
DECLARE @Quantity float
DECLARE @Converted float
SET @Quantity = 6
SET @Operator = '/'
SET @Rate = 2
SET @Converted = 0
@Converted = (@Quantity substituteTheValueOfThis(@Operator) @Rate)
PRINT @Converted
so that the output would be 3
The reason I need to do it like this is that @Operator will change at runtime...
Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.
I'm looking at building an SSIS workflow to integrate new XML product data from our supplier (they're switching to XML instead of the Current MS Access database they use). They're using a schema based on the OAGIS structure. When I try to load a Data Flow Task with the XML file, to attempt to parse it, and I specify the XSD schema they have provided, SSIS errors out by telling me:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Data Import [1]]: The component "Data Import" (1) was unable to process the XML data. Substitution Groups not supported. See element "Dimensions" with "substitutionGroup=http://www.openapplications.org/oagis/9: Dimensions".
------------------------------
ADDITIONAL INFORMATION:
Pipeline component has returned HRESULT error code 0xC02092B0 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Is there any way at all to get around this, or am I screwed? As I said, this data is provided to us by our supplier - we have no control over it, but I am looking to pull the data from XML and put it into a regular SQL table which I am in the process of defining - we have a large traffic e-commerce site and obviously cannot use XML as the "back-end".
Assuming that I have created relationships (PKs and FKs) on my tables already, does the following statement permanently remove a Foreign Key constraint, or does it mearly disable it?ALTER TABLE myTable NOCHECK CONSTRAINT FK_myForeignKeyGO Also, I can't seem to find out how to temporarily remove the Identity qualifier of a field, and then reset it back as Identity later. Any help?Finally, will a failure of ALTER TABLE affect the @@ERROR variable? Can I check @@ERROR after each ALTER TABLE table statement to see if @@ERROR <> 0?The reason for both of these issues is that I am redesigning an unnormalized database, and I need to write a large script to drop all constraints on all tables, transform the data as normalized into the new table structure, and then re-enable constraints, Identity fields, etc. Thanks.
View 3 Replies View RelatedIs there a way to temporarily disable replication? My problem is that I have pull subscribers that I do not want accessing the database at certain times, so that data updates and backups can occur. Is there a way to make that happen?
View 4 Replies View Related
I and my backup admin have full server, database, and SRS admin rights. We both have several subscription reports active. I need to temporarily disable all subscriptions for today. Of course, I can see my subscriptions, get into them, and stop them. However, from SQL Server Management Studio / Reporting Services, I can see her subscriptions but can not find a way to disable them.
Any ideas?
Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.
i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?
Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string
Thanks for your time.
Guys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
I have synchronous mirroring. Some times I loose connection to witness and mirror servers. These times primary server is down. Is there any way I can change mirroring to asynchronous when primary server is down due to communication break down between witness and mirrored servers? I can break mirroring but to re-establish mirroring, I have to backup and restore on the other side. So if I can change mirroring to asynchronous when primary server is down due to connection breakdown between witness and mirrored server, then when witness and mirror servers come back, I don't need to restore the entire database. Ofcourse I could use asynchronous always but that does not failover automatically. I am thankful to all answers and suggestions. Thanks.
View 3 Replies View RelatedI am doing a data conversion in which I drop constraints before inserting data and then reapply at the end. I would like to also drop the not null constraints for certain columns, transfer the nulls and then fill them in later before reapplying the constraints. Is there a relatively simple way to drop and reapply the not null constraints?
View 4 Replies View RelatedI have an application that dumps massive amounts of data into a database during the installation. My log file always ends up being 30-40GB+ at the end of the install. Can I turn off logging while I do the install and enable it after? What are my options.
View 6 Replies View Relatedis there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
Poor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it?
Newbie
I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.
I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.
So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View Related Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
What is @@variables means in sql server?
View 3 Replies View RelatedIs there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies View RelatedHi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)