Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?
SELECT TOP 100 text, query_plan, cp.plan_handle, qs.last_execution_time FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle WHERE objtype='Adhoc'
I have below output:
Text Query_plan Plan_handle Lst_execution_time Select id,name from person <Showplan... dshkkgdaHqrqe13232423 2015-07-21 10:50:22.713 Update customer set Cid=3 where name='abc' <Showplan... poasfvrqe13232423 2015-07-21 10:16:22.500 delete from orders where ORid=8 <Showplan... 2ase2423 2015-07-21 10:10:22.710 Select 1,2,3,4,5 from num <showplan afqfqfq 2015-07-21 10:10:22.710
I am looking for
Text Query_plan Plan_handle Last_execution_time TabName colname Select id,name from person <Showplan... dshkkgdaHqrqe13232423 2015-07-21 10:50:22.713 Person Id, name Update customer set Cid=3 where name='abc' <Showplan poasfvrqe13232423 2015-07-21 10:16:22.500 customer Cid, name delete from orders where ORid=8 <Showplan... 2ase2423 2015-07-21 06:10:22.710 Orders ORid Select 1,2,3,4,5 from num <showplan afqfqfq 2015-07-21 10:10:22.710 nUM 1,2,3,4,5
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
I have a great problem !! I have a database having around 50 tables and around 70 t0 100 stored procedures. due to certain reason name of all the table and procedure was prefixed with "cls" , now i want to rename them by "tbl_" using some query !! in bulk , and tables have relation ships.
so how to rename them !!
is there any way that I can rename them by modifying data in any system table !?
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings. - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter 2. Pass the parameter to a function that will return a string 3. Execute that string as SQL 4. Get a return value from that SQL statement 5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A LEFT JOIN #SqlStatement B ON A.spid = B.spid where B.Statement IN ( SELECT T.name, C.name FROM sys.tables T JOIN sys.columns C ON T.object_id=C.object_id WHERE T.type='U' )
Something like this : find the column names and tables name
we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
hi, I had a table and it has certain dependecies(like stored proc.). I rename the table and created anew one with same name.this new table did not inherited all the dependecies which it should. To say, I had a table TAB.I renamed it to TAB_old and made a new table with name TAB.When I go to SEM and click on 'Display Dependencies' table TAB is not showing anything whereas TAB_old is stil showing all the dependecies.I even ran Update stats and sp_updatestats. any idea what is this going on..... TIA
I'm struggeling a bit with this one, goal is to show the first 3 characters of the name and replace the remaining characters with *** stars. Also the number of stars needs to match the lenght of the name as in the example above.I've tried:
My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.
A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.
Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.
Anyone have any experience with doing things like this?
While looking forward to design a multi-columnar cross-tab query I am anxious to know if there could be a way to change the default names of the pivot columns? In other words for the query like the following can there be a way to apply anAS type command to reflect some other names, instead of having the four dates in heading? Something like Month_A, Month_B?
SELECT * FROM (SELECT X.REP_DT, X.CUST_ID AMOUNT_1 FROM X) P PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01
I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected.
BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt' WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR=' ',FIRSTROW=172)
The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option.
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
I need to create a T-sql script to create logins in bulk so users can access views on the database. Usernames as used in the local domain are all stored in a table.
Users already exist in the domain (let’s say: MYDOMAIN)ADSI.dbo.Ad.username is the column wich contains the usernames as used in the Active Directory domain (+/- 350)Script needs to check if login already exists to make sure login is not added twice.If possible give all these users db_datareader role, in let’s say: MYDATABASE